vlookup average()

[Guest]

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(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)))

But this one is taking into account the empty and counting them as zero
Could anyone point out some solution?

Thanks!

 

[Roger Govier]

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.

 

[Dave Peterson]

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).