Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup average()
Hi everyone,
Does anyone know how to solve the issue that I'm facing? I basically need to provide the average for M01 M02 M03 for my Q1, but some cells do not contain any data are empty ="" My formula is: =IF(ISNA(+IF(O49="",AVERAGE(VLOOKUP($D$22,'sheet1' !$A$4:$BM$1000,26,FALSE),VLOOKUP($D$22,'sheet1'!$A $4:$BM$1000,27,FALSE),VLOOKUP($D$22,'sheet1'!$A$4: $BM$1000,28,FALSE)),O49)),"",(+IF(O49="",AVERAGE(V LOOKUP($D$22,'sheet1'!$A$4:$BM$1000,26,FALSE),VLOO KUP($D$22,'sheet1'!$A$4:$BM$1000,27,FALSE),VLOOKUP ($D$22,'sheet1'!$A$4:$BM$1000,28,FALSE)),O49))) But this one is taking into account the empty and counting them as zero :( Could anyone point out some solution? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup average()
Hi
Average will ignore cells that contain Null. Why not make life a little easier. On Sheet 1 in cell BN4 enter =AVERAGE(Z4:AB4) Repeat in BO4, BP4, BQ4 using the appropriate cells for those quarters. The use =IF(O49="","",VLOOKUP($D$22,Sheet1!$A$4:$BQ$4:$BQ$ 1000,66,0) Change to 67, 68 or 69 when you want the other quarters. -- Regards Roger Govier "new_121" wrote in message ... Hi everyone, Does anyone know how to solve the issue that I'm facing? I basically need to provide the average for M01 M02 M03 for my Q1, but some cells do not contain any data are empty ="" My formula is: =IF(ISNA(+IF(O49="",AVERAGE(VLOOKUP($D$22,'sheet1' !$A$4:$BM$1000,26,FALSE),VLOOKUP($D$22,'sheet1'!$A $4:$BM$1000,27,FALSE),VLOOKUP($D$22,'sheet1'!$A$4: $BM$1000,28,FALSE)),O49)),"",(+IF(O49="",AVERAGE(V LOOKUP($D$22,'sheet1'!$A$4:$BM$1000,26,FALSE),VLOO KUP($D$22,'sheet1'!$A$4:$BM$1000,27,FALSE),VLOOKUP ($D$22,'sheet1'!$A$4:$BM$1000,28,FALSE)),O49))) But this one is taking into account the empty and counting them as zero :( Could anyone point out some solution? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup average()
I think you have a few choices.
One way is to cheat--but it's fraught with danger! You could fill all the empty cells in the lookup range with something that looks empty. I use a formula: ="". Then the =vlookup() won't return 0 for those cells. The danger is that if you change the data, you may forget to add the ="" and you may not notice it. Also, those empty string formulas may mess up any formulas that use =counta(). Another alternative is to change each of the =vlookup() formulas: =vlookup($d$22,'sheet1'!$a$4:$bm$1000,26,false) gets replaced with: =if(vlookup($d$22,'sheet1'!$a$4:$bm$1000,26,false) ="","", vlookup($d$22,'sheet1'!$a$4:$bm$1000,26,false)) You may want to consider putting those =vlookup()'s in a range on a helper worksheet (hidden????), then use =average(thatrange) Formulas in xl97-xl2003 can be 1024 characters long when measured in R1C1 reference style (IIRC). new_121 wrote: Hi everyone, Does anyone know how to solve the issue that I'm facing? I basically need to provide the average for M01 M02 M03 for my Q1, but some cells do not contain any data are empty ="" My formula is: =IF(ISNA(+IF(O49="",AVERAGE(VLOOKUP($D$22,'sheet1' !$A$4:$BM$1000,26,FALSE),VLOOKUP($D$22,'sheet1'!$A $4:$BM$1000,27,FALSE),VLOOKUP($D$22,'sheet1'!$A$4: $BM$1000,28,FALSE)),O49)),"",(+IF(O49="",AVERAGE(V LOOKUP($D$22,'sheet1'!$A$4:$BM$1000,26,FALSE),VLOO KUP($D$22,'sheet1'!$A$4:$BM$1000,27,FALSE),VLOOKUP ($D$22,'sheet1'!$A$4:$BM$1000,28,FALSE)),O49))) But this one is taking into account the empty and counting them as zero :( Could anyone point out some solution? Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
Using VLookup, Displacement, SumIF to total and average | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |