Array functions

[Guest]

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?

 

[Guest]

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?

 

[JE McGimpsey]

One way (array-entered):

=AVERAGE(IF(MOD(ROW(A1:A10),2),IF(ISNUMBER(A1:A10),A1:A10)))

 

[Guest]

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?

 

[RagDyeR]

Typo John:

=AVERAGE(IF(MOD(ROW(A1:A10),2)=0,IF(ISNUMBER(A1:A10),A1:A10)))

 

[Guest]

Try this (entered Ctrl+Shift+Enter):

=AVERAGE(IF((MOD(COLUMN(L8:U8),2)=1)*ISNUMBER(L8:U8),L8:U8,""))

 

[JE McGimpsey]

Nope.

Your formula averages the even rows. OP asked for the odds.

 

[RagDyeR]

I missed that.

Foot-in-mouth disease!<g>

 

[Guest]

Thanks to all! Both suggested methods work well!