# Excel Formula Challenge...

*From*: "mlv" <mike.safetycatchvincent@xxxxxxxxx>*Date*: Thu, 21 Jun 2007 12:18:30 +0100

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

--

Mike

-Please remove 'safetycatch' from email address before firing off your

reply-

.

**Follow-Ups**:**RE: Excel Formula Challenge...***From:*Ron Coderre

**RE: Excel Formula Challenge...***From:*Ron Coderre

- Prev by Date:
**Re: Change default search to Value, not formula?** - Next by Date:
**RE: Excel Formula Challenge...** - Previous by thread:
**Opening new instances of Excel** - Next by thread:
**RE: Excel Formula Challenge...** - Index(es):