Help with Convert a formula

[jonsonbero]

Hello everyone
I have a formula in the Column G as illustrated in the attachment
How can I change the formula to UDF Function so as to easily use it ?
I have included the formula with this reply so you can have a better idea. many thanks for your help

[Aussiebear]

The formula used above is


=IF(A2<=21000,"",ROUND(IF(A2<=30000,(A2-21000)*2.5%,IF(A2<=45000,(A2-30000)*10%+225,IF(A2<=60000,(A2-45000)*15%+225+1500, _
IF(A2<=200000,(A2-60000)*20%+225+1500+2250,IF(A2<=400000,(A2-200000)*22.5%+225+1500+2250+28000, _
IF(A2<=600000,(A2-400000)*25%+225+1500+2250+28000+45000,IF(AND(A2>600000,A2<=1200000), _
(A2-400000)*25%+225+1500+2250+28000+45000+IFERROR(LOOKUP(A2,{600001;700001;8000 01;900001},{525;2775;5025;8025}),0), _
IF(A2>1200000,((A2-1200000)*27.5%)+300000,0)))))))),2)/12)

[Paul_Hossler]

There's more elegant and concise ways but I think this is at least part of that ugly formula

You should be be able to follow it




Option Explicit


Function DiscAmt(NetRev As Double) As Double
Dim X As Double


Select Case NetRev
Case Is <= 21000#
X = 0#


Case Is <= 30000#
X = (NetRev - 21000#) * 0.025

Case Is <= 45000#
X = (NetRev - 30000#) * 0.1 + 225

Case Is <= 60000#
X = (NetRev - 45000#) * 0.15 + 225 + 1500

Case Is <= 200000#
X = (NetRev - 60000#) * 0.2 + 225 + 1500 + 2250

Case Is <= 400000#
X = (NetRev - 200000#) * 0.225 + 225 + 1500 + 2250 + 28000

Case Is <= 600000#
X = (NetRev - 400000#) * 0.25 + 225 + 1500 + 2250 + 28000 + 45000

Case Is <= 1200000#
X = (NetRev - 400000#) * 0.25 + 225 + 1500 + 2250 + 28000 + 45000


Select Case NetRev
Case Is <= 60000#
X = X + 525
Case Is <= 70000#
X = X + 2775
Case Is <= 80000#
X = X + 5025
Case Is <= 90000#
X = X + 8025
End Select

Case Else
X = (NetRev - 1200000#) * 0.275 + 300000#
End Select


DiscAmt = Round(X / 12#, 2)


End Function

[jonsonbero]

Paul_Hossler I thank you greatly for helping
In fact it is solved by 99% .. Just need some modifications to have it completely solved ..
see the attachment for the expected result ... Can you please check it out

[Paul_Hossler]

Option Explicit


Function DiscAmt(NetRev As Double) As Double
Dim X As Double


Select Case NetRev
Case Is <= 21000#
X = 0#


Case Is <= 30000#
X = (NetRev - 21000#) * 0.025

Case Is <= 45000#
X = (NetRev - 30000#) * 0.1 + 225

Case Is <= 60000#
X = (NetRev - 45000#) * 0.15 + 225 + 1500

Case Is <= 200000#
X = (NetRev - 60000#) * 0.2 + 225 + 1500 + 2250

Case Is <= 400000#
X = (NetRev - 200000#) * 0.225 + 225 + 1500 + 2250 + 28000

Case Is <= 600000#
X = (NetRev - 400000#) * 0.25 + 225 + 1500 + 2250 + 28000 + 45000

Case Is <= 1200000#
X = (NetRev - 400000#) * 0.25 + 225 + 1500 + 2250 + 28000 + 45000


Select Case NetRev
Case Is <= 60001#
X = X + 525
Case Is <= 70001#
X = X + 2775
Case Is <= 80001#
X = X + 5025
Case Else
X = X + 8025
End Select

Case Else
X = (NetRev - 1200000#) * 0.275 + 300000#
End Select


DiscAmt = Round(X / 12#, 2)


End Function

[jonsonbero]

This works as it should .... Appreciate your help