G
Guest
How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
etc) that are above zero?
etc) that are above zero?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
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
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
B
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)
)))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
B
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)
=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)
G
Guest
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
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 said: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 said:How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
etc) that are above zero?
B
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)
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
Mike McLellan said: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 said: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 said:How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
etc) that are above zero?
G
Guest
Sorry - missed this in my eagerness to try out the suggested formula!
Thanks to everyone for their help
Mike
Thanks to everyone for their help
Mike
Bob Phillips said: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 said: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 said: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
:
How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
etc) that are above zero?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.