A wee bit of difficulty with Count Function

[Saffire]

Hey everyone,

This is going to probably be one of those "D'OH!" questions but here I go.

I have a range (C2:C32) that has some zero values. I need to average the range WITHOUT the zeros. I believe that a counting function would work, but of course I am having one of those blonde moments that I am notorious for.

Here is the formula that I was originally trying:
=COUNT(C2:C32>0)AVERAGE(C2:C32)

Of course, my excel sheet called me some very bad names when I entered that!

 

[T. Valko]

Okay, ran into another SNAFU on this subject. Everything works perfectly for me in Excel 2007, however Bossman refuses to let go of his Excel 2003 stating that he has "just now gotten use to it"! I need to revise this formula to work in 2003 without getting back a #DIV/0 result. Any ideas?

I have been using the array:

{=AVERAGE(IF(J2:J32<>0,J2:J32,"0"))}

Will the numbers always be positive numbers?

 

[Saffire]

Yes, however there will also be some times where there is not data (thus the 0s) but I want to have the same formula across the board so that when data is added in the future it will automatically update that calculation.

 

[T. Valko]

Yes, however there will also be some times where there is not data (thus the 0s) but I want to have the same formula across the board so that when data is added in the future it will automatically update that calculation.

Try this...

=SUM(J2:J32)/MAX(1,COUNTIF(J2:J32,">0"))

 

[Saffire]

You are an Excel GOD! Thank you so very much for you help!

 

[T. Valko]

You are an Excel GOD! Thank you so very much for you help!

You're welcome. Thanks for the feedback!