Excel Formula Challenge...



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-


.



Relevant Pages

  • Sturgis Report - Somewhat long (24 days worth) - Rated R in spots
    ... Note, most mileage are close estimates, not exacts (rounded to nearest 5 miles, total trip was 7497.8 miles). ... Day 2 - It's raining, so on goes the raingear to start the day off, but it quits raining within 30 minutes of being on the road. ...
    (rec.motorcycles)
  • Ride Report - Sturgis 2005 - Sorta long, 24 days worth.
    ... Note, most mileage are close estimates, not exacts (rounded to nearest 5 miles, total trip was 7497.8 miles). ... Day 2 - It's raining, so on goes the raingear to start the day off, but it quits raining within 30 minutes of being on the road. ... I get to Austin and try to find the office building, but Hwy 183 is a nightmare trying to navigate and the bottom falls out on me again, only I have no where to pull off and put on the raingear. ...
    (rec.motorcycles.harley)
  • Re: Trip Report -- Windsor, CT - Dothan, AL & back
    ... > Mileage: 516 miles ... > that I'd prefer to put miles on something other than my gas-guzzling Jeep ... > the hold up -- a police car having stopped a car on the left shoulders. ... > selected because I was traveling with a dog. ...
    (misc.transport.road)
  • in limbo and back
    ... 149 miles but our vehicle’s travel mileage counter recorded only 132 ... after which is the exit to Washington Avenue which we take going home. ...
    (sci.skeptic)
  • RE: Excel Formula Challenge...
    ... UK HMRC Approved Business Mileage claims on a monthly basis. ... 40p/mile for the first 10,000 miles, followed ... Cell D1 contains the accumulative mileage for the year to date, ...
    (microsoft.public.excel)