Average of cells with zeros

[vbacoder]

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

 

[Scott Huish]

Like this:

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

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

 

[Joe Was]

=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.

 

[dave3009]

Perhaps

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

Adjust ranges to suit


HTH


Dave

 

[erik.van.geit]

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

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"

 

[vbacoder]

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

 

[erik.van.geit]

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

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

 

[vbacoder]

I see, so using:

=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

{=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

 

[Aladin Akyurek]

I see, so using:

=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

{=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.

 

[vbacoder]

Agreed - problems arise, though, when data sources contain zeros that should actually be blanks. Both approaches have their uses./

vcoder