Need HELP figuring formula to calculate mileage

[fdzmah]

Hello, I need help trying to figure out a formula that would save me heaps of time trying to calculate mileage on expense reports each month, and I cannot for the life of me get the formula down. Following are the specifics:

Mileage is reimbursed at a rate of $0.17/mi for the first 198 miles in the week. Any mileage after 198 is calculated at a rate of $0.56/mi. The mileage log I have is a monthly log, and expenses are turned in on a monthly basis.

Is there a way to create a formula that will calculate on a weekly basis, at the correct rate based on the number of miles in that week thus far?

Thanks to anyone taking the time to read/attempting to help out!

 

[bbott]

Assuming your mileage for week 1 is in A2:G2, try this:

=IF(SUM(A2:G2) <= 198,SUM(A2:G2)*0.17,(SUM(A2:G2)-198)*0.56+33.66)

 

[daverm]

Hello, I need help trying to figure out a formula that would save me heaps of time trying to calculate mileage on expense reports each month, and I cannot for the life of me get the formula down. Following are the specifics:

Mileage is reimbursed at a rate of $0.17/mi for the first 198 miles in the week. Any mileage after 198 is calculated at a rate of $0.56/mi. The mileage log I have is a monthly log, and expenses are turned in on a monthly basis.

Is there a way to create a formula that will calculate on a weekly basis, at the correct rate based on the number of miles in that week thus far?

Thanks to anyone taking the time to read/attempting to help out!

Set up your spread sheet as follows:

Cell A1: Mileage Limit, B1: 0, C1: 198

Cell A2: Mileage Rate, B2: 0.17, C2: D2-B2 (formula), D2: 0.56

Cell A4: Week, B4: 1, C4: 2, D4: 3, E4: 4, F4: 5

Cell A5: Mileage, then enter mileage in B5 etc. for the corresponding week

Cell A6: Claim Amount, B6: this formula =SUMPRODUCT((SUM($B5:B5)>$B$1:$C$1)*(SUM($B5:B5)-$B$1:$C$1)*$B$2:$C$2) and rather than pressing enter press Ctrl+Shift+Enter.

You can then copy this across C6:F6.

Note the formula should look like this ={SUMPRODUCT((SUM($B5:B5)>$B$1:$C$1)*(SUM($B5:B5)-$B$1:$C$1)*$B$2:$C$2)} if you enter using Ctrl+Shift+Enter (an array formula).

Note this will give you the running total for your weekly claim amount. i.e. the amount calculated for week 2 is inclusive of the amount claimed for week 1. That is to say the last week (either 4 or 5) is the amount to be claimed for the whole month.