Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    compute average with moving data

    hi,
    I have Office XP and am wanting to have excel compute an average of the last 5 data cells. so if column B contains this data, say B2:B45, another cell, say E1, would compute the average of only the last 5 cells (B40:B45).
    Now each month new data is entered, in this case into B46, then I would like E1 to return the average of B41:B46.

    Thanks,
    Rick

  2. #2
    New Lounger
    Join Date
    Apr 2010
    Location
    Jupiter, Florida, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple way to compute average with moving data

    A simple solution is to set up formula for month 0 in E1; when an additional month is added, copy E1 to E2 for updated average.

  3. #3
    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
    If there are no blanks from B2 until the end you can use a formula like:

    =average(OFFSET(B2,COUNT(B:B)-5,0,5,1))

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks Steve,

    That did it (sorry for the late response)

    Rick

Posting Permissions

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