Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to average last 25 entries in a column?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to average last 25 entries in a column?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to average last 25 entries in a column?
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to average last 25 entries in a column?
=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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to average last 25 entries in a column?
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" wrote in message ... =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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make a cell show average of the last 5 entries in a column? | Excel Worksheet Functions | |||
Use formula to remove blank entries from a column | Excel Worksheet Functions | |||
Need a formula to average every other value in a column. | Excel Discussion (Misc queries) | |||
Formula to add top 4 entries in a column and total them at the bot | Excel Worksheet Functions | |||
Average of column entries | Excel Discussion (Misc queries) |