2 mileage rates

[desert_dweller5]

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]

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
	A	B	C
1		Weekly Miles Driven	Remburstment
2	Week 1	224	$120.96
3	Week 2	225	$121.50
4	Week 3	226	$121.73
5	Week 4	227	$121.96
6	Week 5	231	$122.88
Short Mileage

Cell Formulas
Range	Formula
C2:C6	C2	=IF(B2<=225,B2*0.54,((B2-225)*0.23)+121.5)

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
	A	B	C	D	E
1	Day of the week	Daily	Weekly	54 c/mi	23 c/mile
2	Sunday	1	1	0.54
3	Monday	3	4	1.62
4	Tuesday	5	9	2.7
5	Wednesday	222	231	116.64	1.38
6	Thursday		231		0
7	Friday		231		0
8	Saturday		231		0
9	Current total			121.5	1.38
10	Grand total	Miles this month:			122.88
Long Mileage

Cell Formulas
Range	Formula
C2	C2	=B2
C3:C8	C3	=C2+B3
D9	D9	=IF(D2<>"",SUM(D2:D8),"")
E9	E9	=IF(E8<>"",SUM(E2:E8),"")
E10	E10	=IF(E8<>"",D9+E9,"")

 

[desert_dweller5]

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:

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]

I modified the Macro:

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]

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]

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