2 mileage rates

desert_dweller5

New Member
Joined
Apr 8, 2011
Messages
30
I'm trying to figure out how to figure out my mileage expenses automatically. It's kicking my butt.

Rate 10.54
Limit 1225
rate 20.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 weekDailyWeekly54 c/mi23 c/mile
Sunday224224=a2*0.54 (+$120.96)0
Monday2226$0.54$0.23
Tuesday12270$0.23
Wednesday12280$0.23
Thursday12290$0.23
Friday12300
$0.23
Saturday12310$0.23
Current total231121.50$1.15
Grand totalMiles 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
Joined
Feb 10, 2010
Messages
148
Office Version
  1. 2019
Platform
  1. Windows
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,"")
 

Some videos you may like

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
Joined
Apr 8, 2011
Messages
30
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
Joined
Apr 8, 2011
Messages
30
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.


FunctionName
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.
NameValue
mileageLimit225
rate10.54
rate20.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
Joined
Feb 10, 2010
Messages
148
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Apr 8, 2011
Messages
30
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?
TOTLMain
Distance / Time Totals
[Redacted]
1)201213D=120T= 2.0A= 5.0
2)201214D=147T= 4.1A= 3.6
3)201215D=150T= 3.4A= 6.0
4)201216D=108T= 2.3A= 5.3
5)201217D=188T= 3.5A= 7.0
6)201218D=70T= 1.3A= 4.0
7)201219D=T=A=
CURTOTD=783T=16.6A=31.0
PRVTOTD=336T= 9.1A=34.3
 

Watch MrExcel Video

Forum statistics

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

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
Top