Calculate duty payable

[Bealey]

I need to create a formula in excel 2010 which will calculate moto
vehicles duty.
The duty currently payable is $3 for every $100 (or part of $100) of it
value. However the duty on vehicles valued at $45 000 or more (no
including motor cycles, buses for more than 9 people including th
driver, hearses and invalid conveyances) is $1350 plus $5 for every $10
(or part of $100) over $45 000

 

[Stan Brown]

I need to create a formula in excel 2010 which will calculate motor
vehicles duty.
The duty currently payable is $3 for every $100 (or part of $100) of its
value. However the duty on vehicles valued at $45 000 or more (not
including motor cycles, buses for more than 9 people including the
driver, hearses and invalid conveyances) is $1350 plus $5 for every $100
(or part of $100) over $45 000.

Solution 1:

Assuming the value is in A1,

A2 =3*ceiling(A1/100,1)
A3 =1350 + 5*ceiling((A1-45000)/100,1)
A4 =if(A1>=45000,A3,A2)

Solution 2:

Notice that $1350 is $450*3. In effect, then, the duty is $3 for
each $100 or portion, plus an extra $2 ($5-$3) for each $100 or
portion over $45000. Again assuming that the value is in A1:

A4 = 3*ceiling(A1/100,1) + 2*ceiling(max(0,A1-45000)/100,1)