Hi there,
I want to create a spreadsheet or formula for calculating mileage that has different rates.
Say if I travelled 196 km, I am entitled to 0.78 cents per km for the first 50km, 0.50 cents per km for the next 100 km then over that I get 0.35 cents per km.
Ideally on the spreadsheet I would just enter the total travelled e.g 196 km and the spreadsheet would work it all out for me.
I have no idea how to do this though, hence my asking for help. Or am I expecting too much from Excel?
Cheers
Enter 196 in cell A1
Enter this formula B2
Formula:
Please Login or Register to view this content.
Last edited by mehmetcik; 01-27-2016 at 09:12 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
your wording is slightly ambigious
do you mean
0-50 km= 0.78
51-100km=0.5
100km+=0.35
or
0-50 km
51-151 km
150+km
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks for your reply metmehcik, but this hasn't given me the answer I was looking for.
humdingaling, sorry, I'll try to explain a little better.
Say I have travelled 196 km. The first 50 km I get $0.78 per km, so 0-50 km @ $0.78/km = $39
I still have 146 km left, the next 100 km gets charged at $0.50 per km, so 50-150 km @0.50/km = $50
I have 46 km left, so this gets charged at $0.35 per km 150+ km @ $0.35/km = $16.10
Say I have travelled 196 km. The first 50 km I get $0.78 per km, so 0-50 km @ $0.78/km = $39
Formula:Please Login or Register to view this content.
$39
I still have 146 km left, the next 100 km gets charged at $0.50 per km, so 50-150 km @0.50/km = $50
Formula:Please Login or Register to view this content.
$50
I have 46 km left, so this gets charged at $0.35 per km 150+ km @ $0.35/km = $16.10
Formula:Please Login or Register to view this content.
$16.1
So putting that together
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
that gives $105.1
Awesome, thanks Metmehcik. That works!
since i created it anyways
see attachment of how i did it
slightly different way of doing it to mehmetcik which i included as a checksum
Re: Help with creating excel spreadsheet for calculating mileage with multiple rates
Ahh fantastic, thank you so much! This is exactly what I needed!
Slightly different solution
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks