Division by Zero

[scotfitz]

I am struggling with a formula that in some instances needs to divide by zero.

I am using the formula =Sum(A1:A10)/Count(A1:A10).

In some cases there are numbers in the referenced cells and in some cases the cells are zero.

I get the result I want if I create the formula =Sum(A1:A10) in cell A11; =Count(A1:A10) in cell A12; and =A11/A12 in cell A13. However I would like to do it in one step if possible.

Help is always appreciated.

[mikerickson]

Can you post some sample data? If COUNT(A1:A10) = 0 then both =SUM(A1:A10)/COUNT(A1:A10) and = A11/A12 will return a #DIV/0 error.

Also, have you looked at AVERAGE?

[scotfitz]

A1 5
A2
A3
A4 3
A5
A6 1
A7
A8 9
A9

A few of the cells will contain numbers, however, many of the cells will be blank and some entire columns will be blank.

I have tried =Avg(A1:A10) and get the same #### answer when the entire column is blank.

Thanks.

[daddylonglegs]

Try

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

[mikerickson]

What result do you want when there are no numbers to average?

[scotfitz]

Thanks, it works like a charm.