Formula question

[SURGEON1971]

I have created a worksheet to record business mileage. I want to copy this
worksheet into a workbook to record each month mileage in workbook to
facilitate keeping a running total from one month to the next. Within this
w/book I am also calculating mileage @ 40 pence per mile according to
government allowance rate for first 10,000 miles. After that amount it then
goes down to 25p per mile. Is there a formula to change pence per mile amount
once 10,000 mile limit is reached? Or am i just asking to much of excel?

 

[SURGEON1971]

Hi Mike,

Thanks. I tried formula but it didnt work. On the w/sheet i have two colums
for start and finish speedo readings. the difference is then calculated into
total column(thus there are rows for each day travel) In the next column to
'total' is a column where i enter total of any private mileage. then the next
column is column for business mileage which is total minus private. Thus at
the moment at the bottom of column 'business miles' i have formula
calculating total * by 0.4.

This obviously will not calculate correct rate after 10,000 miles. Where
would you suggest i put your formula?

 

[Peter T]

A1: 10000
A2: 0.4
A3: 0.25
C5: last total
B6: this month's mileage
C6: =IF(B6,C5+B6,0) ie the new total
D6: =IF(C6<=$A$1,B6*$A$2,($A$1-C6+B6)*$A$2+(C6-$A$1)*$A$3)


Regards,
Peter T

 

[Mike H]

Hi,

So if I visualise your table correctly ot looks like this
A B C D E
Start End Miles Private M Business M
15000 22586 7586 250 7336
15000 22586 7586 251 7335
15000 22586 7586 252 7334
15000 22586 7586 253 7333
15000 22586 7586 254 7332
15000 22586 7586 255 7331
15000 22586 7586 256 7330
15000 22586 7586 257 7329


To sum column E in a single formula and calculate the payment
=MIN(4000,SUM(E2:E9)*J1)+MAX(0,(SUM(E2:E9)-10000)*J2)


Note I now use J1 and J2 for the 0.4 & 0.25

Mike

 

[SURGEON1971]

Hi Mike,

I am but a simple arborist (professional trained, experienced and qualified
to manage Trees for amenity purposes). Though mathematics/excel is not my
strong point I can see some of the logic in your formula!!! But still I am in
AWE of your ability to create it as you have. IT WORKS!!!! BLOODY
MARVELOUS!!! YOU ARE A GOD!!! THANKS

 

[Mike H]

Glad I could help, now about the oak tree in my garden......