Results 1 to 10 of 10

Thread: Formula for phased costs

  1. #1
    Seeker sblondon's Avatar
    Join Date
    Dec 2021
    Posts
    6
    Articles
    0
    Excel Version
    office 365 v2102

    Unhappy Formula for phased costs



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello

    I need help creating a formula to work out the service cost for number of vehicles added each month, similar spreadsheet for other types of costs e.g MOT, tyres etc requiring different period start and interval. The current spreadsheet requires the formulas to be manually edited if the interval changes.

    The current spreadsheet:

    • Column A = Month (1-60)
    • Column B = Number of cars added in the month
    • Columns C to BJ = Month (1-60)
    • Row 64 = Totals for each month
    • Cell D1 = Average service cost per vehicle
    • Cell D2 = 1st period service is required after purchase of first vehicles
    • Cell D4 = Service interval e.g. every 4 months



    I need help with creating a formula that will automatically spread the cost based on the intervals and number of vehicles purchased for the period.

    Thank you
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,062
    Articles
    0
    Excel Version
    365
    A different approach in the attached.
    Your Servicing sheet untouched except for a disconnection to the source data which I don't have.
    A new sheet (Sheet2) with a pivot table at cell H1, whose data is almost exactly the same as your Servicing sheet data.
    If you alter the data in table at cell A1 and that in the table at cell D1, then go to the pivot and refresh it, it should alter as per your scenario.
    There's anothe pivot table in Sheet3 which is a less cluttered look showing costs in each month they're produced.

    If this is of interest we can refine it.
    Attached Files Attached Files

  3. #3
    Seeker sblondon's Avatar
    Join Date
    Dec 2021
    Posts
    6
    Articles
    0
    Excel Version
    office 365 v2102
    deleted by sblondon
    Last edited by sblondon; 2021-12-08 at 03:17 PM.

  4. #4
    Seeker sblondon's Avatar
    Join Date
    Dec 2021
    Posts
    6
    Articles
    0
    Excel Version
    office 365 v2102
    Quote Originally Posted by sblondon View Post
    Hello

    I need help creating a formula to work out the service cost for number of vehicles added each month, similar spreadsheet for other types of costs e.g MOT, tyres etc requiring different period start and interval. The current spreadsheet requires the formulas to be manually edited if the interval changes.

    The current spreadsheet:

    • Column A = Month (1-60)
    • Column B = Number of cars added in the month
    • Columns C to BJ = Month (1-60)
    • Row 64 = Totals for each month
    • Cell D1 = Average service cost per vehicle
    • Cell D2 = 1st period service is required after purchase of first vehicles
    • Cell D4 = Service interval e.g. every 4 months



    I need help with creating a formula that will automatically spread the cost based on the intervals and number of vehicles purchased for the period.

    Thank you


    Hi p45cal

    Thank you for your solution. I didn't think to use power query, most probably because of my limited experience and use of it. Your solution works well, can i please ask how to change the start date to be the same as the service month e.g if start date = 4 then costs to start from service month 4. Also can i have one pivot showing month, cars added, service month 1-n , similar to my excel table?

    Thank you
    Last edited by sblondon; 2021-12-08 at 03:21 PM.

  5. #5
    Seeker sblondon's Avatar
    Join Date
    Dec 2021
    Posts
    6
    Articles
    0
    Excel Version
    office 365 v2102
    Sorry forgot to ask if possible to have a cumulative cost of service months below grand total?

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,062
    Articles
    0
    Excel Version
    365
    See attached.
    Running total on Sheet3
    Attached Files Attached Files

  7. #7
    Seeker sblondon's Avatar
    Join Date
    Dec 2021
    Posts
    6
    Articles
    0
    Excel Version
    office 365 v2102

    Quote Originally Posted by p45cal View Post
    See attached.
    Running total on Sheet3

    Thank you!!!

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,062
    Articles
    0
    Excel Version
    365
    I've made a mistake, month 5 with 0 cars is showing cost… looking at it.

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,062
    Articles
    0
    Excel Version
    365
    Correction in attached.
    Attached Files Attached Files

  10. #10
    Seeker sblondon's Avatar
    Join Date
    Dec 2021
    Posts
    6
    Articles
    0
    Excel Version
    office 365 v2102
    Quote Originally Posted by p45cal View Post
    Correction in attached.
    Thank you p45cal!

Posting Permissions

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