Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculate duty payable
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate duty payable
On Sat, 5 May 2012 08:19:16 +0000, Bealey wrote:
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) -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
#3
|
|||
|
|||
Thank you so much for your formula. It worked perfectly.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
any good accounts payable templates? | Excel Discussion (Misc queries) | |||
How do I calculate duty times | New Users to Excel | |||
accounts payable aging report | Excel Worksheet Functions | |||
due date for payable invoice | Excel Worksheet Functions | |||
Using Excel for Accounts Payable - Comprehensive | Excel Worksheet Functions |