Formula for phased costs

**sblondon** · 2021-12-06, 08:15 PM

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

**p45cal** · 2021-12-07, 05:32 PM

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.

**sblondon** · 2021-12-08, 03:12 PM

deleted by sblondon

**sblondon** · 2021-12-08, 03:14 PM

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.

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