Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.