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" <Mark@discussions.microsoft.com> wrote in message
news:B88586C7-8AEC-43B7-94CC-E8006FCEFD77@microsoft.com...
> 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" <Mark@discussions.microsoft.com> wrote in message
news:B88586C7-8AEC-43B7-94CC-E8006FCEFD77@microsoft.com...
> 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:D1>0,A1:D1))

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

Vaya con Dios,
Chuck, CABGx3


"Mark" <Mark@discussions.microsoft.com> wrote in message
news:B88586C7-8AEC-43B7-94CC-E8006FCEFD77@microsoft.com...
> 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?
>.
>