# 2 mileage rates

#### desert_dweller5

##### New Member
I'm trying to figure out how to figure out my mileage expenses automatically. It's kicking my butt.

 Rate 1 0.54 Limit 1 225 rate 2 0.23

lets start out simply.

every week I have to calculate my mileage expenses. following these rates and limits.
i get 54 cents per mile up to 225 miles in 1 week. after that I get 23 cents per mile. the rates start over the next week. I go back to 54 c / mile on sunday.
the problem comes in when I get to 226 miles for the week

 Day of the week Daily Weekly 54 c/mi 23 c/mile Sunday 224 224 =a2*0.54 (+\$120.96) 0 Monday 2 226 \$0.54 \$0.23 Tuesday 1 227 0 \$0.23 Wednesday 1 228 0 \$0.23 Thursday 1 229 0 \$0.23 Friday 1 230 0 \$0.23 Saturday 1 231 0 \$0.23 Current total 231 121.50 \$1.15 Grand total Miles this month: 231 \$122.65

this is what the result should look like. values selected for easy math.

also I may have to calculate a limit for 1000 mi / month. if I go over 1000 miles in a month I stop getting paid for that mileage.
I'm not sure where to calculate this on the spread sheet.
I'm not sure what formulas I need to make this happen. I've been at this for weeks and I still cant get it.
thanks for your time.
Desert_dweller5

#### Ezguy4u

##### Board Regular
Desert Dweller, I had to revise my program because I thought about different criteria. I hope I have all the input covered. This short mileage should also work.

Book1
ABC
1Weekly Miles DrivenRemburstment
2Week 1224\$120.96
3Week 2225\$121.50
4Week 3226\$121.73
5Week 4227\$121.96
6Week 5231\$122.88
Short Mileage
Cell Formulas
RangeFormula
C2:C6C2=IF(B2<=225,B2*0.54,((B2-225)*0.23)+121.5)

VBA Code:
``````Sub Mileage3()

For i = 2 To 8

If Cells(2, 2) >= 226 Then
Cells(i, 4) = 121.5
Cells(i, 5) = (Cells(i, 2) - 225) * 0.23
Exit For
End If

If Cells(i, 3) <= 225 And Cells(i, 2) <= 225 Then

Cells(i, 4) = Cells(i, 2) * 0.54

ElseIf Cells(i, 3) + Cells(i, 2) >= 226 Then

Cells(i, 4) = (Cells(i, 2) - (Cells(i, 3) - 225)) * 0.54
Cells(i, 5) = (Cells(i, 3) - 225) * 0.23
Exit For

End If

Next i
For i = 2 To 7

If Cells(i, 5) <> "" Then

Cells(i + 1, 5) = Cells(i + 1, 2) * 0.23

End If

Next i
End Sub``````

Book1
ABCDE
1Day of the weekDailyWeekly54 c/mi23 c/mile
2Sunday110.54
3Monday341.62
4Tuesday592.7
5Wednesday222231116.641.38
6Thursday2310
7Friday2310
8Saturday2310
9Current total121.51.38
10Grand totalMiles this month:122.88
Long Mileage
Cell Formulas
RangeFormula
C2C2=B2
C3:C8C3=C2+B3
D9D9=IF(D2<>"",SUM(D2:D8),"")
E9E9=IF(E8<>"",SUM(E2:E8),"")
E10E10=IF(E8<>"",D9+E9,"")

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### desert_dweller5

##### New Member
Sub Mileage1() For i = 2 To 8 If Cells(i, 3) <= 225 Then Cells(i, 4) = Cells(i, 2) * 0.54 ElseIf Cells(i, 3) >= 226 Then Cells(i, 4) = (Cells(i, 3) - 225) * 0.54 Cells(i, 5) = (Cells(i, 2) - (Cells(i, 3) - 225)) * 0.23 Exit For End If Next i For i = 2 To 7 If Cells(i, 5) <> "" Then Cells(i + 1, 5) = Cells(i + 1, 2) * 0.23 End If Next i End Sub
This almost works. I came up with this:
VBA Code:
``````Sub Mileage1()

For i = 2 To 8

If Cells(i, 3) <= 225 Then

Cells(i, 4) = Cells(i, 2) * 0.54

ElseIf Cells(i, 3) >= 226 Then

Cells(i, 4) = (225 - Cells(i - 1, 3)) * 0.54
Cells(i, 5) = (Cells(i, 2) - (225 - Cells(i - 1, 3))) * 0.23
Exit For

End If

Next i
For i = 2 To 7

If Cells(i, 5) <> "" Then

Cells(i + 1, 5) = Cells(i + 1, 2) * 0.23

End If

Next i
End Sub``````

what I'd like to do now is come up with variables that stand in for the cells(i,2), cells(i,3), cells(i-1,3), cells(i,4), and cells(i,5) functions

I have not yet figured out how this works.

Pick up from the excel sheet store in a variable then out put back to the sheet?
Cells(i,2) = daily
cells(i,3) = weekly
cells(i-1,3) = yesterdaysWeekly
cells(i,4) = rate1Total
cells(i,5) = rate2Total
limit1 = 225
rate1 = 0.54
rate2 = 0.23

So wherever it says Cells(i,2) you would substitute daily to make it more human readable. To abstract the code a bit more.

if you can help with that I'd be very grateful.
Thanks

#### desert_dweller5

##### New Member
I modified the Macro:
VBA Code:
``````Sub Mileage1()

For i = 2 To 8

If Cells(i, 3) <= 225 Then

Cells(i, 4) = Cells(i, 2) * 0.54

ElseIf Cells(i, 3) >= 226 Then

Cells(i, 4) = (225-Cells(i-1, 3)) * 0.54
Cells(i, 5) = (cells(i,2)-(225-Cells(i-1, 3))) * 0.23
Exit For

End If

Next i
For i = 2 To 7

If Cells(i, 5) <> "" Then

Cells(i + 1, 5) = Cells(i + 1, 2) * 0.23

End If

Next i
End Sub``````
This seems to work.
the first 225 miles are the ones that get 54 cents per mile. the previous script was not calculating the split value correctly. when the miles runs over 225 and it's not equal to 225.
I would also like to use variables to name these Cells(i,x) functions.
Not sure how to do that.

 Function Name Cells(i, 2) Daily Cells(i, 3) weekly Cells(i-1, 3) yesterdayweekly Cells(i, 4) totalForRate1 Cells(i, 5) totalForRate2

also there are other variables that i want to extract and not have them hardcoded.
 Name Value mileageLimit 225 rate1 0.54 rate2 0.23

it also might be faster to take in the values as an array and loop through the array rather than picking each cell value individually? granted there are only 21 values in total, but maybe this could be more scalable for multiple weeks?

the code works as written. I just want to tweak it so it's that much better.
thanks.
Ian

#### Ezguy4u

##### Board Regular
desert dweller or Ian If you are going to use my solution it is better if you use post #11 posted on Nov. 17. Let's start with that solution. Then if you have any comments, suggestion or improvements, we can go from there.

#### desert_dweller5

##### New Member
This is the raw table of what I start with. How do I get this to be what want in 1 shortcut keystroke or 1 mouse click?
 TOTL Main Distance / Time Totals [Redacted] 1) 201213 D=120 T= 2.0 A= 5.0 2) 201214 D=147 T= 4.1 A= 3.6 3) 201215 D=150 T= 3.4 A= 6.0 4) 201216 D=108 T= 2.3 A= 5.3 5) 201217 D=188 T= 3.5 A= 7.0 6) 201218 D=70 T= 1.3 A= 4.0 7) 201219 D= T= A= CURTOT D=783 T=16.6 A=31.0 PRVTOT D=336 T= 9.1 A=34.3

Replies
2
Views
336
Replies
12
Views
850
Replies
1
Views
238
Replies
10
Views
586
Replies
6
Views
525

Threads
1,127,973
Messages
5,627,942
Members
416,282
Latest member
fchagas97

### Share this page ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back