Using Average function when number is zero
 

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

Array formula entered with CTRL+SHIFT+ENTER:

=AVERAGE(IF(A1:L10,A1:L1))


On Fri, 5 Nov 2004 12:37:03 -0800, Deb wrote:

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

one way -

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

for the range a1:a12


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=275766

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's
no benefit to excluding zeros from the sum. On the other hand, you need
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, then
they could also be zero.


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=275766