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!