Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging Number above Zero
How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16
etc) that are above zero? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging Number 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:A160),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:A160) ,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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging Number above Zero
=AVERAGE(IF(N(OFFSET(A1,{0,5,10,15},0,1,1))0,N(OF FSET(A1,{0,5,10,15},0,1,1)
))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mike McLellan" wrote in message ... How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16 etc) that are above zero? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging Number above Zero
Or
=SUMPRODUCT(--(MOD(ROW(A1:A1000),5)=1),--(A1:A10000),A1:A1000)/SUMPRODUCT(- -(MOD(ROW(A1:A1000),5)=1),--(A1:A10000)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mike McLellan" wrote in message ... How do I average the number in a non-contiguous range (e.g. A1, A6, A11, A16 etc) that are above zero? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging Number 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:J1070),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:A160),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:A160) ,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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging Number 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) "Mike McLellan" wrote in message ... 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:J1070),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:A160),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:A160) ,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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging Number above Zero
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" wrote in message ... 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:J1070),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:A160),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:A160) ,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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating (in a random order)each number once from a given number | Excel Worksheet Functions | |||
Change number format from text to number? | New Users to Excel | |||
number format | Excel Discussion (Misc queries) | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) |