Excel formula for a mileage claim, help please

[scrwright]

This is doing my head in & my formula keep failing at the 10k mile switchover between mileage rates.

I am doing a multi tabbed worksheet 1 month per tab, 1 line per mileage claim.

(Keeping with my cell numbers)
"F2" is the years mileage total at each month end (so first month this starts as zero)

First line entry is line 8 on my worksheet so:

"D8" is the mileage claim, in miles traveled
"E8" is =IF(D8=0,0,D8+F2) to give the total for the year at that date (or for no entry gives zero so I can cascade it down & sum at the bottom).

My calculation in "F8" is:
=IF(E8<10000,D8*0.45,(D8*0.25))

Which works unless the running total in my "E8" cell crosses the 10k mark then is uses the lower rate of 25p not 45p to 10k then 25p past 10k.

Any excel gurus out there who can enlighten me as how to get past the 10k mark?

Ta

Steve

Edited by scrwright on Monday 18th August 22:43

[scrwright]

email sent, thanks.

I have edited my "e8" cell above so if I cascade down a blank sheet doesn't give values & my auto sum at the end works.

Someone suggested:

=IF(E8<10000,D8*0.45,((D8-10000)*0.25)+4500)

But it gives the wrong values over 10k miles

I want the value of each line for the mileage on that line only. So if my D column has a mileage of 10, before 10000 miles its £4.50, after £2.50

Ta

Edited by scrwright on Monday 18th August 22:48

[scrwright]

think I may have it (thanks for the pointers!), multiple IF statements!!

=IF(E8<10000,0.45*D8,IF(E8-D8<10000,(10000-(E8-D8))*0.45+(E8-10000)*0.25,D8*0.25))

Thanks SSM though, will use some of your template, always nice to know how it works though!

[scrwright]

seems to, if I set the month start to 9k miles & add the following lines miles claimed/total/£ claimed:

1 9001 £0.45
1000 10001 £449.80
10 10011 £2.50
1000 11011 £250.00