MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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<>0,A1:A100))

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

OzGrid Business Applications

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

More info


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


Dave


OzGrid Business Applications

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

Aladin

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

Re: More info


Thanks that worked Great!!!

Thanks for your Help

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

Bad habit


Allan, I would refrain from using a shorter formula. While it may save you 5 seconds intially, it is very bad practice as it does not give any indication as to what the argument is within the formula. You will find yourself looking at the formula later on and thinking, 'now what does this do? If another user has to use the spreadsheet they certainly wont!


Dave

OzGrid Business Applications