complex excel formula

[menajaro]

Hello everyone
I have complex excel formula that calculates based on the Column A and this is the formula

Code: Select all

=IFERROR(CEILING(ROUND(IF($A2<=666.67;"";IF($A2<=2500;($A2-666.67)*10%;IF($A2<=3750;($A2-2500)*15%+183.33;IF($A2<=16666.67;($A2-3750)*20%+370.83;IF($A2>16666.67;($A2-16666.67)*22.5%+2954.17;0)))))*(1-(LOOKUP($A2;{1;667.67;2501;3751;16667.67};{0;0.85;0.45;0.075;0})));2);0.05);"")

how do I convert it to UDF ....Please see the attachment file
Thanks advanced for help

[HansV]

In the attached version, I propose a simpler formula using a lookup range, and I also created a UDF. The result is the same.

taxes.xlsm

[menajaro]

Thanks a lot Mr Hans for great and creative solutions

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
    Dim discount As Variant
    tiers = Array(0, 666.67, 2500, 3750, 16666.67)
    rates = Array(0, 0.1, 0.05, 0.05, 0.025)
    discount = Array(0, 0.85, 0.45, 0.075, 0)
    For i = LBound(tiers) To UBound(tiers)
        If Amount >= tiers(i) Then
            s = s + (Amount - tiers(i)) * rates(i)
        End If
    Next i
    s = s * (1 - Application.Lookup(Amount - 0.01, tiers, discount))
    Tax = Application.Ceiling(Round(s, 2), 0.05)
End Function

Just one last point...How can return the values to a empty cells Instead of Zero In case if the value of the column cells A Less than or equal to 666.67
Again, thanks so much

[HansV]

Wouldn't it be much more logical to return 0?

You can format the cells with the formula to hide zero values: set the number format to the custom format 0.00;;

[menajaro]

You're right, sir .... Thank you so much, it's perfect!
Once again, thank you for you help.
Best Regards from the deep of my heart