Convert a formula

[jonsonbero]

Hi All,
I am facing a lot of problems with this formula ...How can I change the formula to UDF?

=IF(SUM(A2:D2)<=3000;"";CEILING(IF(AND(SUM(A2:D2)>3000;SUM(A2:D2)<=4000);SUM(A2:D2)*0.5%;IF(AND(SUM(A2:D2)>4000;SUM(A2:D2)<=5000);SUM(A2:D2)*0.75%;IF(AND(SUM(A2:D2)>5000;SUM(A2:D2)<=6000);SUM(A2:D2)*0.85%;IF(AND(SUM(A2:D2)>6000;SUM(A2:D2)<=6500);SUM(A2:D2)*1.5%;IF(AND(SUM(A2:D2)>6500;SUM(A2:D2)<=7000);SUM(A2:D2)*1.75%;IF(SUM(A2:D2)>7000;SUM(A2:D2)*2%))))));0.05))
Appreciate your support .... Here's an attachment, Thanks!

[snb]

Why not ?


=SUM(A2:D2)*INDEX({0;0,005;0,0075;0,0085;0,015;0,0175};MATCH(SUM(A2:D2)/10^3;{3;4;5;6;6,5;7;10};1))

[jonsonbero]

Thanks a lot for sharing me my issues.but I got incorrect results ..

[p45cal]

1 udf, 3 formulae in the attached:
UDF:
=SAB(A2:D2)

Formula 1:
=CEILING(SUM(A2:D2)*VLOOKUP(SUM(A2:D2);$K$17:$L$23,2);0.05)

Formula 2:
=CEILING(SUM(A2:D2)*VLOOKUP(SUM(A2:D2);{0,0;3000.000000001,0.005;4000.00000 0001,0.0075;5000.000000001,0.0085;6000.000000001,0.015;6500.000000001,0.017 5;7000.000000001,0.02};2);0.05)
(you probably don't need as many decimal places, you'll maybe get away with 3000.001 etc.
or even Formula 3:
=CEILING(SUM(A2:D2)*VLOOKUP(SUM(A2:D2);{0,0;3001,0.005;4001,0.0075;5001,0.0 085;6001,0.015;6501,0.0175;7001,0.02};2);0.05)

ps. I may have got my commas and semicolons wrong in the above, if so the formulae should be correct in the attached file.

[jonsonbero]

my sincere thanks for taking an interest in my problem.
I found the results are correct and as expected ..

Function SAB(rng)
Dim mySum, lookupTbl
mySum = Application.Sum(rng)
lookupTbl = [{0,0;3000.000000001,0.005;4000.000000001,0.0075;5000.000000001,0.0085;6000. 000000001,0.015;6500.000000001,0.0175;7000.000000001,0.02}]
SAB = Application.Ceiling(Application.VLookup(mySum, lookupTbl, 2) * mySum, 0.05)
End Function
Another approach

Function SSS(rng As Range) As Variant Dim s As Double
Dim f As Double
s = Application.Sum(rng)
Select Case s
Case Is <= 3000
SSS = ""
Exit Function
Case Is <= 4000
f = 0.005
Case Is <= 5000
f = 0.0075
Case Is <= 6000
f = 0.0085

Case Is <= 6500
f = 0.015


Case Is <= 7000
f = 0.0175

Case Else
f = 0.02
End Select
SSS = Application.Ceiling(f * s, 0.05)
End Function

Regards

[snb]

You don't show very much interest in suggestions.
'Wrong results' is a rather meagre form of 'feedback', if any.


=IFERROR(CEILING(SUM(A2:D2)*INDEX({0,005;0,0075;0,0085;0,015;0,0175;0,02};M ATCH(SUM(A2:D2);{3001;4001;5001;6001;6501;7001;8001};1));0,05);"")