Average Formula Help

[EAB1977]

Hello everyone,

I have a set of percents (cells B30 - I30) that I would like to
average, but I want to eliminate any percents that equal zero. Using
the average function, it takes into account zeros, thus skewing my
average. Is there a function that I can use that will eliminate zeros
from my average and give a true percent average?

 

[Claus Busch]

Hi,

Am Thu, 9 Jun 2011 08:58:28 -0700 (PDT) schrieb EAB1977:

I have a set of percents (cells B30 - I30) that I would like to
average, but I want to eliminate any percents that equal zero. Using
the average function, it takes into account zeros, thus skewing my
average. Is there a function that I can use that will eliminate zeros
from my average and give a true percent average?

try:
=SUM(B30:I30)/COUNTIF(B30:I30,">0")
or array formula (STRG+Shift+Enter):
=AVERAGE(IF(B30:I30,B30:I30))


Regards
Claus Busch

 

[EAB1977]

That worked! Thank you!