Formula(s) for choosing & calculating milage rates

[misteronetwo]

Hi There,
I'm trying to create an annual mileage expense document. We have three applicable milage rates depending on the amount of mileage covered over the calendar year. 0-1500 / 1501-5500 / 5501 - 25000
Ideally, the overall mileage figure would accumulate over the monthly sheets and only the rate applicable to the mileage in the month would be used. So after 1500 kms the, every additional km to 5500 would be charged at the second rate.
Potentially two rates might be used in one month as the crossover threshold is met.
So, I'm looking to see if there is a formula or formulae to help achieve this.

Im not sure if this is overly complex or if theres a simpler way of achieving same.
Ive attached the document for reference.
Thanks

Paul

[JohnTopley]

can you please add a few manual results to you file (distance and engine size)

[misteronetwo]

Hi John,
Thanks for the response.
Ive added in a few more distance to the document. The engine size will be the same for all vehicles even though theres three options on the document.
amended file attached.

Paul

[JohnTopley]

TRY

in C46

=MIN(C43,1500)*F52+MAX(0,C43-1500)*F53+MAX(0,C43-5500)*F54

[misteronetwo]

Hi John,
Thanks for that, appreciate your time on this.
Id need the formula to take into account that once a threshold has been met, cumulatively, that rate would no longer be used.
So once a driver has passed the 1500km mark (in a calendar year... not just monthly), every km after that is charged at the second rate and likewise for the 5500 threshold and third rate.
This is why I've added the annual cumulative cell on the sheet, to maybe reference it in some way.
I may not have explained it 100% initially and again, not sure if I'm overly complicating it. We've been changing the rates manually up to now, just trying to streamline it a little.

Let me know what you think or if it's possible.
Thanks

[JohnTopley]

That is what the formula does. Did you actual try it with higher mileages which is why I requested them in a previous post

Or do you mean

CM = Cumulative mileage

=IF (CM <= limit1,rate1,if(cm<=limit2,Rate2,Rate3)

e,g

=if(E43<=1500,F52,if(E43<=5500,F53,F54)

[misteronetwo]

Hi,
Yes have tried it with more mileage cells filled in. The formula works perfectly Month 1.
The problem i'm having is let's say I do exactly 1500kms in January, then every km in February should be charged at the second rate only and not use the first rate as we've already crossed that annual threshold.
If I use the same formula in Feb's sheet then then it starts from 0kms again and charges from the first rate up to 1500.

[JohnTopley]

do you mean

CM = Cumulative mileage

=IF (CM <= limit1,rate1,if(cm<=limit2,Rate2,Rate3)

e,g

=if(E43<=1500,F52,if(E43<=5500,F53,F54)

E43 = ANNUAL CUMULALATIVE

In my view you would be better having an annual summary sheet with the 12 months on this.

Off for the day (night) now!

[JohnTopley]

See attached


Uses same formula to calculate Year-to-Date and Cumulative- to -previous month to determine current month

as per March in your posted file.

[misteronetwo]

Hi John,
That's great, this really helps.
Thanks very much.
Paul