Average Last # Entries (2000)

Topic

New Reply

Situation: I have a column, A, consisting of several hundred entries of data in consecutive order from A1 through A:500. What I would like to be able to do is average the last (i. e., bottom-most) 12, 18, 24, etc. entries. For example, if the data is entered in cells A1:A100, I would like a formula in cell A101 that would average the last 12 entries in the column, i. e., cells A89:A100, and likewise for, say the last 18, 24, 36, etc. entries. Any help?
Thanks,
Jeff

Reply | Quote

Replies

How about something like:

=AVERAGE(OFFSET(INDIRECT(ADDRESS(MATCH(9.99999999999999E+307,A1:A100)+ROW(A1:A100)-1,COLUMN(A1:A100))),0,0,-12,1))

Change athe range A1:A100 as desired (3 places) and the -12 (for last 12) to whatever.

Steve

Reply | Quote

Hi,
I was gonna ask…Why do you use a scientific number – 9.99999999999999E+307? Whats its significance?

Reply | Quote

If you look up the specifications for calculations in the online help, you’ll find that that number is the largest that can be entered in a cell. So whatever number you have in a cell, it will always be less than or equal to 9.99999999999999E307.

Reply | Quote

Adding to Hans’s observation, see my contrib in:

http://tinyurl.com/83b2x%5B/url%5D

Reply | Quote

Thanx Aladin and Hans!

Reply | Quote

If the formula needs to be in the cell immediately after the last cell of the range of interest (which is A1:A100 in your example)…

In A101 enter:

=AVERAGE(OFFSET(INDEX(A:A,ROW()),-1,0,-N,1))

where N is a value like 12, meaning “last 12”.

If you’d want to enter the formula in a cell outside column A….

=AVERAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,$A$1:$A$100)-N,0,N))

or with a bit control:

=IF(COUNT($A$1:$A$100)<=N,AVERAGE($A$1:$A$100),AVERAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,$A$1:$A$100)-N,0,N)))

where N is the same as above.

Reply | Quote

Much better than mine…

Steve

Reply | Quote

Some great formulas you guys!

Just something very simple thats worth mentioning to any new excel users who may consider taking advantage of these formula’s. Of stead of using the value inside the formula where Aladin has placed the N, use a empty cell reference in the spreadsheet, say $E$1 (NB to have absolute reference on it!) Then all you need to do is type 10, or 15, or 20 etc… in the cell E1 to average the last 10, or 15 or 20 rows! It makes it nice and dynamic…and you do not need to fuss with the formula if the row numbers to average change!

Just my worth!

Reply | Quote