Formula for phased costs

sblondon

New member
Joined
Dec 6, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
office 365 v2102
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
 

Attachments

  • Service cost.xlsx
    32.5 KB · Views: 9

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
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.
 

Attachments

  • ExelGuru11389Service cost.xlsx
    68.3 KB · Views: 8

sblondon

New member
Joined
Dec 6, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
office 365 v2102
deleted by sblondon
 
Last edited:

sblondon

New member
Joined
Dec 6, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
office 365 v2102
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:

sblondon

New member
Joined
Dec 6, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
office 365 v2102
Sorry forgot to ask if possible to have a cumulative cost of service months below grand total?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
365
See attached.
Running total on Sheet3
 

Attachments

  • ExelGuru11389Service cost_v02.xlsx
    69.3 KB · Views: 6

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
365
I've made a mistake, month 5 with 0 cars is showing cost… looking at it.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
365
Correction in attached.
 

Attachments

  • ExelGuru11389Service cost_v03.xlsx
    70.9 KB · Views: 6
Top