# Thread: Formula for phased costs

1. ## Formula for phased costs

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.

• 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

2. 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.

3. deleted by sblondon

4. Originally Posted by sblondon
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.

• 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

5. Sorry forgot to ask if possible to have a cumulative cost of service months below grand total?

6. See attached.
Running total on Sheet3

7. Originally Posted by p45cal
See attached.
Running total on Sheet3

Thank you!!!

8. I've made a mistake, month 5 with 0 cars is showing cost… looking at it.

#### Posting Permissions

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