Average - without zero?
 

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!

Average - without zero?
 

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

Average - without zero?
 

On Feb 7, 9:06*am, Cliff wrote:
=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!

Average - without zero?
 

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

" wrote:

On Feb 7, 9:06 am, Cliff wrote:
=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!

Average - without zero?
 

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" wrote:

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

" wrote:

On Feb 7, 9:06 am, Cliff wrote:
=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!

Average - without zero?
 

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!!

"Mike H" wrote:

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" wrote:

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

" wrote:

On Feb 7, 9:06 am, Cliff wrote:
=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!