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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Miles[/TD]
[TD]Acc. Miles[/TD]
[TD]Pay[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4800[/TD]
[TD]=SUM($M$2:M2)[/TD]
[TD]=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))))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500[/TD]
[TD]=SUM($M$2:M3)[/TD]
[TD]=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))))[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]400[/TD]
[TD]=SUM($M$2:M4)[/TD]
[TD]=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))))[/TD]
[/TR]
</tbody>[/TABLE]

Which results in the following:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Miles[/TD]
[TD]Acc. Miles[/TD]
[TD]Pay[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4800[/TD]
[TD]4800[/TD]
[TD]1920[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500[/TD]
[TD]5300[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]400[/TD]
[TD]5700[/TD]
[TD]80[/TD]
[/TR]
</tbody>[/TABLE]

 

[jzinko]

Worked like a charm. Thank you so much.