Average without zeros

[goosteroo]

Hi. How do I calculate averages without including zeros?

i.e.

A B C D
0 6 0 2

The AVERAGE() formula gives "2" as the answer, but without including "0," it's 4.

What would the formula be?

Thanks.

 

[erik.van.geit]

Hi,

try this
  A   B  C  
1 txt    4  
2 2         
3 0         
4 6         

list
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
C1    {=AVERAGE(IF((A1:A4)<>0,A1:A4))}

 enter formula without {}
 confirm with Control-Shift-Enter

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[barry houdini]

Either

=AVERAGE(IF(A1:D1<>0,A1:D1))

confirmed with CTRL+SHIFT+ENTER

or, assuming all data is numeric

=SUM(A1:D1)/MAX(1,COUNTIF(A1:D1,"<>0"))

which only requires ENTER

edited typo in first formula

 

[indiantrix]

Try this: =SUM(A1:D1)/COUNTIF(A1:D1,"<>0")
/s/ Larry

 

[mortgageman]

Over here you will find similar responses, plus a UDF as well.

Gene, "The Mortgage Man", Klein