Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Deb
 
Posts: n/a
Default 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
  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

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


  #3   Report Post  
duane
 
Posts: n/a
Default


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

  #4   Report Post  
hgrove
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I find an average number of specific words in a column cashgrfx New Users to Excel 7 January 6th 05 05:44 PM
IF Statement with Average Function results in #Value! Paul Excel Discussion (Misc queries) 5 December 28th 04 09:11 AM
Paste a function as a fixed number Martini25 Excel Discussion (Misc queries) 1 December 21st 04 12:39 PM
#VALUE in cell but pop up function box show right number Ted Dalton Excel Discussion (Misc queries) 1 December 14th 04 04:15 PM
SUM function over infinite number of sheets? Not2Bright Excel Worksheet Functions 1 October 28th 04 02:35 AM


All times are GMT +1. The time now is 07:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"