Using Average function when number is zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
duane wrote...
one way -

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top