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

 

[Dave Patton]

T202011a.xlsm
	A	B	C	D	E	F
1	Total limit	1000
2	1- 225	0.54
3	226 -1000	0.23
4
5		Week1	Week2	Week3	Week4	Week5
6	Mileage	300	200	100	700	200
7	Payment	$138.75	$108.00	$54.00	$161.75	$0.00
8
9a

Cell Formulas
Range	Formula
B7:F7	B7	=IF(AND(B6>0,SUM($A$6:A6)<$B$1),MIN(225,B6)*$B$2+(B6>225)*MIN(B6-225,$B$1-SUM($A$6:A6)-225)*$B$3,0)

 

[Ezguy4u]

Because you can never have too many solutions, here is a VBA.

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

Book1
	A	B	C	D	E
1	Day of the week	Daily	Weekly	54 c/mi	23 c/mile
2	Sunday	224	224	$120.96
3	Monday	2	226	$0.54	$0.23
4	Tuesday	1	227		$0.23
5	Wednesday	1	228		$0.23
6	Thursday	1	229		$0.23
7	Friday	1	230		$0.23
8	Saturday	1	231		$0.23
9	Current total			$121.50	$1.38
10	Grand total	Miles this month:			$122.88
Miliage

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,"")

Here is another example.

Book1
	A	B	C	D	E
1	Day of the week	Daily	Weekly	54 c/mi	23 c/mile
2	Sunday	200	200	$108.00
3	Monday	20	220	$10.80
4	Tuesday	3	223	$1.62
5	Wednesday	5	228	$1.62	$0.46
6	Thursday	1	229		$0.23
7	Friday	1	230		$0.23
8	Saturday	1	231		$0.23
9	Current total			$122.04	$1.15
10	Grand total	Miles this month:			$123.19
Miliage

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,"")

 

[Dave Patton]

Ezguy4u​

What is the payment if in week 1 there was 800 miles and in week 2 700 miles?

 

[Dave Patton]

If you are not concerned after the 1000 mile threshold
Consider one of the following.
It is a little more complex to consider the 1000 mile threshold; see next post.

T202011a.xlsm
	A	B	C	D	E	F
5		Week1	Week2	Week3	Week4	Week5
6	Mileage	200	300	400	400	200
12
13		108.00	138.75	161.75	161.75	108.00
14		108.00	138.75	161.75	161.75	108.00
15		108.00	138.75	161.75	161.75	108.00
16		108.00	138.75	161.75	161.75	108.00
9a

Cell Formulas
Range	Formula
B13:F13	B13	=B6*$B$3+MIN($A$2,B6)*($B$2-$B$3)
B14:F14	B14	=MIN(B6,$A$2)*$B$2+(B6>$A$2)*(B6-$A$2)*$B$3
B15:F15	B15	=IF(B6<$A$2,B6*$B$2,(B6-$A$2)*$B$3+$A$2*$B$2)
B16:F16	B16	=B6*0.23+MIN(225,B6)*0.31

 

[Dave Patton]

T202011a.xlsm
	A	B	C	D	E	F
1	Upper Limit
2	225	0.54	0.54
3	1000	0.23	-0.31
4
5		Week1	Week2	Week3	Week4	Week5
6	Mileage	200	300	400	400	200
7	Payment	$108.00	$138.75	$161.75	$54.00	$0.00
8		$108.00	$138.75	$161.75	$54.00	$0.00
9		$108.00	$138.75	$161.75	$54.00	$0.00
10
9a

Cell Formulas
Range	Formula
C2:C3	C2	=B2-B1
B7:F7	B7	=IF(AND(B6>0,SUM($A$6:A6)<$A$3),MIN($A$2,B6,$A$3-SUM($A$6:A6))*$B$2+(B6>225)*(($A$3-SUM($A$6:A6)-225)>0)*(B6-225)*$B$3,0)
B8:F8	B8	=SUMPRODUCT(--(MIN(B6,$A$3-SUM($A$6:A6))>{0;225}),MIN(B6,$A$3-SUM($A$6:A6))-{0;225},{0.54;-0.31})
B9:F9	B9	=SUMPRODUCT(--(MIN(B6,$A$3-V(SUM($A$6:A6)))>{0;225}),MIN(B6,$A$3-V())-{0;225},$C$2:$C$3)

N.B. Range B9:F9 uses a UDF V(), if your Excel has the new function LET use it instead of V(). To use V() you will need to put the UDF in your file.

 

[Dave Patton]

This example may be easier to follow.

T202011a.xlsm
	A	B	C	D	E	F
5		Week1	Week2	Week3	Week4	Week5
6	Mileage	200	300	400	400	200
7	Accum	200	500	900	1300	1500
8	Net Eligible	200	300	400	100	0
9	Payment	108.00	138.75	161.75	54.00	0.00
9aa

Cell Formulas
Range	Formula
B7:F7	B7	=SUM($B$6:B6)
B8:F8	B8	=MAX(0,MIN(B6,1000-N(A7)))
B9:F9	B9	=SUMPRODUCT(--(B8>{0;225;1000}),B8-{0;225;1000},{0.54;-0.31;-0.23})

 

[desert_dweller5]

I actually don’t have a maximum mileage. It’s actually 23 cents a mile for all miles after 225. There’s no monthly maximum

 

[desert_dweller5]

Ezguy4u​

What is the payment if in week 1 there was 800 miles and in week 2 700 miles?

Any miles after 225 is 23 cents per mile. There’s no max.

 

[Ezguy4u]

So desert dweller, if you have any questions about any of these solutions, let us know.