Excel formula for a mileage claim, help please
Discussion
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
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
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
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
Gassing Station | Finance | Top of Page | What's New | My Stuff