Mod Formula for Mileage?

[Debbie D.]

A mileage claim form is used. Drivers get a certain rate for mileage driven
up to 1,000 miles, at 0.40 pence per mile and another for over 1,000 at 0.25
pence per mile. I have set everything up to record the accumulative miles
each month, but can’t split the monthly figure into the number up to 1,000,
and the remainder above. For example at a mileage of 2,200 it would work out
a follows:

Total Mileage = 2,200
First 1,000 at 0.40 pence per mile =£400
Over first 1,000 mileage 1,200 at 0.25 pence per mile = £300.00

Many thanks for taking the time to look at my problem. All and any help
appreciated.

Thanks

Deb D (UK)

 

[David Biddulph]

=0.4*(MIN(A1,1000))+0.25*(MAX(0,A1-1000))

 

[bj]

try
=miles *.25+min(miles,1000)*.15

 

[Debbie D.]

Many thanks to the above two posters and thanks to both of them for supplying
with the answers. Superstars both!!

Thanks

Deb D (UK)