mileage rates based on yearly total

[jzinko]

I'm a bit of an excel noob and need help with a formula.

I submit mileage expenses monthly. My mileage rate is based on yearly totals: 40 cents/mi for everything up to 5000 miles per year, 20 cents after that.

So I need to input the yearly total mileage from the previous monthly report, then determine the reimbursement rate for the current month only.

eg:
last month I had 4800 miles. This month I had 500 miles. I need to calculate 200 miles at 40 cents, then 300 miles at 20 cents. Then next month, all mileage will be at the 20 cents rate.

Any help is appreciated.

 

[tyija1995]

Hey,

I'd have 3 (maybe 4) columns:
Column M: Miles
Column N: Accumulated Miles
Column O: Pay

In Column M fill in monthly miles (perhaps set Column L to the month)
In Column N (cell N2) accumulate the monthly miles =SUM($M$2:M2) and drag down from N2 to N13
In Column O (cell O2) use this formula: =IF(N2<5000,0.4*M2,IF(N(N1)-5000<0,(5000-N(N1))*0.4+(N2-5000)*0.2,0.2*(N2-N(N1))))

	M	N	O
1	Miles	Acc. Miles	Pay
2	4800	=SUM($M$2:M2)	=IF(N2<5000,0.4*M2,IF(N(N1)-5000<0,(5000-N(N1))*0.4+(N2-5000)*0.2,0.2*(N2-N(N1))))
3	500	=SUM($M$2:M3)	=IF(N3<5000,0.4*M3,IF(N(N2)-5000<0,(5000-N(N2))*0.4+(N3-5000)*0.2,0.2*(N3-N(N2))))
4	400	=SUM($M$2:M4)	=IF(N4<5000,0.4*M4,IF(N(N3)-5000<0,(5000-N(N3))*0.4+(N4-5000)*0.2,0.2*(N4-N(N3))))

<tbody>
</tbody>

Which results in the following:

	M	N	O
1	Miles	Acc. Miles	Pay
2	4800	4800	1920
3	500	5300	140
4	400	5700	80

<tbody>
</tbody>

 

[jzinko]

Worked like a charm. Thank you so much.