Prev: Leading Zero as value not appearance
Next: how do i switch off template help in microsoft excel?
From: Going Crazy with excel on 1 Jun 2010 05:24
Guess I have a duel question.
First, I have numerous columns of data, all numerical. New entries are
entered at the end of the column. Is there a formula to average just the
last 25 entries in the column? (Bottom of column up)
Second question pertains to the ability to "auto populate". I have a column
of names. (three dozen). I would then like to auto populate the single
column of names, matching the names with the average data obtained from the
above. (average of last 25, 50, 100,etc entries) Data changes daily?
Any help would be greatly appreciated.
From: Mike H on 1 Jun 2010 05:54
Hi,

Average last 25 entries

=AVERAGE(OFFSET(A1,COUNTA(A:A)-25,):OFFSET(A1,COUNTA(A:A),))

I don't understand the second question
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Going Crazy with excel" wrote:

> Guess I have a duel question.
> First, I have numerous columns of data, all numerical. New entries are
> entered at the end of the column. Is there a formula to average just the
> last 25 entries in the column? (Bottom of column up)
> Second question pertains to the ability to "auto populate". I have a column
> of names. (three dozen). I would then like to auto populate the single
> column of names, matching the names with the average data obtained from the
> above. (average of last 25, 50, 100,etc entries) Data changes daily?
> Any help would be greatly appreciated.
From: Going Crazy with excel on 1 Jun 2010 06:33
Sorry Mike, I know what I want, just don't know how to say it.
Maybe an example will help.

Data Sheet one Data Sheet two
Column 1 Column 1 Column 2 Column 3 Column 4
Ralph Sam 199 496 408
Sam Ralph 210 333
535
John Mike 75 322
647
Mike Tim 498 354
657
Tim John 637 577
353

Looking to take the information from Data Sheet two, and auto populate that
data into the matching name on data sheet one. I know I can drag the
information from one to the other, but there are numerous entries for each.
Better?


"Mike H" wrote:

> Hi,
>
> Average last 25 entries
>
> =AVERAGE(OFFSET(A1,COUNTA(A:A)-25,):OFFSET(A1,COUNTA(A:A),))
>
> I don't understand the second question
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Going Crazy with excel" wrote:
>
> > Guess I have a duel question.
> > First, I have numerous columns of data, all numerical. New entries are
> > entered at the end of the column. Is there a formula to average just the
> > last 25 entries in the column? (Bottom of column up)
> > Second question pertains to the ability to "auto populate". I have a column
> > of names. (three dozen). I would then like to auto populate the single
> > column of names, matching the names with the average data obtained from the
> > above. (average of last 25, 50, 100,etc entries) Data changes daily?
> > Any help would be greatly appreciated.
From: Steve Dunn on 2 Jun 2010 05:46
=AVERAGE(OFFSET($B$1,MAX(COUNT($B:$B)-25,0),,25))

and

=INDEX(Sheet2!$A$1:$E$5,MATCH($A1,Sheet2!$A$1:$A$5,0),COLUMN())

HTH
Steve D.


"Going Crazy with excel" <GoingCrazywithexcel(a)discussions.microsoft.com>
wrote in message news:DE3895FD-628E-4FA0-AD24-30A4EE502135(a)microsoft.com...
> Sorry Mike, I know what I want, just don't know how to say it.
> Maybe an example will help.
>
> Data Sheet one Data Sheet two
> Column 1 Column 1 Column 2 Column 3 Column 4
> Ralph Sam 199 496
> 408
> Sam Ralph 210 333
> 535
> John Mike 75 322
> 647
> Mike Tim 498 354
> 657
> Tim John 637 577
> 353
>
> Looking to take the information from Data Sheet two, and auto populate
> that
> data into the matching name on data sheet one. I know I can drag the
> information from one to the other, but there are numerous entries for
> each.
> Better?
>
>
> "Mike H" wrote:
>
>> Hi,
>>
>> Average last 25 entries
>>
>> =AVERAGE(OFFSET(A1,COUNTA(A:A)-25,):OFFSET(A1,COUNTA(A:A),))
>>
>> I don't understand the second question
>> --
>> Mike
>>
>> When competing hypotheses are otherwise equal, adopt the hypothesis that
>> introduces the fewest assumptions while still sufficiently answering the
>> question.
>>
>>
>> "Going Crazy with excel" wrote:
>>
>> > Guess I have a duel question.
>> > First, I have numerous columns of data, all numerical. New entries are
>> > entered at the end of the column. Is there a formula to average just
>> > the
>> > last 25 entries in the column? (Bottom of column up)
>> > Second question pertains to the ability to "auto populate". I have a
>> > column
>> > of names. (three dozen). I would then like to auto populate the
>> > single
>> > column of names, matching the names with the average data obtained from
>> > the
>> > above. (average of last 25, 50, 100,etc entries) Data changes daily?
>> > Any help would be greatly appreciated.

From: Steve Dunn on 2 Jun 2010 05:55
1st formula should perhaps have been better shown as:

=AVERAGE(OFFSET(B$1,MAX(COUNT(B:B)-25,0),,25))

So it can be copied along to other columns.


"Steve Dunn" <stunn(a)sky.com> wrote in message
news:7DB69655-A45E-4A50-ADCF-4A15409B465B(a)microsoft.com...
> =AVERAGE(OFFSET($B$1,MAX(COUNT($B:$B)-25,0),,25))
>
> and
>
> =INDEX(Sheet2!$A$1:$E$5,MATCH($A1,Sheet2!$A$1:$A$5,0),COLUMN())
>
> HTH
> Steve D.
>
>
> "Going Crazy with excel" <GoingCrazywithexcel(a)discussions.microsoft.com>
> wrote in message
> news:DE3895FD-628E-4FA0-AD24-30A4EE502135(a)microsoft.com...
>> Sorry Mike, I know what I want, just don't know how to say it.
>> Maybe an example will help.
>>
>> Data Sheet one Data Sheet two
>> Column 1 Column 1 Column 2 Column 3 Column 4
>> Ralph Sam 199 496 408
>> Sam Ralph 210 333
>> 535
>> John Mike 75 322
>> 647
>> Mike Tim 498 354
>> 657
>> Tim John 637 577
>> 353
>>
>> Looking to take the information from Data Sheet two, and auto populate
>> that
>> data into the matching name on data sheet one. I know I can drag the
>> information from one to the other, but there are numerous entries for
>> each.
>> Better?
>>
>>
>> "Mike H" wrote:
>>
>>> Hi,
>>>
>>> Average last 25 entries
>>>
>>> =AVERAGE(OFFSET(A1,COUNTA(A:A)-25,):OFFSET(A1,COUNTA(A:A),))
>>>
>>> I don't understand the second question
>>> --
>>> Mike
>>>
>>> When competing hypotheses are otherwise equal, adopt the hypothesis that
>>> introduces the fewest assumptions while still sufficiently answering the
>>> question.
>>>
>>>
>>> "Going Crazy with excel" wrote:
>>>
>>> > Guess I have a duel question.
>>> > First, I have numerous columns of data, all numerical. New entries
>>> > are
>>> > entered at the end of the column. Is there a formula to average just
>>> > the
>>> > last 25 entries in the column? (Bottom of column up)
>>> > Second question pertains to the ability to "auto populate". I have a
>>> > column
>>> > of names. (three dozen). I would then like to auto populate the
>>> > single
>>> > column of names, matching the names with the average data obtained
>>> > from the
>>> > above. (average of last 25, 50, 100,etc entries) Data changes daily?
>>> > Any help would be greatly appreciated.
>

 | 
Pages: 1
Prev: Leading Zero as value not appearance
Next: how do i switch off template help in microsoft excel?