Averaging, ignoring zeros

[Mark]

How do you average values in a row, ignoring any zeros?

 

[Bob Phillips]

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

This is an array formula, so commit with Ctrl-SHift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

=AVERAGE(IF(A11<>0,A11))

entered with ctrl + shift & enter

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[Jason Morin]

Just an alternative to the typical AVERAGE array formula:

=SUM(1:1)/SUM(COUNTIF(1:1,{"<>","*",0})*{1,-1,-1})

HTH
Jason
Atlanta, GA

 

[Harlan Grove]

Peo Sjoblom wrote...

=AVERAGE(IF(A11<>0,A11))

entered with ctrl + shift & enter

....

Normal caveats with respect to continuity - if there could be positive
and negative values, zero values should be included. If only positive
values should be included in averages, that should be made explicit,
i.e.,

=AVERAGE(IF(A11>0,A11))