Convert a formula to UDF

[menajaro]

Hello everyone
Column “B” contains this formula ... how do I convert it to UDF

Code: Select all

=IF(A1<=15000;"";CEILING(ROUND(IF(A1<=30000;(A1-15000)*2.5%;IF(A1<=45000;(A1-30000)*10%+375;IF(A1<=60000;(A1-45000)*15%+1875;IF(A1<=400000;(A1-60000)*20%+4125;IF(A1>400000;(A1-400000)*25%+72125)))));2)*10/10/12;0.05))

Please see the attachment file , Thank you in advance for looking at this.

[HansV]

This looks like a simplified version of complex excel formula - there is no discount.

[menajaro]

Thanks a lot Mr. Hans for your reply
this a different version of the same thing... But it did not achieve the desired results

Code: Select all

Function Tax(Amount As Double) As Variant
    Dim s As Double
    Dim i As Long
    Dim tiers As Variant
    Dim rates As Variant
    tiers = Array(0, 15000, 30000, 45000, 60000, 400000)
    rates = Array(0, 0.025, 0.01, 0.015, 0.02, 0.025)
    For i = LBound(tiers) To UBound(tiers)
        If Amount >= tiers(i) Then
            s = s + (Amount - tiers(i)) * rates(i)
        End If
    Next i
    Tax = Application.Ceiling(Round(s, 0), 0.05) * 10 / 10 / 12
End Function

it's still a bit blurry to me in this line of code

Code: Select all

    rates = Array(0, 0.025, 0.01, 0.015, 0.02, 0.025)

Or maybe you have another version to achieve the results
Please consider checking out my work. Thanks again for your support, regards.

[HansV]

In your formula, you divide by 12 inside the CEILING function. In the UDF, you did it outside that function, so the results weren't rounded correctly.
And it helps if you use the correct values in the rates array...

Code: Select all

Function Tax(Amount As Double) As Variant
    Dim s As Double
    Dim i As Long
    Dim tiers As Variant
    Dim rates As Variant
    tiers = Array(0, 15000, 30000, 45000, 60000, 400000)
    ' The rates are the increase from the previous tier
    rates = Array(0, 0.025, 0.075, 0.05, 0.05, 0.05)
    For i = LBound(tiers) To UBound(tiers)
        If Amount >= tiers(i) Then
            s = s + (Amount - tiers(i)) * rates(i)
        End If
    Next i
    Tax = Application.Ceiling(Round(s, 2) / 12, 0.05)
End Function

[menajaro]

Now I could get it completely ..thanks a lot Mr. Hans for great help