+ Reply to Thread
Results 1 to 13 of 13

Calculating a Rolling Sum of the Previous 6 Months

  1. #1
    Registered User
    Join Date
    04-15-2013
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2016 Mac
    Posts
    11

    Calculating a Rolling Sum of the Previous 6 Months

    Hi Everybody,

    I has exhausted every Google, forum, and friend search I have so I am coming to the experts. I have attached a small sample of the workbook that I am working to help my description.

    Row 2: Months of the Year
    Rows 6 - 10: The construction cost of several different home types, with costs appreciating monthly)
    Row 13: The number of homes with construction beginning in a given month
    Row 14: The total cost of the houses that are started in a given month
    Rows 15-17: The percentage breakdown of the construction cost if the as if the number in Row 13 was representative of the phase as whole.
    Row 18: The contingency cost of the construction

    The Problem: Essentially, my problem is that a house does not take 1 month to build, it takes 6. So what I want to is have Row 14 sum 1/6 of the total of each of the preceding 6 months to have a 6 month rolling cost of construction in a given month. This is important because I will draw down the loan on the amount in Row 14 on any given month and pay interest on the amount until the house is sold. Typically in the 7 month following construction beginning.

    I have tried SUMIFS, and OFFSETS to no avail. Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Calculating a Rolling Sum of the Previous 6 Months

    Can you show some sample answers please, and explain how you got them?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-15-2013
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2016 Mac
    Posts
    11

    Re: Calculating a Rolling Sum of the Previous 6 Months

    Sorry, I became so frustrated I deleted the formulas giving me #Value or not including the full array. The cell references are matched to my sample sheet, but here is the closest I got:

    =SUMIF($F2:AL2,">="&DATE(YEAR(K2),MONTH(K2)-5,DAY(K2)),$F15:K15) - Would work, but:

    =SUMIF($F2:AL2,">="&DATE(YEAR(K2),MONTH(K2)-5,DAY(K2)),$F15:K17) would not work.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Calculating a Rolling Sum of the Previous 6 Months

    Just fyi, this will give you the same answer as your 1st formula...
    =SUMIF($F2:AL2,">="&DATE(YEAR(K2),MONTH(K2)-5,DAY(K2)),$F15:K15) - Would work, but:
    =SUMIF($F2:AL2,">="&EDATE(K2,-5),$F15:K15)

    OK, you showed me what partially worked and what didnt, but you didnt explain what you are trying to do?
    Can you walk me though how you would do this manually?

  5. #5
    Registered User
    Join Date
    04-15-2013
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2016 Mac
    Posts
    11

    Re: Calculating a Rolling Sum of the Previous 6 Months

    Thanks.

    If I was doing it manually I would go as follows:

    J14=SUM(J15:J18)/6
    K15=SUM(J15:J18)/6+SUM(K15:K18)/6
    L15=SUM(J15:J18)/6+SUM(K15:K18)/6+SUM(L15:L18)/6
    M15=SUM(J15:J18)/6+SUM(K15:K18)/6+SUM(L15:L18)/6+SUM(M15:M18)/6
    N15=SUM(J15:J18)/6+SUM(K15:K18)/6+SUM(L15:L18)/6+SUM(M15:M18)/6+SUM(N15:N18)/6
    O15=SUM(J15:J18)/6+SUM(K15:K18)/6+SUM(L15:L18)/6+SUM(M15:M18)/6+SUM(N15:N18)/6+SUM(O15:O18)/6
    P15=SUM(K15:K18)/6+SUM(L15:L18)/6+SUM(M15:M18)/6+SUM(N15:N18)/6+SUM(O15:O18)/6+SUM(P15:P18)/6
    Q15=SUM(L15:L18)/6+SUM(M15:M18)/6+SUM(N15:N18)/6+SUM(O15:O18)/6+SUM(P15:P18)/6+SUM(Q15:Q18)/6
    R15=SUM(M15:M18)/6+SUM(N15:N18)/6+SUM(O15:O18)/6+SUM(P15:P18)/6+SUM(Q15:Q18)/6+SUM(R15:R18)/6

  6. #6
    Registered User
    Join Date
    04-15-2013
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2016 Mac
    Posts
    11

    Re: Calculating a Rolling Sum of the Previous 6 Months

    Sorry error, here is the corrected version:

    J14=SUM(J15:J18)/6
    K14=SUM(J15:J18)/6+SUM(K15:K18)/6
    L14=SUM(J15:J18)/6+SUM(K15:K18)/6+SUM(L15:L18)/6
    M14=SUM(J15:J18)/6+SUM(K15:K18)/6+SUM(L15:L18)/6+SUM(M15:M18)/6
    N14=SUM(J15:J18)/6+SUM(K15:K18)/6+SUM(L15:L18)/6+SUM(M15:M18)/6+SUM(N15:N18)/6
    O14=SUM(J15:J18)/6+SUM(K15:K18)/6+SUM(L15:L18)/6+SUM(M15:M18)/6+SUM(N15:N18)/6+SUM(O15:O18)/6
    P14=SUM(K15:K18)/6+SUM(L15:L18)/6+SUM(M15:M18)/6+SUM(N15:N18)/6+SUM(O15:O18)/6+SUM(P15:P18)/6
    Q14=SUM(L15:L18)/6+SUM(M15:M18)/6+SUM(N15:N18)/6+SUM(O15:O18)/6+SUM(P15:P18)/6+SUM(Q15:Q18)/6
    R14=SUM(M15:M18)/6+SUM(N15:N18)/6+SUM(O15:O18)/6+SUM(P15:P18)/6+SUM(Q15:Q18)/6+SUM(R15:R18)/6

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculating a Rolling Sum of the Previous 6 Months

    Withdrawn by FR.
    Dave

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Calculating a Rolling Sum of the Previous 6 Months

    I could probably figure a way to make this all the same formula, but to start, put this where you need the answer to be...
    =SUM($J$15:J18)/6
    copy across to Jun 19

    For Aug onward, we can use the same formula
    =SUM($K$15:P18)/6
    copied across as needed

  9. #9
    Registered User
    Join Date
    04-15-2013
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2016 Mac
    Posts
    11
    Thanks, but don’t think that works because every month drops off six months previous. And the full spreadsheet is 120 columns wide, so it is a lot to set up if you have to make any changes. And this set is just one of 11 different phases.

    Quote Originally Posted by FDibbins View Post
    I could probably figure a way to make this all the same formula, but to start, put this where you need the answer to be...
    =SUM($J$15:J18)/6
    copy across to Jun 19

    For Aug onward, we can use the same formula
    =SUM($K$15:P18)/6
    copied across as needed

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculating a Rolling Sum of the Previous 6 Months

    Thanks, but don’t think that works because every month drops off six months previous.
    I take that to mean average the 6 most recent months. Your formulas above suggest that.

    If that's correct try making a slight adjustment to Ford's last formula.

    In P14 change =SUM($K$15:P18)/6
    To =SUM(K15:P18)/6 and fill across. It will always be looking back at the previous 6 months.

    Is that what you mean?
    Last edited by FlameRetired; 06-29-2018 at 12:49 AM.

  11. #11
    Registered User
    Join Date
    04-15-2013
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2016 Mac
    Posts
    11

    Re: Calculating a Rolling Sum of the Previous 6 Months

    Good morning Dave and Ford, clearly I at 11 hours I was staring at this spreadsheet too long yesterday and was looking for a far more complicated than necessary solution. This works perfectly. Thank you for bearing with my stupidity on this one.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculating a Rolling Sum of the Previous 6 Months

    at 11 hours I was staring at this spreadsheet too long yesterday and was looking for a far more complicated than necessary solution.
    LOL Been there, done that.

    You are welcome. Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Calculating a Rolling Sum of the Previous 6 Months

    @ Dave, thanks for the assist, I meant to remove the $ from the 2nd formula

    @ NSA, happy to help and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. DAX formula help!! (Calculating rolling 3 months)
    By tbucki1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2016, 02:25 PM
  2. [SOLVED] Find last previous non blank value and summarise previous 6 months
    By Lady_Shaz in forum Excel General
    Replies: 7
    Last Post: 12-11-2012, 06:20 AM
  3. Replies: 1
    Last Post: 12-01-2010, 03:20 PM
  4. [SOLVED] rolling 12 months
    By Max in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 09:05 PM
  5. rolling 12 months
    By D in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  6. [SOLVED] rolling 12 months
    By D in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  7. [SOLVED] rolling 12 months
    By D in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 AM

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