I've searched but can't find info on averaging

Posted by Ross on January 16, 2002 5:47 AM

How do I write an Average formula that omits zeros from the calculation?

Posted by Aladin Akyurek on January 16, 2002 6:04 AM

[1]

=SUM(A1:A100)/MAX(1,COUNTIF(A1:A100,"&LT;&GT;0")-COUNTBLANK(A1:A100))

[2]

=SUM(A1:A7)/MAX(1,SUMPRODUCT((A1:A7&LT;&GT;0)*(ISNUMBER(A1:A7))))

[3] Array-enter:

=AVERAGE(IF(ISNUMBER(A1:A7)*(A1:A7&LT;&GT;0),A1:A7))

will exclude 0's as well as blanks (but, intendedly, not neg values).

In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter. I'd suggest using [1].

Aladin

Posted by Ross on January 16, 2002 6:49 AM

Thanks!! nm

Posted by Mark W. on January 16, 2002 9:05 AM

Use the array formula, {=AVERAGE(IF(A1:A7,A1:A7))}