Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    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., 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. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    3 Star Lounger
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Gold Lounger
    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

  6. #6
    Gold Lounger
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  8. #8
    3 Star Lounger
    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/83b2x
    Microsoft MVP - Excel

  9. #9
    Gold Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •