Averaging noncontiguous numbers ignoring zeros?

[Biff]

Hi!

Is there a specific pattern like your example of A1, A3,
A5 or are the cells scattered all over the place?

Biff

>-----Original Message-----
>Averaging noncontiguous numbers ignoring zeros?
>
>I'm trying to calculate an average but I want to not
include any values of
>zero. I've seen examples here and in Microsft help for
contiguous values (ex:
>A1:A7) but not for the averages of noncontiguous data
(ex: A1, A3, A5).
>
>I found Microsoft's example using contiguous data
under "Calculate the
>average of numbers, ignoring zero (0) values". They show
how to average
>A1:A6 using:
>=AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to
revise this
>formula to calculate noncontiguous data. Any ideas?
>--
>Cheers,
>Mike
>.
>

[Andy Brown]

"Mike" <[email protected]> wrote in message
news:[email protected]...
> Averaging noncontiguous numbers ignoring zeros?

> =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
> formula to calculate noncontiguous data. Any ideas?

=SUM(A2:A7)/COUNTIF(A2:A7,">0")

, possibly.

HTH,
Andy

[Mike]

There is a specific pattern. All the cells are in the same row with two
columns of data after each cell.

"Biff" wrote:

> Hi!
>
> Is there a specific pattern like your example of A1, A3,
> A5 or are the cells scattered all over the place?
>
> Biff
>
> >-----Original Message-----
> >Averaging noncontiguous numbers ignoring zeros?
> >
> >I'm trying to calculate an average but I want to not
> include any values of
> >zero. I've seen examples here and in Microsft help for
> contiguous values (ex:
> >A1:A7) but not for the averages of noncontiguous data
> (ex: A1, A3, A5).
> >
> >I found Microsoft's example using contiguous data
> under "Calculate the
> >average of numbers, ignoring zero (0) values". They show
> how to average
> >A1:A6 using:
> >=AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to
> revise this
> >formula to calculate noncontiguous data. Any ideas?
> >--
> >Cheers,
> >Mike
> >.
> >
>

[Mike]

Averaging noncontiguous numbers ignoring zeros?

I'm trying to calculate an average but I want to not include any values of
zero. I've seen examples here and in Microsft help for contiguous values (ex:
A1:A7) but not for the averages of noncontiguous data (ex: A1, A3, A5).

I found Microsoft's example using contiguous data under "Calculate the
average of numbers, ignoring zero (0) values". They show how to average
A1:A6 using:
=AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
formula to calculate noncontiguous data. Any ideas?
--
Cheers,
Mike

[Dave R.]

Here is an array example of how MOD could come into play;

=AVERAGE(IF((MOD(COLUMN(A1:P1)-1,3)=0)*(A1:P1<>0),A1:P1))

with your values in A1:P1, averaging every 3rd column if <> 0.





"Mike" <[email protected]> wrote in message
news:[email protected]...
> Myrna,
> Thanks, but not sure if I follow your idea. I "simply" want to average
only
> those cells in A1, A3, and A5 that have a value <>0 . Not sure how MOD
would
> work in this case.
>
> "Myrna Larson" wrote:
>
> > But A2:A7 is not a group of non-contiguous cells. I think the example he
gave
> > was A1, A3, A5
> >
> > That can be solved with an array formula that involves MOD and the the
row
> > number, but if he wants something like A2:A7, B9:B13, C1:C3, etc., that
won't
> > work, either.
> >
> > Another possibility is to select the various cells and assign a name to
them.
> > Then a simple =AVERAGE(MyRange) will do.
> >
> > On Thu, 3 Mar 2005 23:00:00 -0000, "Andy Brown"
<[email protected]>
> > wrote:
> >
> > >"Mike" <[email protected]> wrote in message
> > >news:[email protected]...
> > >> Averaging noncontiguous numbers ignoring zeros?
> > >
> > >> =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
> > >> formula to calculate noncontiguous data. Any ideas?
> > >
> > >=SUM(A2:A7)/COUNTIF(A2:A7,">0")
> > >
> > >, possibly.
> > >
> > >HTH,
> > >Andy
> > >
> >
> >

[Mike]

OK, I figured it out. Here's my solution:
=(C26+G26+K26)/(3-(COUNTIF(K26,0)+(COUNTIF(G26,0))+(COUNTIF(C26,0))))

Still open for any other approaches that work.

"Mike" wrote:

> Averaging noncontiguous numbers ignoring zeros?
>
> I'm trying to calculate an average but I want to not include any values of
> zero. I've seen examples here and in Microsft help for contiguous values (ex:
> A1:A7) but not for the averages of noncontiguous data (ex: A1, A3, A5).
>
> I found Microsoft's example using contiguous data under "Calculate the
> average of numbers, ignoring zero (0) values". They show how to average
> A1:A6 using:
> =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
> formula to calculate noncontiguous data. Any ideas?
> --
> Cheers,
> Mike

[Mike]

Myrna,
Thanks, but not sure if I follow your idea. I "simply" want to average only
those cells in A1, A3, and A5 that have a value <>0 . Not sure how MOD would
work in this case.

"Myrna Larson" wrote:

> But A2:A7 is not a group of non-contiguous cells. I think the example he gave
> was A1, A3, A5
>
> That can be solved with an array formula that involves MOD and the the row
> number, but if he wants something like A2:A7, B9:B13, C1:C3, etc., that won't
> work, either.
>
> Another possibility is to select the various cells and assign a name to them.
> Then a simple =AVERAGE(MyRange) will do.
>
> On Thu, 3 Mar 2005 23:00:00 -0000, "Andy Brown" <[email protected]>
> wrote:
>
> >"Mike" <[email protected]> wrote in message
> >news:[email protected]...
> >> Averaging noncontiguous numbers ignoring zeros?
> >
> >> =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
> >> formula to calculate noncontiguous data. Any ideas?
> >
> >=SUM(A2:A7)/COUNTIF(A2:A7,">0")
> >
> >, possibly.
> >
> >HTH,
> >Andy
> >
>
>

[Andy Brown]

"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> But A2:A7 is not a group of non-contiguous cells. I think the example he
gave
> was A1, A3, A5

Of course, but A2:A7 can *contain* a group of non-contiguous cells.

[Andy Brown]

"Mike" <[email protected]> wrote in message
news:[email protected]...
> Unfortunately the data is not in a range so I can't use any A2:A7
functions.

Where is data if not in a range?

[Mike]

Unfortunately the data is not in a range so I can't use any A2:A7 functions.

"Andy Brown" wrote:

> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > Averaging noncontiguous numbers ignoring zeros?
>
> > =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
> > formula to calculate noncontiguous data. Any ideas?
>
> =SUM(A2:A7)/COUNTIF(A2:A7,">0")
>
> , possibly.
>
> HTH,
> Andy
>
>
>

[Myrna Larson]

But A2:A7 is not a group of non-contiguous cells. I think the example he gave
was A1, A3, A5

That can be solved with an array formula that involves MOD and the the row
number, but if he wants something like A2:A7, B9:B13, C1:C3, etc., that won't
work, either.

Another possibility is to select the various cells and assign a name to them.
Then a simple =AVERAGE(MyRange) will do.

On Thu, 3 Mar 2005 23:00:00 -0000, "Andy Brown" <[email protected]>
wrote:

>"Mike" <[email protected]> wrote in message
>news:[email protected]...
>> Averaging noncontiguous numbers ignoring zeros?
>
>> =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
>> formula to calculate noncontiguous data. Any ideas?
>
>=SUM(A2:A7)/COUNTIF(A2:A7,">0")
>
>, possibly.
>
>HTH,
>Andy
>

[Myrna Larson]

Huh? Do you mean that all of the cells in A2:A7 aren't filled, i.e. the DATA
isn't contiguous (certainly those CELLS are contiguous).

Let's say there is data in ALL of those cells, but he only wants to average
A3, A4, A5, and A6. I don't see how your formula will average just those
cells.

On Thu, 3 Mar 2005 23:16:54 -0000, "Andy Brown" <[email protected]>
wrote:

>"Myrna Larson" <[email protected]> wrote in message
>news:[email protected]...
>> But A2:A7 is not a group of non-contiguous cells. I think the example he
>gave
>> was A1, A3, A5
>
>Of course, but A2:A7 can *contain* a group of non-contiguous cells.
>

[Myrna Larson]

MOD would work in the case of A1, A3, and A5, where you want every-other
column. The array formula would be

=AVERAGE(IF(MOD(ROW(A1:A5),2)=1,IF(A1:A5<>0,A1:A5)))

But that will not work for the example/solution you posted most recently,
which refers to cells C26, G26, and K26. The column numbers are 3, 7, and 11,
so there's no numeric relationship between them.

OTOH, if you wanted C26, G26, and *J26*, you could use

MOD(COLUMN(C26:J26),4)=3

because that's every 4th column.


On Thu, 3 Mar 2005 15:25:06 -0800, "Mike" <[email protected]>
wrote:

>Myrna,
>Thanks, but not sure if I follow your idea. I "simply" want to average only
>those cells in A1, A3, and A5 that have a value <>0 . Not sure how MOD would
>work in this case.
>
>"Myrna Larson" wrote:
>
>> But A2:A7 is not a group of non-contiguous cells. I think the example he
gave
>> was A1, A3, A5
>>
>> That can be solved with an array formula that involves MOD and the the row
>> number, but if he wants something like A2:A7, B9:B13, C1:C3, etc., that
won't
>> work, either.
>>
>> Another possibility is to select the various cells and assign a name to
them.
>> Then a simple =AVERAGE(MyRange) will do.
>>
>> On Thu, 3 Mar 2005 23:00:00 -0000, "Andy Brown" <[email protected]>
>> wrote:
>>
>> >"Mike" <[email protected]> wrote in message
>> >news:[email protected]...
>> >> Averaging noncontiguous numbers ignoring zeros?
>> >
>> >> =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
>> >> formula to calculate noncontiguous data. Any ideas?
>> >
>> >=SUM(A2:A7)/COUNTIF(A2:A7,">0")
>> >
>> >, possibly.
>> >
>> >HTH,
>> >Andy
>> >
>>
>>

[Andy Brown]

"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> Huh? Do you mean that all of the cells in A2:A7 aren't filled

That's the inference of the original question, AFAI can tell. A2:A7 *can*
contain a group of non-contiguous cells, eg: A3, A5, A7.

[Myrna Larson]

PS: If you need this formula just once on the worksheet, you could assign a
name to cells C26, G26, and K26. But if you have to do that for every row,
that may not be feasible.

On Thu, 03 Mar 2005 18:17:15 -0600, Myrna Larson
<[email protected]> wrote:

>MOD would work in the case of A1, A3, and A5, where you want every-other
>column. The array formula would be
>
> =AVERAGE(IF(MOD(ROW(A1:A5),2)=1,IF(A1:A5<>0,A1:A5)))
>
>But that will not work for the example/solution you posted most recently,
>which refers to cells C26, G26, and K26. The column numbers are 3, 7, and 11,
>so there's no numeric relationship between them.
>
>OTOH, if you wanted C26, G26, and *J26*, you could use
>
> MOD(COLUMN(C26:J26),4)=3
>
>because that's every 4th column.
>
>
>On Thu, 3 Mar 2005 15:25:06 -0800, "Mike" <[email protected]>
>wrote:
>
>>Myrna,
>>Thanks, but not sure if I follow your idea. I "simply" want to average only
>>those cells in A1, A3, and A5 that have a value <>0 . Not sure how MOD
would
>>work in this case.
>>
>>"Myrna Larson" wrote:
>>
>>> But A2:A7 is not a group of non-contiguous cells. I think the example he
>gave
>>> was A1, A3, A5
>>>
>>> That can be solved with an array formula that involves MOD and the the row
>>> number, but if he wants something like A2:A7, B9:B13, C1:C3, etc., that
>won't
>>> work, either.
>>>
>>> Another possibility is to select the various cells and assign a name to
>them.
>>> Then a simple =AVERAGE(MyRange) will do.
>>>
>>> On Thu, 3 Mar 2005 23:00:00 -0000, "Andy Brown"
<[email protected]>
>>> wrote:
>>>
>>> >"Mike" <[email protected]> wrote in message
>>> >news:[email protected]...
>>> >> Averaging noncontiguous numbers ignoring zeros?
>>> >
>>> >> =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
>>> >> formula to calculate noncontiguous data. Any ideas?
>>> >
>>> >=SUM(A2:A7)/COUNTIF(A2:A7,">0")
>>> >
>>> >, possibly.
>>> >
>>> >HTH,
>>> >Andy
>>> >
>>>
>>>

[Andy Brown]

"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> MOD would work in the case of A1, A3, and A5, where you want every-other
> column. The array formula would be
>
> =AVERAGE(IF(MOD(ROW(A1:A5),2)=1,IF(A1:A5<>0,A1:A5)))
>
> But that will not work for the example/solution you posted most recently,
> which refers to cells C26, G26, and K26. The column numbers are 3, 7, and
11,
> so there's no numeric relationship between them.

Actually, the relationship is +1 = 4*1, 4*2, 4*3.

Rgds,
Andy

[Harlan Grove]

Mike wrote...
>Unfortunately the data is not in a range so I can't use any A2:A7
functions.

If the data is stored in worksheet cells, then it's stored in ranges,
though likely not single area ranges.

There is a way to do this for a general, multiple area range X. It
requires using a defined name like seq referring to

=ROW(INDIRECT("1:1024"))

and it's a HUGE array formula. Like this,

=SUM(SUMIF(INDIRECT(MID(CELL("Address",(IV65536,X)),
SMALL(IF(MID(","&CELL("Address",(IV65536,X)),seq,1)=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1)))),
SMALL(IF(MID(CELL("Address",(IV65536,X))&",",seq,1)=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1))))
-SMALL(IF(MID(","&CELL("Address",(IV65536,X)),seq,1)=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1)))))),">0"))
/SUM(COUNTIF(INDIRECT(MID(CELL("Address",(IV65536,X)),
SMALL(IF(MID(","&CELL("Address",(IV65536,X)),seq,1)=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1)))),
SMALL(IF(MID(CELL("Address",(IV65536,X))&",",seq,1)=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1))))
-SMALL(IF(MID(","&CELL("Address",(IV65536,X)),seq,1)=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1)))))),">0"))

This assumes cell IV65536 is blank.

[Andy Brown]

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> If the data is stored in worksheet cells, then it's stored in ranges,

Finally!!! ; it takes a genius to understand an idiot -- the latter being
me, BTW, not Mike.

[Myrna Larson]

Good catch, but I think you mean -1, i.e. the column numbers to be added are:
COLUMN() = 4*i-1, where i = an integer. Solving for i, the formula would be

=(COLUMN()+1)/4)=INT(COLUMN()+1/4)


On Fri, 4 Mar 2005 00:26:55 -0000, "Andy Brown" <[email protected]>
wrote:

>"Myrna Larson" <[email protected]> wrote in message
>news:[email protected]...
>> MOD would work in the case of A1, A3, and A5, where you want every-other
>> column. The array formula would be
>>
>> =AVERAGE(IF(MOD(ROW(A1:A5),2)=1,IF(A1:A5<>0,A1:A5)))
>>
>> But that will not work for the example/solution you posted most recently,
>> which refers to cells C26, G26, and K26. The column numbers are 3, 7, and
>11,
>> so there's no numeric relationship between them.
>
>Actually, the relationship is +1 = 4*1, 4*2, 4*3.
>
>Rgds,
>Andy
>

[Myrna Larson]

I counted wrong: column K is 11, J is 10, so

MOD(COLUMN(C26:K26),4)=3

would average C26, G26, and K26 (not J26)


>OTOH, if you wanted C26, G26, and *J26*, you could use
>
> MOD(COLUMN(C26:J26),4)=3
>
>because that's every 4th column.
>
>
>On Thu, 3 Mar 2005 15:25:06 -0800, "Mike" <[email protected]>
>wrote:
>
>>Myrna,
>>Thanks, but not sure if I follow your idea. I "simply" want to average only
>>those cells in A1, A3, and A5 that have a value <>0 . Not sure how MOD
would
>>work in this case.
>>
>>"Myrna Larson" wrote:
>>
>>> But A2:A7 is not a group of non-contiguous cells. I think the example he
>gave
>>> was A1, A3, A5
>>>
>>> That can be solved with an array formula that involves MOD and the the row
>>> number, but if he wants something like A2:A7, B9:B13, C1:C3, etc., that
>won't
>>> work, either.
>>>
>>> Another possibility is to select the various cells and assign a name to
>them.
>>> Then a simple =AVERAGE(MyRange) will do.
>>>
>>> On Thu, 3 Mar 2005 23:00:00 -0000, "Andy Brown"
<[email protected]>
>>> wrote:
>>>
>>> >"Mike" <[email protected]> wrote in message
>>> >news:[email protected]...
>>> >> Averaging noncontiguous numbers ignoring zeros?
>>> >
>>> >> =AVERAGE(IF(A2:A7<>0, A2:A7,"")) . I've not been able to revise this
>>> >> formula to calculate noncontiguous data. Any ideas?
>>> >
>>> >=SUM(A2:A7)/COUNTIF(A2:A7,">0")
>>> >
>>> >, possibly.
>>> >
>>> >HTH,
>>> >Andy
>>> >
>>>
>>>