Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array functions
Thought I was getting the hand of array functions. Clearly not....
Can I use an array function with a set of discontiguous cells, for example (A1,A3,A5,A7,A9) rather than A1:A10? If not can someone suggest away of finding the average value of a set of discontiguous cells, some of which may have numbers, others may have an error code. (Not sure if this is relevent but I have specified any error code to be #N/A to ensure that I can still use the data set for a line chart, and not return a zero value for a cell with errors)? Is there a way without using an array function? |
#2
|
|||
|
|||
Yes you can, is there any pattern to the discontinuous cells? In your
example, it looks like you want an average of just the odd numbered rows? In that case you can use this formula (array formula entered Ctrl+Shift+Enter): =AVERAGE(IF(MOD(ROW(A1:A10),2)=1,A1:A10,"")) Similarly, If you would like the average of the even rows then use: =AVERAGE(IF(MOD(ROW(A1:A10),2)=0,A1:A10,"")) If you would like the average of every third row use: =AVERAGE(IF(MOD(ROW(A1:A10),3)=0,A1:A10,"")) Does that help? -- Regards, Dave "rmellison" wrote: Thought I was getting the hand of array functions. Clearly not.... Can I use an array function with a set of discontiguous cells, for example (A1,A3,A5,A7,A9) rather than A1:A10? If not can someone suggest away of finding the average value of a set of discontiguous cells, some of which may have numbers, others may have an error code. (Not sure if this is relevent but I have specified any error code to be #N/A to ensure that I can still use the data set for a line chart, and not return a zero value for a cell with errors)? Is there a way without using an array function? |
#3
|
|||
|
|||
One way (array-entered):
=AVERAGE(IF(MOD(ROW(A1:A10),2),IF(ISNUMBER(A1:A10) ,A1:A10))) In article , rmellison wrote: Thought I was getting the hand of array functions. Clearly not.... Can I use an array function with a set of discontiguous cells, for example (A1,A3,A5,A7,A9) rather than A1:A10? If not can someone suggest away of finding the average value of a set of discontiguous cells, some of which may have numbers, others may have an error code. (Not sure if this is relevent but I have specified any error code to be #N/A to ensure that I can still use the data set for a line chart, and not return a zero value for a cell with errors)? Is there a way without using an array function? |
#4
|
|||
|
|||
Yes and no.
The array function works, but i'm still unsure as to how to disregard cells containing "#N/A". I'm trying a variation on the theme with: {=AVERAGE(IF(--(MOD(COLUMN(L8:U8),2)=1)*(--(MOD(COLUMN(L8:U8),2)<"#N/A")),L8:U8,""))} But it still yields #N/A if one of these appears within the cell range. Could it be something to do with the fact that the #N/A cell within the array itself is returned from a function - it is returned in an IF function as #N/A rather than the string "#N/A". Perhaps this is relevant? "David Billigmeier" wrote: Yes you can, is there any pattern to the discontinuous cells? In your example, it looks like you want an average of just the odd numbered rows? In that case you can use this formula (array formula entered Ctrl+Shift+Enter): =AVERAGE(IF(MOD(ROW(A1:A10),2)=1,A1:A10,"")) Similarly, If you would like the average of the even rows then use: =AVERAGE(IF(MOD(ROW(A1:A10),2)=0,A1:A10,"")) If you would like the average of every third row use: =AVERAGE(IF(MOD(ROW(A1:A10),3)=0,A1:A10,"")) Does that help? -- Regards, Dave "rmellison" wrote: Thought I was getting the hand of array functions. Clearly not.... Can I use an array function with a set of discontiguous cells, for example (A1,A3,A5,A7,A9) rather than A1:A10? If not can someone suggest away of finding the average value of a set of discontiguous cells, some of which may have numbers, others may have an error code. (Not sure if this is relevent but I have specified any error code to be #N/A to ensure that I can still use the data set for a line chart, and not return a zero value for a cell with errors)? Is there a way without using an array function? |
#5
|
|||
|
|||
Typo John:
=AVERAGE(IF(MOD(ROW(A1:A10),2)=0,IF(ISNUMBER(A1:A1 0),A1:A10))) -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "JE McGimpsey" wrote in message ... One way (array-entered): =AVERAGE(IF(MOD(ROW(A1:A10),2),IF(ISNUMBER(A1:A10) ,A1:A10))) In article , rmellison wrote: Thought I was getting the hand of array functions. Clearly not.... Can I use an array function with a set of discontiguous cells, for example (A1,A3,A5,A7,A9) rather than A1:A10? If not can someone suggest away of finding the average value of a set of discontiguous cells, some of which may have numbers, others may have an error code. (Not sure if this is relevent but I have specified any error code to be #N/A to ensure that I can still use the data set for a line chart, and not return a zero value for a cell with errors)? Is there a way without using an array function? |
#6
|
|||
|
|||
Try this (entered Ctrl+Shift+Enter):
=AVERAGE(IF((MOD(COLUMN(L8:U8),2)=1)*ISNUMBER(L8:U 8),L8:U8,"")) -- Regards, Dave "rmellison" wrote: Yes and no. The array function works, but i'm still unsure as to how to disregard cells containing "#N/A". I'm trying a variation on the theme with: {=AVERAGE(IF(--(MOD(COLUMN(L8:U8),2)=1)*(--(MOD(COLUMN(L8:U8),2)<"#N/A")),L8:U8,""))} But it still yields #N/A if one of these appears within the cell range. Could it be something to do with the fact that the #N/A cell within the array itself is returned from a function - it is returned in an IF function as #N/A rather than the string "#N/A". Perhaps this is relevant? "David Billigmeier" wrote: Yes you can, is there any pattern to the discontinuous cells? In your example, it looks like you want an average of just the odd numbered rows? In that case you can use this formula (array formula entered Ctrl+Shift+Enter): =AVERAGE(IF(MOD(ROW(A1:A10),2)=1,A1:A10,"")) Similarly, If you would like the average of the even rows then use: =AVERAGE(IF(MOD(ROW(A1:A10),2)=0,A1:A10,"")) If you would like the average of every third row use: =AVERAGE(IF(MOD(ROW(A1:A10),3)=0,A1:A10,"")) Does that help? -- Regards, Dave "rmellison" wrote: Thought I was getting the hand of array functions. Clearly not.... Can I use an array function with a set of discontiguous cells, for example (A1,A3,A5,A7,A9) rather than A1:A10? If not can someone suggest away of finding the average value of a set of discontiguous cells, some of which may have numbers, others may have an error code. (Not sure if this is relevent but I have specified any error code to be #N/A to ensure that I can still use the data set for a line chart, and not return a zero value for a cell with errors)? Is there a way without using an array function? |
#7
|
|||
|
|||
Nope.
Your formula averages the even rows. OP asked for the odds. In article , "RagDyeR" wrote: Typo |
#8
|
|||
|
|||
I missed that.
Foot-in-mouth disease!<g -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "JE McGimpsey" wrote in message ... Nope. Your formula averages the even rows. OP asked for the odds. In article , "RagDyeR" wrote: Typo |
#9
|
|||
|
|||
Thanks to all! Both suggested methods work well!
"rmellison" wrote: Thought I was getting the hand of array functions. Clearly not.... Can I use an array function with a set of discontiguous cells, for example (A1,A3,A5,A7,A9) rather than A1:A10? If not can someone suggest away of finding the average value of a set of discontiguous cells, some of which may have numbers, others may have an error code. (Not sure if this is relevent but I have specified any error code to be #N/A to ensure that I can still use the data set for a line chart, and not return a zero value for a cell with errors)? Is there a way without using an array function? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering Array Functions | Excel Discussion (Misc queries) | |||
Array functions ARGHH! | Excel Discussion (Misc queries) | |||
array functions and ISNUMBER() | Excel Worksheet Functions | |||
Array Functions from Alan Beban | Excel Worksheet Functions | |||
Array Functions - Two Questions | Excel Worksheet Functions |