+ Reply to Thread
Results 1 to 3 of 3

I believe it would be called a rolling formula????

  1. #1
    Cat Foster
    Guest

    I believe it would be called a rolling formula????

    I have a complicated problem. I have a spread sheet that calculates employee
    points. The spreadsheet itself contains all the employee points ever
    earned. I need a way to only calculate the last six months. And then every
    time the months moves forward (from May to June) it will automatically
    calculate the last six months including June (ex. June back to January). The
    other points that were incluided from December on the spreadsheet need to
    stay there just not be calculated.

    Is there a way to do this and if there is how.

    Thank you

    --
    Catherine Foster
    MBS-Aumsville
    Toll Free 800-682-1422
    Phone 503-749-4949
    Fax 503-749-4950
    www.mbs-modular.com



  2. #2
    Biff
    Guest

    Re: I believe it would be called a rolling formula????

    Hi!

    Try something like this:

    I assume you want to sum the points (you didn't say). You also didn't say
    what you want if there are not 6 months worth of points. So, I'm assuming a
    lot here!

    The points are in column A starting in cell A1 and there are no empty cells
    within the range.

    =IF(COUNT(A:A)<7,SUM(A:A),SUM(OFFSET(A1,COUNT(A:A)-1,,-6)))

    This will sum all entries until there are at least 7 then it will sum the
    last 6.

    If this isn't what you had in mind post back and be more specific. The more
    specific details you include the better the solution.

    Biff

    "Cat Foster" <[email protected]> wrote in message
    news:[email protected]...
    >I have a complicated problem. I have a spread sheet that calculates
    >employee points. The spreadsheet itself contains all the employee points
    >ever earned. I need a way to only calculate the last six months. And then
    >every time the months moves forward (from May to June) it will
    >automatically calculate the last six months including June (ex. June back
    >to January). The other points that were incluided from December on the
    >spreadsheet need to stay there just not be calculated.
    >
    > Is there a way to do this and if there is how.
    >
    > Thank you
    >
    > --
    > Catherine Foster
    > MBS-Aumsville
    > Toll Free 800-682-1422
    > Phone 503-749-4949
    > Fax 503-749-4950
    > www.mbs-modular.com
    >




  3. #3
    Sandy Mann
    Guest

    Re: I believe it would be called a rolling formula????

    Catherine,

    It's a bit difficult to give a complete answer without knowing how your data
    is laid out but as an illustration, with dates in column A, Pinits in column
    B and the end date of the current month in G2 generated by the formulas:

    =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

    and the starting date for six months ago in G3 generated by the formula:

    =DATE(YEAR(TODAY()),MONTH(TODAY())-5,0)

    the points for the last six months will then be returned by:

    =SUMPRODUCT((A2:A20<G2)*(A2:A20>G3)*(B2:B20))

    --
    HTH

    Sandy
    In Perth, the ancient capital of Scotland

    [email protected]
    [email protected] with @tiscali.co.uk


    "Cat Foster" <[email protected]> wrote in message
    news:[email protected]...
    >I have a complicated problem. I have a spread sheet that calculates
    >employee points. The spreadsheet itself contains all the employee points
    >ever earned. I need a way to only calculate the last six months. And then
    >every time the months moves forward (from May to June) it will
    >automatically calculate the last six months including June (ex. June back
    >to January). The other points that were incluided from December on the
    >spreadsheet need to stay there just not be calculated.
    >
    > Is there a way to do this and if there is how.
    >
    > Thank you
    >
    > --
    > Catherine Foster
    > MBS-Aumsville
    > Toll Free 800-682-1422
    > Phone 503-749-4949
    > Fax 503-749-4950
    > www.mbs-modular.com
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1