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
