# Formula for phased costs

#### sblondon

##### New member
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

#### Attachments

• Service cost.xlsx
32.5 KB · Views: 9

#### p45cal

##### Super Moderator
Staff member
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.

#### Attachments

• ExelGuru11389Service cost.xlsx
68.3 KB · Views: 8

#### sblondon

##### New member
deleted by sblondon

Last edited:

#### sblondon

##### New member
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

Last edited:

#### sblondon

##### New member
Sorry forgot to ask if possible to have a cumulative cost of service months below grand total?

#### p45cal

##### Super Moderator
Staff member
See attached.
Running total on Sheet3

#### Attachments

• ExelGuru11389Service cost_v02.xlsx
69.3 KB · Views: 6

#### p45cal

##### Super Moderator
Staff member
I've made a mistake, month 5 with 0 cars is showing cost… looking at it.

#### p45cal

##### Super Moderator
Staff member
Correction in attached.

#### Attachments

• ExelGuru11389Service cost_v03.xlsx
70.9 KB · Views: 6