MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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