software

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

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:A16gt;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:A16gt;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-Proquot;Mike McLellanquot; wrote:

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

=AVERAGE(IF(N(OFFSET(A1,{0,5,10,15},0,1,1))gt;0,N(OF FSET(A1,{0,5,10,15},0,1,1)
)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Mike McLellanquot; gt; wrote in message
...
gt; How do I average the number in a non-contiguous range (e.g. A1, A6, A11,
A16
gt; etc) that are above zero?
Or

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Mike McLellanquot; gt; wrote in message
...
gt; How do I average the number in a non-contiguous range (e.g. A1, A6, A11,
A16
gt; etc) that are above zero?
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:J107gt;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

quot;Ron Coderrequot; wrote:

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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

Thanks to everyone for their help

Mikequot;Bob Phillipsquot; wrote:

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