Thread: Average Last # Entries (2000)

1. 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., 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

2. Re: Average Last # Entries (2000)

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

Steve

3. 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.

4. Re: Average Last # Entries (2000)

<img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> Much better than mine...

Steve

5. 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!

6. Re: Average Last # Entries (2000)

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

7. 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.

8. Re: Average Last # Entries (2000)

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

http://tinyurl.com/83b2x