stamp duty tax

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

Code:

---

=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

Code:

---

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

---

Version of Excel?
Try formula:

Code:

---

=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.

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

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

Quote:

---

Originally Posted by joky

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)?

Quote:

---

Originally Posted by p45cal

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.