Averaging when cells contain blanks or zeros?

[liliantsingli]

I am trying to average around 50 cells, some of which are either blank or contain zeros. I just want to know the average for the cells that contain numbers. Naturally, If I use =AVERAGE(FI5:HH5) I get the div/0 error. Please can you help me, I have checked the archives to no avail. Thanks in advance.

 

[fairwinds]

Welcome!

As you get DIV/0! I guess your numbers are formatted as text.


Try:

=AVERAGE(IF(--FI5:HH5<>0,--FI5:HH5))

Confirmed with Ctrl + Shift + enter.

 

[TheToeOfPooh]

One way you could work around this is to firstly use COUNTIF
to identify cells with contents >0

e.g.using Column "F" Rows 4 to 34 inclusive

In cell F35
=COUNTIF(F4:F34,">=0")

In cell F36
then sum
=SUM(F4:F34)

Then in cell F37
=SUM(F36/F35)

HTH
Bernard

 

[P Sitaram]

Also look here:

http://www.mrexcel.com/board2/viewtopic.php?t=82921