function to compute average without zeros

[Sherie]

I am not a programmer but need to write a formula that
will compute an average from a column but without the
zeros.
Or I need the zeros to become an empty cell through a
formula.
I have been researching this for hours and have come up
blank.
Thanks for all help
Sherie

 

[Tom Ogilvy]

If your cells' results are produced by formulas

=if(formlua=0,"",formula)

then just use the average formula.

 

[Ryan Poth]

Sherie,

You could try this (assuming your data is in the range
E5:E14):

=AVERAGE(IF(E5:E14<>0,E5:E14))

This formula is array-entered (ctrl-shift-enter)

hth,
Ryan

 

[Paul Simon]

Hi Sherie,

With numbers in cells A1 through A6 with some cells containing zeros,
this formula should give you what you want:

=SUM(A1:A6)/COUNTIF(A1:A6,"<>0")

Paul

 

[Tom Ogilvy]

Just a caution,
If any of the cells are blank, it will give an unexpected answer.

Regards,
Tom Ogilvy