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

[jaimiep]

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 "D8" plus "F2" to give the total for the year at that date.

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.

Not sure on the different cells and tabs but I think you need something like:

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

As you will always be over 10k for the second part,

[Mattt]

Nested IF would work for that interim situation.

[Rickeh]

Replace the false section of your formula with ((10,000x0.45)+((whatever the running total is, E8?-10000)*0.25))

Sorry not easy to type on phone and the quote hasn't come up so can't see your cell refs. Hopefully I've understood what you're trying to do?

Edited by Rickeh on Monday 18th August 22:34

[Super Slo Mo]

I have a similar spreadsheet that some of our guys who use their own cars have. It does the automatic calculation when the mileage crosses the 10,000 mile threshold.

If you'd like me to send it over to you so you can cross reference the formulas with yours, I'll happily do so. Send me a PM if you'd like it.

[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

[z4RRSchris99]

easy peasy, I'll do it for you tomorrow

[Super Slo Mo]

Spreadsheet template should be on its way.

[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!

[Rickeh]

If(e8<10000, d8*0.45, if(and(e8>10000, f2<10000), (10000-f2)*0.45)+((e8-10000)*0.25), d8*0.25))

That work? Assumed in the previous formula it was just the running total that you needed calculated and not the individual months.

Ah you've found one that works. Is it correct if one month goes from 9000 to 11000 running total? So it should be 450 + 250= 700?

[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

[TrickyDicky101]

You could consider alternatively something along the lines of:

=D8*0.45-MEDIAN(0,D8,E8-10000)*(0.45-0.25)