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(IF(B5:B17<>0,B5:B17)) & enter this as an array formula via
Ctrl+Shift+Enter

or in Excel 2007

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

On Feb 7, 9:06*am, Cliff <Ragsdal...@gmail.com> 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!

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

"(E-Mail Removed)" wrote:

> On Feb 7, 9:06 am, Cliff <Ragsdal...@gmail.com> 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!
>

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))
>
> "(E-Mail Removed)" wrote:
>
> > On Feb 7, 9:06 am, Cliff <Ragsdal...@gmail.com> 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!
> >

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))
> >
> > "(E-Mail Removed)" wrote:
> >
> > > On Feb 7, 9:06 am, Cliff <Ragsdal...@gmail.com> 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!
> > >