Averaging w/o zeroes or blanks
Posted by Mike on July 29, 2001 12:09 AM
I'd like to have a formula that would average numbers in a set range of cells, but ignore blanks or zeroes in that range. Any ideas?
Posted by Aladin Akyurek on July 29, 2001 5:01 AM
Two ideas:
(1) A formula with SUMPRODUCT (my preference in this case)
=SUM(A1:A5)/(SUMPRODUCT((A1:A5<>0)+0))
(2) The famous array formula:
=AVERAGE(IF(A1:A10,A1:A10))
You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter an array formula.
Both will ignore 0's and blanks.
Aladin
Posted by Mike on July 29, 2001 9:10 AM
Thanks again for your help, of course I realized as soon as I posted the question that the Average function already ignored blanks in a range, what I really needed was to ignore zeroes. Of course both of the formulas that you suggested worked. I used the first one. May I ask why that was your preference?
Posted by Aladin Akyurek on July 29, 2001 9:28 AM
It does not require a special method of entering and care, which is of course a minor point. Although I don't have any evidence yet, it may perform better (i.e., may require less time) on larger ranges of cells.
Aladin
