Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula to calculate mileage reimbursement costs for the following.
Mileage from 0 to 75 = $0.00
Mileage from 76 to 125 pays $1.25 per mile
Mileage form 126 to 9999 pays 1.00 per mile
If I enter total mileage X in a cell I need a total for X in the final cell
Solved by E. J. in 44 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/03/2018 - 12:43
Hello, what can I help you with today?
Excelchat Expert
16/03/2018 - 12:47
I'd like to confirm I understand your question: are you looking for a formula to calculate the reimbursement costs for mileage based on
Excelchat Expert
16/03/2018 - 12:48
$0.00 for the first 75 miles, $1.25 for miles 76 through 125, and $1.00 for miles 126 to 9999?
User
16/03/2018 - 12:48
yes
Excelchat Expert
16/03/2018 - 12:50
Okay, please give me a moment to solve your problem.
Excelchat Expert
16/03/2018 - 12:58
Okay, I have a solution.
Excelchat Expert
16/03/2018 - 12:59
I've broken down the formula into 3 scenarios:
Excelchat Expert
16/03/2018 - 12:59
1. Reimbursement for 75 miles or less
Excelchat Expert
16/03/2018 - 12:59
2. Reimbursement for 76-125 miles
Excelchat Expert
16/03/2018 - 12:59
3. Reimbursement for 126-9999 miles
Excelchat Expert
16/03/2018 - 01:00
I'll enter in an example for each scenario in column A
Excelchat Expert
16/03/2018 - 01:01
The formula is an IF statement with a True or False scenario.
Excelchat Expert
16/03/2018 - 01:03
The logical statement for the first part of the IF statement is if the value in Column A is less than 75.
Excelchat Expert
16/03/2018 - 01:03
If this is the case, the reimbursement cost should be 0.
Excelchat Expert
16/03/2018 - 01:04
The portion that I have highlighted shows what the value will be if our logical statement is true.
Excelchat Expert
16/03/2018 - 01:05
This is the second part of the IF statement.
Excelchat Expert
16/03/2018 - 01:06
In the third part of this IF statement, I have entered another IF statement with another logical statement with a requirement that the value in column A is larger than 75, but less then 125.
Excelchat Expert
16/03/2018 - 01:06
This is what I have highlighted currently.
Excelchat Expert
16/03/2018 - 01:08
In the second IF statement, we still need values for whether the logical statement is true or false.
Excelchat Expert
16/03/2018 - 01:09
The highlighted section shows what the value will be when the logical statement is true (the value is greater than 75, but less than or equal to 125).
Excelchat Expert
16/03/2018 - 01:10
Since the first 75 miles have a reimbursement cost of $0.00, the formula subtracts 75 from the number of miles and multiplies that number by $1.25 to give us the reimbursement cost.
Excelchat Expert
16/03/2018 - 01:11
This last section which is highlighted will return the reimbursement cost when the number of miles is greater than 125.
Excelchat Expert
16/03/2018 - 01:12
In which case, the number of miles from 75 to 125 (which is a difference of 50 miles) have a cost of $1.25 per mile. This is why 50 is being multiplied by $1.25.
User
16/03/2018 - 01:13
Should be 76 to 125
Excelchat Expert
16/03/2018 - 01:13
Sorry, you are correct, 76 to 125.
User
16/03/2018 - 01:13
I don't see the highlighted ares your talking about
Excelchat Expert
16/03/2018 - 01:14
Can you see the formula?
User
16/03/2018 - 01:15
no what cell is it in?
Excelchat Expert
16/03/2018 - 01:15
Cell B2
User
16/03/2018 - 01:17
I see it
Excelchat Expert
16/03/2018 - 01:17
Great.
User
16/03/2018 - 01:18
How much do I owe you
Excelchat Expert
16/03/2018 - 01:18
I'm not quite sure, I believe that is all done via the website.
Excelchat Expert
16/03/2018 - 01:20
I marked the problem as solved. There may be something you need to do on your end.
User
16/03/2018 - 01:21
ok thanks for you help
Excelchat Expert
16/03/2018 - 01:21
You are welcome. Have a great day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
