Excel Formula Challenge...

[mlv]

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

 

[Ron Coderre]

With
A1: 40p (Rate for values up through 10,000)
B1: 25p (Rate for values over 10,000)
C1: (Mileage)

Effectively, all values are charged at 25p, but amounts up to 10,000 are
charged an additional 15p.

Try something like this:
D1: =MIN(10000,C1)*(A1-B1)+C1*B1


Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Bernie Deitrick]

Mike,

=IF(D1<10000,A1*C1,IF(D1-C1<10000,(10000-(D1-C1))*A1+(D1-10000)*B1,C1*B1))

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Mike,

Ooops. Either enter .4 and .25 into cells A1 and B1, or use

=IF(D1<10000,A1*C1,IF(D1-C1<10000,(10000-(D1-C1))*A1+(D1-10000)*B1,C1*B1))/100

Sorry about that...
Bernie
MS Excel MVP

Mike,

=IF(D1<10000,A1*C1,IF(D1-C1<10000,(10000-(D1-C1))*A1+(D1-10000)*B1,C1*B1))

HTH,
Bernie
MS Excel MVP

 

[Ron Coderre]

Well, I realize I missed part of the requirements
So....

With
A1: 40p (Rate for values up through 10,000)
B1: 25p (Rate for values over 10,000)
C1: (Mileage)
D1: (Cumulative mileage)

Incremental Amount
E1: =SUM(((D1<=10000)*(D1-C1*{0,1})*(A1-B1)+(D1-C1*{0,1}*B1))*{1,-1})/100


Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[mlv]

=IF(D1<10000,A1*C1,IF(D1-C1<10000,(10000-(D1-C1))*A1+(D1-10000)*B1,C1*B1))/100

E1: =SUM(((D1<=10000)*(D1-C1*{0,1})*(A1-B1)+(D1-C1*{0,1}*B1))*{1,-1})/100

Thanks very much for help guys.

I'll give both formulas a thorough test run when I get home tonight.

I'll also try to understand the syntax

Regards

Mike

 

[Ron Coderre]

I tried a different approach on this one....

With
A1: 0.40 (Rate for values up through 10,000)
B1: 0.25 (Rate for values over 10,000)
C1: (Mileage)
D1: (Cumulative mileage)

Try this:
=(MAX(MIN(10000-D1+C1,C1),0)*(A1-B1)+C1*B1)/100

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[mlv]

Well, I realize I missed part of the requirements
So....

With
A1: 40p (Rate for values up through 10,000)
B1: 25p (Rate for values over 10,000)
C1: (Mileage)
D1: (Cumulative mileage)

Incremental Amount
E1: =SUM(((D1<=10000)*(D1-C1*{0,1})*(A1-B1)+(D1-C1*{0,1}*B1))*{1,-1})/100


Does that help?

Sorry Ron, this formula seems to fail in the month were the 10,000 mileage
threshold is reached if there are some miles at 40p/mile and some at
25p/mile. It seems to calculate all the miles at 25p/mile.

For example:
A1 = 40p (Rate for mileages up to 10,000)
B1 = 25p (Rate for mileages over 10,000)
C1 = 100 miles (Mileage in month)
D1 = 10050 miles (Cumulative mileage)

The correct value of the mileage claim should be £32.50 (50 miles at
40p/mile plus 50 miles at 25p/mile). Your formula gives £25.00, which is
100 miles at 25p/mile.

The other formula you posted seems fine though.

Thanks

 

[mlv]

=IF(D1<10000,A1*C1,IF(D1-C1<10000,(10000-(D1-C1))*A1+(D1-10000)*B1,C1*B1))/100

Hi Bernie

This formula seems to work just fine. I haven't managed to trip it up yet.

It seems very close to my Autolisp routine too, so I can follow its logic.

Thanks

 

[mlv]

I tried a different approach on this one....

With
A1: 0.40 (Rate for values up through 10,000)
B1: 0.25 (Rate for values over 10,000)
C1: (Mileage)
D1: (Cumulative mileage)

Try this:
=(MAX(MIN(10000-D1+C1,C1),0)*(A1-B1)+C1*B1)/100

Does that help?

Hi Ron

This formula seems to work just fine. I haven't managed to trip it up yet.

I like its simplicity too.

Thanks