Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Sensitive Average (200)

    I need a formula to calculate a rolling average. I am looking at sales by month and would like to create a formula to calculate the average based on the last 6 complete months. Example:

    It is now May 4th, 2004. The formula would look at (Nov.

  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: Date Sensitive Average (200)

    If we assume you have date values in A1:A500 and the corresponding sales in B1:B500 (change as desired), this ARRAY formula (confirm with ctrl-shift-enter), note it is all one line.

    =AVERAGE(IF(ISNUMBER($B$1:$B$500)*($A$1:$A$500>=DA TE(YEAR(TODAY()),MONTH(TODAY())-6,1))*($A$1:$A$500<=DATE(YEAR(TODAY()),MONTH(TODAY ()),0)),$B$1:$B$500))

    should give you the average you are asking about.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Sensitive Average (200)

    If the dates and sales are in the cells Steve suggested, you could do something like this:

    =AVERAGE(OFFSET(B1:B500,MATCH(MAX(A1:A500),A1:A500 )-1,0,-6,1))

  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: Date Sensitive Average (200)

    My only concern would be if there were already (future) dates filled in A1:A500 that were just waiting for the sales figures. I will frequently fill a series with the values so save time later, so the max might not be the the one with the "current date"

    Offset would also be a good way using match to find today, however. But ti depends on how the spreadsheet is setup. The way I listed is "generic" and the dates do not even have to be sorted to work.

    Steve

Posting Permissions

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