Getting average but ignoring cells with blank (#value!)

**Elainefish** · 05-23-2013, 08:07 AM

I am using =AVERAGE(AL4:AL997)
but I am getting error in cell AL999 since my cell range contains blanks
calculation_question6.xlsx

additional notes:
No problem with my formulas in AL4:AL997 but I want to know the average of all the data in column AL which will populate in AL999
as of now, I am using =AVERAGE(AL4:AL997)
but I am getting error in cell AL999 since my cell range contains blanks

**Fotis1991** · 05-23-2013, 08:12 AM

In AL4 AND COPY DOWN.

=IFERROR(((((R4*T4)*V4)*X4)*(((Z4*AB4)+AE4)))*(AI4*AK4);"")

**Elainefish** · 05-23-2013, 08:16 AM

Originally Posted by Fotis1991

In AL4 AND COPY DOWN.

=IFERROR(((((R4*T4)*V4)*X4)*(((Z4*AB4)+AE4)))*(AI4*AK4);"")

I am sorry that I did not make myself clear...
No problem with y formulas in AL4:AL997 but I want to know the average of all the data in column AL which will populate in AL999
as of now, I am using =AVERAGE(AL4:AL997)
but I am getting error in cell AL999 since my cell range contains blanks

**Kalergi** · 05-23-2013, 08:22 AM

Hi!
As Fotis said, use: =IFERROR(((((R4*T4)*V4)*X4)*(((Z4*AB4)+AE4)))*(AI4*AK4);"") and your AVERAGE formula form AL999 will show the correct result.

Cheers!