Averaging Number above Zero

[Mike McLellan]

How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
etc) that are above zero?

[Ron Coderre]

Here are two thoughts (both are array formulas*)....

If your list of cells is not very extensive:
B1: =AVERAGE(IF(ISNUMBER(MATCH(ROW(A1:A16),{1,6,11,16},0))*(A1:A16>0),A1:A16))

OR..if you want row numbers that end in 1 or 6:
B1: =AVERAGE(IF((MOD(ROW(A1:A16),10)={1,6})*(A1:A16>0),A1:A16))

*Note: To commit array an formula, hold down [Ctrl] and [Shift] when you
press [Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike McLellan" wrote:

> How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
> etc) that are above zero?

[Bob Phillips]

=AVERAGE(IF(N(OFFSET(A1,{0,5,10,15},0,1,1))>0,N(OFFSET(A1,{0,5,10,15},0,1,1)
)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike McLellan" <[email protected]> wrote in message
news:[email protected]...
> How do I average the number in a non-contiguous range (e.g. A1, A6, A11,
A16
> etc) that are above zero?

[Bob Phillips]

Or

=SUMPRODUCT(--(MOD(ROW(A1:A1000),5)=1),--(A1:A1000>0),A1:A1000)/SUMPRODUCT(-
-(MOD(ROW(A1:A1000),5)=1),--(A1:A1000>0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike McLellan" <[email protected]> wrote in message
news:[email protected]...
> How do I average the number in a non-contiguous range (e.g. A1, A6, A11,
A16
> etc) that are above zero?

[Ron Coderre]

Here are two thoughts (both are array formulas*)....

If your list of cells is not very extensive:
B1: =AVERAGE(IF(ISNUMBER(MATCH(ROW(A1:A16),{1,6,11,16},0))*(A1:A16>0),A1:A16))

OR..if you want row numbers that end in 1 or 6:
B1: =AVERAGE(IF((MOD(ROW(A1:A16),10)={1,6})*(A1:A16>0),A1:A16))

*Note: To commit array an formula, hold down [Ctrl] and [Shift] when you
press [Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike McLellan" wrote:

> How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
> etc) that are above zero?

[Bob Phillips]

=AVERAGE(IF(N(OFFSET(A1,{0,5,10,15},0,1,1))>0,N(OFFSET(A1,{0,5,10,15},0,1,1)
)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike McLellan" <[email protected]> wrote in message
news:[email protected]...
> How do I average the number in a non-contiguous range (e.g. A1, A6, A11,
A16
> etc) that are above zero?

[Bob Phillips]

Or

=SUMPRODUCT(--(MOD(ROW(A1:A1000),5)=1),--(A1:A1000>0),A1:A1000)/SUMPRODUCT(-
-(MOD(ROW(A1:A1000),5)=1),--(A1:A1000>0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike McLellan" <[email protected]> wrote in message
news:[email protected]...
> How do I average the number in a non-contiguous range (e.g. A1, A6, A11,
A16
> etc) that are above zero?

[Bob Phillips]

=AVERAGE(IF(N(OFFSET(A1,{0,5,10,15},0,1,1))>0,N(OFFSET(A1,{0,5,10,15},0,1,1)
)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike McLellan" <[email protected]> wrote in message
news:[email protected]...
> How do I average the number in a non-contiguous range (e.g. A1, A6, A11,
A16
> etc) that are above zero?

[Bob Phillips]

Or

=SUMPRODUCT(--(MOD(ROW(A1:A1000),5)=1),--(A1:A1000>0),A1:A1000)/SUMPRODUCT(-
-(MOD(ROW(A1:A1000),5)=1),--(A1:A1000>0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike McLellan" <[email protected]> wrote in message
news:[email protected]...
> How do I average the number in a non-contiguous range (e.g. A1, A6, A11,
A16
> etc) that are above zero?

[Mike McLellan]

Ron,

Many thanks for your help.

I've input the following ...

=AVERAGE(IF(ISNUMBER(MATCH(ROW(J10:J107),{15,23,32,40,48,57,65,73,82,90,98,107},0))*(J10:J107>0),J10:J107))

and yet it is coming up with an #VALUE error. All the cells in the
specified list (15,23,32 etc) contain a valid number (0 or greater) but the
other cells in the range contain (mostly) spaces - is this what is causing
the problem?

Mike

"Ron Coderre" wrote:

> Here are two thoughts (both are array formulas*)....
>
> If your list of cells is not very extensive:
> B1: =AVERAGE(IF(ISNUMBER(MATCH(ROW(A1:A16),{1,6,11,16},0))*(A1:A16>0),A1:A16))
>
> OR..if you want row numbers that end in 1 or 6:
> B1: =AVERAGE(IF((MOD(ROW(A1:A16),10)={1,6})*(A1:A16>0),A1:A16))
>
> *Note: To commit array an formula, hold down [Ctrl] and [Shift] when you
> press [Enter].
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Mike McLellan" wrote:
>
> > How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
> > etc) that are above zero?

[Bob Phillips]

It needs to be array entered, Ctrl-Shift-Enter, not just enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike McLellan" <[email protected]> wrote in message
news:[email protected]...
> Ron,
>
> Many thanks for your help.
>
> I've input the following ...
>
>
=AVERAGE(IF(ISNUMBER(MATCH(ROW(J10:J107),{15,23,32,40,48,57,65,73,82,90,98,1
07},0))*(J10:J107>0),J10:J107))
>
> and yet it is coming up with an #VALUE error. All the cells in the
> specified list (15,23,32 etc) contain a valid number (0 or greater) but
the
> other cells in the range contain (mostly) spaces - is this what is causing
> the problem?
>
> Mike
>
> "Ron Coderre" wrote:
>
> > Here are two thoughts (both are array formulas*)....
> >
> > If your list of cells is not very extensive:
> > B1:
=AVERAGE(IF(ISNUMBER(MATCH(ROW(A1:A16),{1,6,11,16},0))*(A1:A16>0),A1:A16))
> >
> > OR..if you want row numbers that end in 1 or 6:
> > B1: =AVERAGE(IF((MOD(ROW(A1:A16),10)={1,6})*(A1:A16>0),A1:A16))
> >
> > *Note: To commit array an formula, hold down [Ctrl] and [Shift] when you
> > press [Enter].
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Mike McLellan" wrote:
> >
> > > How do I average the number in a non-contiguous range (e.g. A1, A6,
A11, A16
> > > etc) that are above zero?

[Mike McLellan]

Sorry - missed this in my eagerness to try out the suggested formula!

Thanks to everyone for their help

Mike


"Bob Phillips" wrote:

> It needs to be array entered, Ctrl-Shift-Enter, not just enter.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Mike McLellan" <[email protected]> wrote in message
> news:[email protected]...
> > Ron,
> >
> > Many thanks for your help.
> >
> > I've input the following ...
> >
> >
> =AVERAGE(IF(ISNUMBER(MATCH(ROW(J10:J107),{15,23,32,40,48,57,65,73,82,90,98,1
> 07},0))*(J10:J107>0),J10:J107))
> >
> > and yet it is coming up with an #VALUE error. All the cells in the
> > specified list (15,23,32 etc) contain a valid number (0 or greater) but
> the
> > other cells in the range contain (mostly) spaces - is this what is causing
> > the problem?
> >
> > Mike
> >
> > "Ron Coderre" wrote:
> >
> > > Here are two thoughts (both are array formulas*)....
> > >
> > > If your list of cells is not very extensive:
> > > B1:
> =AVERAGE(IF(ISNUMBER(MATCH(ROW(A1:A16),{1,6,11,16},0))*(A1:A16>0),A1:A16))
> > >
> > > OR..if you want row numbers that end in 1 or 6:
> > > B1: =AVERAGE(IF((MOD(ROW(A1:A16),10)={1,6})*(A1:A16>0),A1:A16))
> > >
> > > *Note: To commit array an formula, hold down [Ctrl] and [Shift] when you
> > > press [Enter].
> > >
> > > Does that help?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "Mike McLellan" wrote:
> > >
> > > > How do I average the number in a non-contiguous range (e.g. A1, A6,
> A11, A16
> > > > etc) that are above zero?
>
>
>