Hi

I'm creating a workbook of twelve Excel sheets for recording and calculating
UK HMRC Approved Business Mileage claims on a monthly basis.

The current HMRC rates are:  40p/mile for the first 10,000 miles, followed
by 25p per mile thereafter.

I need an Excel formula that will calculate the mileage claim on a monthly
basis, taking account of when the 10,000 mile threshold is passed.

I can write the routine in Autolisp, but I haven't got my head round Excel's
syntax yet.

Assume:
Cell A1 contains the current mileage rate in pence (40) for the first 10,000
miles.
Cell B1 contains the current mileage rate in pence (25) for any mileage over
10,000 miles.
Cell C1 contains the mileage (in miles) for the current month being
calculated.
Cell D1 contains the accumulative mileage (in miles) for the year to date,
including the current month being calculated.

Formula (in Autolisp) :

(if (< D1 10,000)
 (/ (* C1 A1) 100)
 (if (>= (- D1 C1) 10000)
   (/ (* C1 B1) 100)
   (/ (+ (* (- 10000 (- D1 C1)) A1)
     (* (- D1 10000) B1)) 100)
 )
)

Formula explained (hopefully!) :

(if (< D1 10,000)
If D1 is less than 10,000...

 (/ (* C1 A1) 100) = Mileage claim in £s
  ...multiply C1 by A1 (£0.40) to establish the claim for the month.
  Otherwise (as D1 must therefore be more than 10,000)...

 (if (>= (- D1 C1) 10000)
  If D1 - C1 is greater than or equal to 10,000 (i.e. the 10,000 mile
threshold had been passed in an earlier month)...

   (/ (* C1 B1) 100) = Mileage claim in £s
    ...multiply C1 by  B1 (£0.25) to establish the claim for the month,
    Otherwise (as the 10,000 mile threshold must therefore have been
reached or passed in the current month)...

   (/ (+ (* (- 10000 (- D1 C1)) A1) (* (- D1 10000) B1)) 100))) = Mileage
claim in £s
    Subtract current month's mileage (C1) from accumulative mileage for
year (D1) to establish last month's accumulative mileage, then subtract that
figure from 10,000 to establish how many miles remain chargeable at the A1
(40p) rate. Multiply the result by A1 (£0.40).
    Then...
    Subtract 10,000 from the accumulative mileage for year (D1) to
establish how many miles are chargeable at the lower B1 (25p) rate.
Multiply the result by B1 (£0.25).  Add the two results together to
establish the claim for the month.  /100 converts the mileage rates from
pence (40p) to £s (£0.40).

Can anyone spare the time to re-write (and maybe simplify) the formula for
Excel?

TIA