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?

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?

One way (array-entered):

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

In article <349A5F8F-F142-4CCE-A683-AA92006DD5F0@microsoft.com>,
rmellison <rmellison@discussions.microsoft.com> 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?

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?

Typo John:

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

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-462FB8.09100307092005@msnews.microsoft.com...
One way (array-entered):

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

In article <349A5F8F-F142-4CCE-A683-AA92006DD5F0@microsoft.com>,
rmellison <rmellison@discussions.microsoft.com> 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?

Try this (entered Ctrl+Shift+Enter):

=AVERAGE(IF((MOD(COLUMN(L8:U8),2)=1)*ISNUMBER(L8:U8),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?

Nope.

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

In article <OVPVqJ8sFHA.524@TK2MSFTNGP12.phx.gbl>,
"RagDyeR" <ragdyer@cutoutmsn.com> wrote:

> Typo

I missed that.

Foot-in-mouth disease!<g>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-0E965A.09594607092005@msnews.microsoft.com...
Nope.

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

In article <OVPVqJ8sFHA.524@TK2MSFTNGP12.phx.gbl>,
"RagDyeR" <ragdyer@cutoutmsn.com> wrote:

> Typo

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?