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]

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!

Try one of these...

If you're using Excel 2007 or later:

=AVERAGEIF(C2:C32,"<>0")

This array formula** will work in all versions of Excel:

=AVERAGE(IF(C2:C32<>0,C2:C32))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Teethless mama]

All versions:
=AVERAGE(IF(C2:C32<>0,C2:C32))

Press ctrl+shift+enter, not just enter


Excel 2007 and higher:
=AVERAGEIF(C2:C32,"<>0",C2:C32)

Just enter normally

 

[T. Valko]

Excel 2007 and higher:
=AVERAGEIF(C2:C32,"<>0",C2:C32)

No need to repeat the average range:

=AVERAGEIF(C2:C32,"<>0")

 

[Saffire]

Those all work very well, thank you. Just a teensy little thing ... how would I get it to display 0 instead of #DIV/0 ... probably something so easy a kindergartner could do it, I'm sure. Thanks for all the help!

 

[T. Valko]

Those all work very well, thank you. Just a teensy little thing ... how would I get it to display 0 instead of #DIV/0 ... probably something so easy a kindergartner could do it, I'm sure. Thanks for all the help!

What version of Excel are you using?

 

[T. Valko]

What version of Excel are you using?

Well, I guess if "Those all work..." then that means you're using at least Excel 2007...

So, try this:

=IFERROR(AVERAGEIF(C2:C32,"<>0"),0)

 

[Saffire]

Yes, I am using Office 2007 and you are a total lifesaver! Bossman didn't believe me when I told him we would not have to do all those calculations by hand. I love the 'fossil fuels', sometimes I wonder how they managed without Excel ... let alone without computers :D

 

[T. Valko]

Yes, I am using Office 2007 and you are a total lifesaver! Bossman didn't believe me when I told him we would not have to do all those calculations by hand. I love the 'fossil fuels', sometimes I wonder how they managed without Excel ... let alone without computers :D

You're welcome. Thanks for the feedback!

 

[Saffire]

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"))}