+ Reply to Thread
Results 1 to 5 of 5

Formula help to calculate only the first 90 days

  1. #1
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Formula help to calculate only the first 90 days

    I am using a formula to calculate a rolling 90 days:
    =SUMPRODUCT(--($A$5:$A$9647<=TODAY()),--($A$5:$A$9647>TODAY()-90),$F$5:$F$9647)

    I need something similar but it will only calculate the first 90 days. Basically one cell will have a start date of something like 01/01/2009 and it will use that date as a starting date.

    The thing is the rolling 90 days should not start calculating until after first 90 days is up. The cashier variances for the first 90 days will not be added into the beginning of the rolling 90 days.

    Attached is a excel sheet that I am working with.

    This is a very tough one, I really do appreciate any help.

    Thanks,
    Nick
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    I'm afraid I don't understand the question.

    Can you post up a larger sample and the *expected* result ?

  3. #3
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346
    Hi, sorry I read that over myself and it confused even me.

    I attached an updated excel sheet where I made notes on the sheet.

    Is that easier to do it that way?

    Thanks,
    Nick
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    If I've understood correctly:

    F1: =SUMPRODUCT(--($A$5:$A$22>=$D1),--($A$5:$A$22<$D$1+90),$F$5:$F$22)

    Then

    F2: =SUMPRODUCT(--($A$5:$A$22>=$D1+90),--($A$5:$A$22<$D$1+180),$F$5:$F$22)

  5. #5
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346
    Thanks I will test it out tonight.

+ 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