• Average Last # Entries (2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Average Last # Entries (2000)

    Author
    Topic
    WSjlkirk
    AskWoody Lounger
    May 27, 2005 at 8:54 am #420068

    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
    Viewing 1 reply thread
    Author
    Replies
    • WSsdckapr
      AskWoody Lounger
      May 27, 2005 at 10:22 am #950149

      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
      • WSRudi
        AskWoody Lounger
        May 30, 2005 at 1:35 pm #950418

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

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          May 30, 2005 at 1:43 pm #950419

          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
        • WSAladin Akyurek
          AskWoody Lounger
          May 31, 2005 at 2:55 am #950498

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

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

          Reply | Quote
    • WSAladin Akyurek
      AskWoody Lounger
      May 27, 2005 at 6:46 pm #950206

      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
      • WSsdckapr
        AskWoody Lounger
        May 27, 2005 at 9:54 pm #950225

        thumbup Much better than mine…

        Steve

        Reply | Quote
      • WSRudi
        AskWoody Lounger
        May 30, 2005 at 11:02 am #950404

        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 2cents worth!

        Reply | Quote
    Viewing 1 reply thread
    Reply To: Average Last # Entries (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.