Averaging over cells with a number (including a zero) but not empty cells

[erik11]

Dear MrExcel users

I have a problem, that I hope someone can help me solve:
How do I write a formula in a cell, which will compute the average of all the numbers in the range from say A1 to A5 in the following way: If any of the cells in the range are empty they should be left out completely. So in the following example, the "advanced average" I am looking for should deliver the result (3+7+0+10)/4 = 5:

3
7
0
(empty)
10

NB! My intention is a worksheet, which can calculate the "current average" as the cells are being filled out ...

Regards, Erik

 

[VoG]

Hello. AVERAGE ignores blank cells and text entries so you can use

=AVERAGE(A1:A5)

 

[T. Valko]

Dear MrExcel users

I have a problem, that I hope someone can help me solve:
How do I write a formula in a cell, which will compute the average of all the numbers in the range from say A1 to A5 in the following way: If any of the cells in the range are empty they should be left out completely. So in the following example, the "advanced average" I am looking for should deliver the result (3+7+0+10)/4 = 5:

3
7
0
(empty)
10

NB! My intention is a worksheet, which can calculate the "current average" as the cells are being filled out ...

Regards, Erik

Use the AVERAGE function. It will ignore empty cells (unless all the cells are empty!).

=AVERAGE(A1:A5)

If all the cells are empty then you'd get a #DIV/0! error.

One way to account for that is to make sure there is at least one number in the range:

=IF(COUNT(A1:A5),AVERAGE(A1:A5),"")

 

[erik11]

Great Biff! That was exactly what I was looking for, so I avoid the division by zero. Thanks a lot!

Erik

 

[T. Valko]

Great Biff! That was exactly what I was looking for, so I avoid the division by zero. Thanks a lot!

Erik

You're welcome. Thanks for the feedback!