Average of cells with zeros

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Just a quickie,

I want to take an average of only non-zero numbers in a range of cells containing a few zeros. I know there is a function to do this, but I haven't been able to find it. With the normal average() function, the denominator is too large as cells with 0 are included.

vcoder
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
Like this:

=AVERAGE(IF(A1:A1000<>0,A1:A1000))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
 
Upvote 0

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
=AVERAGE(IF(A1:A100<>0, A1:A100,""))

Select the cell Formula cell then, Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.
 
Upvote 0

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Perhaps

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

Adjust ranges to suit


HTH


Dave
 
Upvote 0

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

try this
  A  B  
1 5  5  
2 4     
3 0     
4 6     
5 0     
6 7     
7 3     
8 8     
9 2     

Sheet1

[Table-It] version 07 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
B1    {=AVERAGE(IF(A1:A9<>0,A1:A9))}

{=formula}:
 enter formula without {}
 confirm with Control-Shift-Enter

[Table-It] version 07 by Erik Van Geit

kind regards,
Erik

EDIT: seems we jumped onto this one "en masse" :LOL:
 
Upvote 0

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Thank you very much for all your replies! I like lots of replies because it's a great way to learn a range of tricks.

vcoder
 
Upvote 0

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Be aware of different behaviour between the "countif" and "average(if..."

  A  B     
1 5  5     
2 4  4,375 
3          
4 6        
5 0        
6 7        
7 3        
8 8        
9 2        

sheet1

[Table-It] version 07 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
B1    {=AVERAGE(IF(A1:A9<>0,A1:A9))}
B2    =SUM(A1:A9)/COUNTIF(A1:A9, "<>0")

{=formula}:
 enter formula without {}
 confirm with Control-Shift-Enter

[Table-It] version 07 by Erik Van Geit

1st formula "empty" = zero
2nd formula "empty" <> 0
 
Upvote 0

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
I see, so using:
Code:
=SUM(A1:A9)/COUNTIF(A1:A9, "<>0")

...means that blanks are counted in the averaging calculation - because they are not equal to zero. Whereas using
Code:
{=AVERAGE(IF(A1:A9<>0,A1:A9))}
will include zero valued data in the calculation. I guess I do actually want to include zeros if this is true data, but the problem is some data sources contain 0's in place of missing data...

Many thanks for clarifying the difference between the two approaches Erik.

vcoder
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
I see, so using:
Code:
=SUM(A1:A9)/COUNTIF(A1:A9, "<>0")

...means that blanks are counted in the averaging calculation - because it is not equal to zero. Whereas using
Code:
{=AVERAGE(IF(A1:A9<>0,A1:A9))}
will include zero valued data in the calculation. I guess I do actually want to include zeros if this is true data, but the problem is some data sources contain 0's in place of missing data...

Many thanks for clarifying the difference between the two approaches Erik.

vcoder

I think it's mathematically not sound to exclude true 0's from an average for numbers: ...,-2,-1,0,1,2,..

If you just want to average positive numbers excluding negs and 0's...

=SUMIF(Range,">0")/COUNTIF(Range,">0")

If there is no negs in Range...

=SUM(Range)/COUNTIF(Range,">0")

will suffice.
 
Upvote 0

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Agreed - problems arise, though, when data sources contain zeros that should actually be blanks. Both approaches have their uses./

vcoder
 
Upvote 0

Forum statistics

Threads
1,191,723
Messages
5,988,308
Members
440,148
Latest member
sandy123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top