# Average

Posted by Michael Saunders on March 20, 2001 5:31 AM

How do I do an average but tell it to omit 0 if its in a cell

IE

I want to average 9 8 4 5 3 0 4 5 3 0 4 0 0
but I don't want the 0's to be averaged... ???

Can anyone help ??? Thanks

Posted by Dave Hawley on March 20, 2001 5:37 AM

Michael, The quickest way is with an array formula: =AVERAGE(IF(A1:A100&LT;&GT;0,A1:A100))

But personall I would prefer the DAVERAGE formula!
Let me know if interested.

Posted by Dave Hawley on March 20, 2001 5:39 AM

Negleted to say, you MUST enter any array formula by pushing Ctrl+Shift+Enter

Dave

Posted by Aladin Akyurek on March 20, 2001 5:39 AM

Array-enter (that is, hit CONTROL+SHIF+ENTER at the same time to enter)the following formula

=AVERAGE(IF(A1:A4>0;A1:A4))

Posted by Michael Saunders on March 20, 2001 5:45 AM

Thanks that worked Great!!!

Posted by Mark W. on March 20, 2001 6:26 AM

Even simplier...

Michael, since the IF() function treats any non-zero
numeric value as TRUE and zero values as FALSE there
is no need for the conditional operator in Dave's
formulation.

{=AVERAGE(IF(A1:A100,A1:A100))} will suffice.

Posted by Dave Hawley on March 20, 2001 11:58 PM