function to compute average without zeros

  • Thread starter Thread starter Sherie
  • Start date Start date
S

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
 
If your cells' results are produced by formulas

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

then just use the average formula.
 
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
 
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
 
Just a caution,
If any of the cells are blank, it will give an unexpected answer.

Regards,
Tom Ogilvy
 
Back
Top