Average without counting zero’s

[Saturn]

I want to calculate the average of, let’s say, four cells.
Basic.
But if one or more cells in the range are zero, I don’t want these cells to a part of the calculation.
How could the formula be constructed ?

Saturn

[canapone]

Hi, an array formula

=AVERAGE(IF(A1:A100,A1:A100))

to be confirmed with control+shift+enter.

Regards

Edit: I'd left a ";" in the formula.

[Saturn]

Great!! An array is the solution.
It works.
Thanks a lot

[martindwilson]

=sum(a1:a100)/countif(a1:a100,"<>"&0)

[Saturn]

Thanks again
The last one is good if the cells actually contains zero's and is not just blank.
Another good option.

Saturn

[daddylonglegs]

You can also use AVERAGEIF function

=AVERAGEIF(A1:A4,"<>0")

[jlr4]

I have all most the same problem. I'm trying to average a value in b2 plus a different sheet (bills) c2:ab2 and not average in cells that contain a zero. Here is how I have the formula:
=averageif(b2,bills!c2:ab2,"<>0") what I get is a dialog box saying error in formula and it highlights b2 in the formula. If I remove b2 from the formula it works.
=averageif(bills!c2:ab2,"<>0") . Any idea on whats going on?

jim

[martindwilson]

you should start your own thread linking to this one if needed