Average ERROR

[Ed Davis]

I am using the formulas below for a couple of ranges.
Most work but one is giving me a total of all cells instead of the average.

Example
C36 = 6.00
C37 = 6.00
This formula gives me 12.00 instead of 6.00.
=SUM(C36:C200)/MAX(1,COUNTIF(C36:C200,">0"))

Other ranges work fine.
B36 = 119
B37 = 119
This formula gives me 119 (correct)
=SUM(B36:B200)/MAX(1,COUNTIF(B36:B200,">0"))

I am trying to get the average of the range counting only the cells that
have a number other than "0" in them. There may be cells in between that are
blank of do not have a numbers or zero's.

 

[Dave Peterson]

Do you have any hidden rows in that range (36:200). Maybe the stuff you can't
see is affecting the average.

(Your formula worked fine for me.)

 

[Ed Davis]

I changed the formula to only include rows 36 - 40 and this is what I get.

=SUM(C36:C40)/MAX(1,COUNTIF(C36:C40,">0"))



120 $ (20.00) 1.80%
Average Average Average Average
Date Speed Cash Voids
9/11/2008 119.5 $ (6.25) 1.80%
9/11/2008 119.5 $ (6.25) 1.80%
9/11/2008 119.5 $ (6.25) 1.80%
9/11/2008 119.5 $ (1.25) 1.80%
9/11/2008 119.5 $ - 1.80%

 

[Dave Peterson]

Does C36:C40 contain 119.5?
and are you getting 120?

If yes, either widen the column with the formula
or change the font size for that cell
or change the number format to include decimals.

 

[Ed Davis]

I sent this about 20 min ago but never posted. Her it is again.

The 120 is correct it is the (20.00) that is not correct. It has -6.25 3
times and then -1.25

 

[Dave Peterson]

Your divisor is only counting the numbers that are greater than 0.

If you want to include the negative numbers, change your formulas to:

=SUM(C36:C40)/MAX(1,COUNTIF(C36:C40,"<>0"))

Personally, I like this syntax (but they are equivalent):

=SUM(C36:C40)/MAX(1,COUNTIF(C36:C40,"<>"&0))

I think it's easier to see what's going on and to change the 0 to point to a
cell:

=SUM(C36:C40)/MAX(1,COUNTIF(C36:C40,"<>"&x999))
(for instance)

I sent this about 20 min ago but never posted. Her it is again.

The 120 is correct it is the (20.00) that is not correct. It has -6.25 3
times and then -1.25

 

[Dave Peterson]

ps. If you may have empty cells in your range, you may want:

=SUM(C36:C40)/MAX(1,(COUNT(C36:C40)-COUNTIF(C36:C40,0)))

I sent this about 20 min ago but never posted. Her it is again.

The 120 is correct it is the (20.00) that is not correct. It has -6.25 3
times and then -1.25

 

[Ed Davis]

This worked just fine.

Thank you so much for your help.