| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help Please with If statement / vlookup
=AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24)
Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Please with If statement / vlookup
Donna,
Is therre an error in the data? I insert an +NA() into the data and this produced a #VALUE! error. Correct this and it will work. HTH Peter "Donna" wrote: =AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24) Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Please with If statement / vlookup
Billy, I don't understand the insert an +NA() in the data
{=AVERAGE(SMALL(K7:K20,ROW($1:$5)))} This formulal works great to find an average for the first five cells that have a value, however If want to look at cells from two different sheets how would I write the formula. I tried this, {=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5)))} but I get a #value! ( one of the sheets is Mar and one is Feb) ***Basically I need to get an average from the first five cells that have values, But I need to look at cells in two different sheets. "Billy Liddel" wrote: Donna, Is therre an error in the data? I insert an +NA() into the data and this produced a #VALUE! error. Correct this and it will work. HTH Peter "Donna" wrote: =AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24) Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Please with If statement / vlookup
I was replying to you regarding the UDF. There is an Excel function called NA
that produces the #N/A! value. I entered this into the test data '=NA()',to produce a #VALUE! error like the one you describe. The UDF would also produce a value error if there was a division by zero. If the data is numeric with no errors it will give the average Perhaps Micky can help you with the other solution. "Donna" wrote: Billy, I don't understand the insert an +NA() in the data {=AVERAGE(SMALL(K7:K20,ROW($1:$5)))} This formulal works great to find an average for the first five cells that have a value, however If want to look at cells from two different sheets how would I write the formula. I tried this, {=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5)))} but I get a #value! ( one of the sheets is Mar and one is Feb) ***Basically I need to get an average from the first five cells that have values, But I need to look at cells in two different sheets. "Billy Liddel" wrote: Donna, Is therre an error in the data? I insert an +NA() into the data and this produced a #VALUE! error. Correct this and it will work. HTH Peter "Donna" wrote: =AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24) Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Please with If statement / vlookup
Hi Billy, I am sorry, I am trying both things at once.
there isn't a error in any of the data, but some of the cells are blank... "Billy Liddel" wrote: I was replying to you regarding the UDF. There is an Excel function called NA that produces the #N/A! value. I entered this into the test data '=NA()',to produce a #VALUE! error like the one you describe. The UDF would also produce a value error if there was a division by zero. If the data is numeric with no errors it will give the average Perhaps Micky can help you with the other solution. "Donna" wrote: Billy, I don't understand the insert an +NA() in the data {=AVERAGE(SMALL(K7:K20,ROW($1:$5)))} This formulal works great to find an average for the first five cells that have a value, however If want to look at cells from two different sheets how would I write the formula. I tried this, {=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5)))} but I get a #value! ( one of the sheets is Mar and one is Feb) ***Basically I need to get an average from the first five cells that have values, But I need to look at cells in two different sheets. "Billy Liddel" wrote: Donna, Is therre an error in the data? I insert an +NA() into the data and this produced a #VALUE! error. Correct this and it will work. HTH Peter "Donna" wrote: =AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24) Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup, if statement, maybe an and statement | Excel Discussion (Misc queries) | |||
| Using IF statement in a VLOOKUP | Excel Discussion (Misc queries) | |||
| Help with If and Vlookup Statement | Excel Worksheet Functions | |||
| If Statement / VLookup | Excel Worksheet Functions | |||
| Vlookup or If statement Help | Excel Worksheet Functions | |||
Hybrid Mode
