# Averaging when cells contain blanks or zeros?

#### liliantsingli

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.

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

Replies
2
Views
720
Replies
3
Views
261
Replies
4
Views
243
Replies
5
Views
308
Replies
5
Views
190

1,221,127
Messages
6,158,100
Members
451,464
Latest member
Holden3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back