Average - without zero?

[cjwenngatz]

Hello,

I have a sheet that I require a monthly average for, however the
number of months in each column varies. Therefore I've been using the
formula: =AVERAGE(IF(ISNUMBER(B5:B17),B5:B17))

The problem is, this formula adds any zero's into the formula, and
that skews the average. Any ideas on how to change this? An example of
a table I'm using would be:

Jan 12
Feb 14
Mar 21
Apr 0
May 0
Jun 0
Jul 0

I want it to only average those cells that have a number other than 0.

Thanks for any help!

 

[Cliff]

=AVERAGE(IF(B5:B17<>0,B5:B17)) & enter this as an array formula via
Ctrl+Shift+Enter

or in Excel 2007

=AVERAGEIF(B5:B17,"<>0")

 

[cjwenngatz]

=AVERAGE(IF(B5:B17<>0,B5:B17))  & enter this as an array formula via
Ctrl+Shift+Enter

or in Excel 2007

=AVERAGEIF(B5:B17,"<>0")

Thanks Cliff, that worked perfectly!

 

[GeoffWhite18]

Ok so I tried that and end up with a #VALUE! error. What I'm trying to do is
find the average Through cells G2:G50 and ignor any values less than 1.
=AVERAGE(IF(G2:G50<>0,G2:G50))

 

[Mike H]

Thay's because it's an array formula. Type the formula in the cell then
commit it by pressing

Ctrl+Shift+Enter

If you've done it corrrectly then Excel will put curly brackets around the
formula.

Mike

 

[GeoffWhite18]

Ahh. I was doing Ctrl+Shift+Enter before I put in the formula. Works great
now! I don't know how you guys know all this stuff but going through these
threads in here has saved me a lot of headache! Thanks!!