Averaging, ignoring zeros
 

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

=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)


"Mark" wrote in message
...
How do you average values in a row, ignoring any zeros?

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

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom


"Mark" wrote in message
...
How do you average values in a row, ignoring any zeros?

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

Vaya con Dios,
Chuck, CABGx3


"Mark" wrote in message
...
How do you average values in a row, ignoring any zeros?

Just an alternative to the typical AVERAGE array formula:

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

HTH
Jason
Atlanta, GA

-----Original Message-----
How do you average values in a row, ignoring any zeros?
.

Peo Sjoblom wrote...
=AVERAGE(IF(A1:D1<0,A1:D1))

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(A1:D10,A1:D1))