Calculating driver pay from the mileage they have travelled

Hi guys,

I am trying to calculate the pay for over 5000 taxi runs that have gone. I have the pay sheet (£s per mile etc) and a list of the jobs with the exact mileage (2.02 etc). What we would normally do is for a route that is 2.02 we would pay them for 3 miles. What is a simple formula that I can put in to calculate this. The tables look like the following;

A B C D E F G H I J
Start Date Call Sign Surname Forename Planned Mileage Pay Mobility Text From Postcode To Postcode

Column E is what need to be filled out from

A B
Miles Pay
1 4.50
2 6.00
3 7.50
4 9.00

So for example if a driver did a job for 3.5 miles they would get £9, if they did a job for 0.3 they would get £4.50 etc

Hope this makes sense

TJ

Thanks Glenn, unfortunately no I simplified it - the actual mileage calculator is as follows;

Mls PPM Total PAY
1 4.5 4.5
2 1.35 5.85
3 1.35 7.2
4 1.35 8.55
5 1.35 9.9
6 1.35 11.25
7 1.35 12.6
8 1.35 13.95
9 1.35 15.3
10 1.35 16.65
11 1.35 18
12 1.35 19.35
13 1.35 20.7
14 1.35 22.05
15 1.35 23.4
16 1.3 24.7
17 1.3 26
18 1.3 27.3
19 1.3 28.6
20 1.3 29.9
21 1.3 31.2
22 1.3 32.5
23 1.3 33.8
24 1.3 35.1
25 1.3 36.4
26 1.13 37.53
27 1.13 38.66
28 1.13 39.79
29 1.13 40.92
30 1.13 42.05

After this its 1.13 a mile no matter how far they travel

Thanks Glenn that is absolutely perfect and worked a treat - you are an excel guru

TJ