Results 1 to 9 of 9
Thread: Average Last # Entries (2000)

20050527, 08:54 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Average Last # Entries (2000)
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., bottommost) 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

20050527, 10:22 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Average Last # Entries (2000)
How about something like:
=AVERAGE(OFFSET(INDIRECT(ADDRESS(MATCH(9.999999999 99999E+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

20050527, 18:46 #3
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Average Last # Entries (2000)
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),AVE RAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,$A$1: $A$100)N,0,N)))
where N is the same as above.Microsoft MVP  Excel

20050527, 21:54 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Average Last # Entries (2000)
<img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> Much better than mine...
Steve

20050530, 11:02 #5
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Average Last # Entries (2000)
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 <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth!Regards,
Rudi

20050530, 13:35 #6
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Average Last # Entries (2000)
Hi,
I was gonna ask...Why do you use a scientific number  9.99999999999999E+307? Whats its significance?Regards,
Rudi

20050530, 13:43 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Average Last # Entries (2000)
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.

20050531, 02:55 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Average Last # Entries (2000)
Adding to Hans's observation, see my contrib in:
http://tinyurl.com/83b2xMicrosoft MVP  Excel

20050531, 05:27 #9
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Average Last # Entries (2000)
Thanx Aladin and Hans!
Regards,
Rudi