stamp duty tax

[joky]

Hello,
I want to be able to calculate the Stamp Duty tax In an easy way
Shown below is the current rates Using this formula

=IF(SUM(A2:D2)<=3000;"";IF(AND(SUM(A2:D2)>3001;SUM(A2:D2)<=3500);SUM(A2:D2)*1%;IF(AND(SUM(A2:D2)>3501;SUM(A2:D2)<=4000);SUM(A2:D2)*1.5%;IF(AND(SUM(A2:D2)>4001;SUM(A2:D2)<=4500);SUM(A2:D2)*2%;IF(AND(SUM(A2:D2)>4501;SUM(A2:D2)<=5000);SUM(A2:D2)*2.5%;IF(AND(SUM(A2:D2)>5001;SUM(A2:D2)<=5500);SUM(A2:D2)*3%;IF(AND(SUM(A2:D2)>5501;SUM(A2:D2)<=6000);SUM(A2:D2)*3.5%;IF(AND(SUM(A2:D2)>6001;SUM(A2:D2)<=6500);SUM(A2:D2)*4%;IF(AND(SUM(A2:D2)>6501;SUM(A2:D2)<=7000);SUM(A2:D2)*4.5%;IF(SUM(A2:D2)>7000;SUM(A2:D2)*5%))))))))))
How can I change the formula to UDF so as to easily use it.?
I have attached the expected results in this example
Thanks advanced for help

[Paul_Hossler]

Option Explicit


Function StampDutyTax(r As Range) As Double
Dim Amt As Double

Amt = Application.WorksheetFunction.Sum(r)

Select Case Amt
Case Is <= 3000
StampDutyTax = 0#
Case Is <= 3500
StampDutyTax = 0.01 * Amt
Case Is <= 4000
StampDutyTax = 0.015 * Amt
Case Is <= 4500
StampDutyTax = 0.02 * Amt
Case Is <= 5000
StampDutyTax = 0.025 * Amt
Case Is <= 5500
StampDutyTax = 0.03 * Amt
Case Is <= 6000
StampDutyTax = 0.035 * Amt
Case Is <= 6500
StampDutyTax = 0.04 * Amt
Case Is <= 7000
StampDutyTax = 0.045 * Amt
Case Else
StampDutyTax = 0.05 * Amt
End Select

End Function

[p45cal]

Version of Excel?
Try formula:
=XLOOKUP(SUM(A2:D2),{3000;3500;4000;4500;5000;5500;6000;6500;7000;1E+99},{0 ;0.01;0.015;0.02;0.025;0.03;0.035;0.04;0.045;0.05},"z",1)*SUM(A2:D2) which caters for values such as 3001,3501 etc.
The formula could be shortened more, but exactly how depends on your version of Excel.

[joky]

Thank you very much for all of you
How Can The formula could be shortened more? I'm using excel 2010

[Aussiebear]

Joky, you initially asked for a UDF which Paul kindly provided you with. Now you are asking for a shortened formula. Which is it?

[p45cal]

How Can The formula could be shortened more using Excel 2010?
Could you show me the actual rules for stamp duty rates, rather than a formula which has interpreted them (I feel the interpretation isn't quite right)?

[joky]

The formula could be shortened more, but exactly how depends on your version of Excel.
I like to have multiple solutions for any problem Anyway Thanks for both of these solutions, both of which work wonderfully.