Using Average function when number is zero

D

Deb

In a set of linked spreadsheets, how can you NOT have the Average function
use the number zero in a calculation. Example, a set of worksheets that
create an average of the past twelve months of figures, however November and
December have not been filled out yet, so the calculation in that slot is
zero. Averaging 12 months with two zeros (which are included by Excel)
changes the figures dramatically. I know a spacebar instead of zero will fix
that however, doing that would wipe out the calculation that is used in other
parts of the spreadsheet. Thanks Deb
 
H

hgrove

duane wrote...
one way -

=SUMPRODUCT((A1:A12<>0)*(A1:A12))/COUNTA(A1:A12)
Click to expand...
...

Exactly backwards. 1 + 2 + 3 = 1 + 2 + 3 + 0 + 0 + 0 . . ., so there'
no benefit to excluding zeros from the sum. On the other hand, you nee
to exclude zeros from the count. So

=SUM(A1:A12)/COUNTIF(A1:A12,">0")

Note also >0. If legitimate values could be negative or positive, the
they could also be zero
 
Top