User Defined Function

[ra]

Hello Team,

I would like some help creating a multiple variable function.

The inputs are:
1) Sales Growth
2) Tier

and outputs based off following table:

SalesGrowth Tier 1 Tier 2 Tier 3
<3% 0.0% 0.0% 0.0%
3-5% 0.1% 0.2% 0.3%
5-10% 0.2% 0.6% 0.8%
10-15% 0.3% 1.0% 1.3%
15-20% 0.4% 1.4% 1.8%

20% 0.5% 2.0% 2.5%

Currently I have a created a custom function that takes a Sales Growth
e.g. 7% and converts it to a band i.e. "5-10%".
I am then using a lookup and match to get charge from above table.

My function is below - is there anyway to include both variable in
function to avoid need for lookup?
Any help or sugustions appreciated.

Function SalesCat(Growth)
Const Tier1 = "<3%"
Const Tier2 = "3-5%"
Const Tier3 = "5-10%"
Const Tier4 = "10-15%"
Const Tier5 = "15-20%"
Const Tier6 = ">20%"



' Calculates SalesCat base on Growth
Select Case Growth

Case Is <= 0.03: SalesCat = Tier1
Case 0.0301 To 0.05: SalesCat = Tier2
Case 0.0501 To 0.1: SalesCat = Tier3
Case 0.1001 To 0.15: SalesCat = Tier4
Case 0.1501 To 0.2: SalesCat = Tier5
Case Is >= 0.2001: SalesCat = Tier6
End Select
End Function

 

[RadarEye]

Hi ra,

It is not completely clear to me want you are looking for.

I have cooked a function which returns the values from your table:

Function raQuestion(SalesGrowth As Double, Tier As Integer) As Double
Select Case SalesGrowth
Case Is <= 0.03
raQuestion = 0
Case 0.0301 To 0.05
Select Case Tier
Case 1: raQuestion = 0.001
Case 2: raQuestion = 0.002
Case 3: raQuestion = 0.003
End Select
Case 0.0501 To 0.1
Select Case Tier
Case 1: raQuestion = 0.002
Case 2: raQuestion = 0.006
Case 3: raQuestion = 0.008
End Select
Case 0.1001 To 0.15
Select Case Tier
Case 1: raQuestion = 0.003
Case 2: raQuestion = 0.01
Case 3: raQuestion = 0.013
End Select
Case 0.1501 To 0.2
Select Case Tier
Case 1: raQuestion = 0.004
Case 2: raQuestion = 0.014
Case 3: raQuestion = 0.018
End Select
Case Is > 0.2
Select Case Tier
Case 1: raQuestion = 0.005
Case 2: raQuestion = 0.02
Case 3: raQuestion = 0.025
End Select
End Select
End Function

HTH,

 

[ra]

Hi ra,

It is not completely clear to me want you are looking for.

I have cooked a function which returns the values from your table:

Function raQuestion(SalesGrowth As Double, Tier As Integer) As Double
    Select Case SalesGrowth
        Case Is <= 0.03
            raQuestion = 0
        Case 0.0301 To 0.05
            Select Case Tier
                Case 1: raQuestion = 0.001
                Case 2: raQuestion = 0.002
                Case 3: raQuestion = 0.003
            End Select
        Case 0.0501 To 0.1
            Select Case Tier
                Case 1: raQuestion = 0.002
                Case 2: raQuestion = 0.006
                Case 3: raQuestion = 0.008
            End Select
        Case 0.1001 To 0.15
            Select Case Tier
                Case 1: raQuestion = 0.003
                Case 2: raQuestion = 0.01
                Case 3: raQuestion = 0.013
            End Select
        Case 0.1501 To 0.2
            Select Case Tier
                Case 1: raQuestion = 0.004
                Case 2: raQuestion = 0.014
                Case 3: raQuestion = 0.018
            End Select
        Case Is > 0.2
            Select Case Tier
                Case 1: raQuestion = 0.005
                Case 2: raQuestion = 0.02
                Case 3: raQuestion = 0.025
            End Select
    End Select
End Function

HTH,

Thank you! that works exactly. The problem I was having was using two
variables however I can see where I was going wrong now. Always good
to learn something new, cheers.

 

[Dana DeLouis]

Don't know if you would be interested in this general idea. It has no error
checking.
You have to move the "Tbl" code to one line in vba. (Broken up for posting)
Usually, Tbl refers to a group of cells on a worksheet.

Function raQuestion(SalesGrowth As Double, Tier As Integer) As Double
Dim Tbl
Tbl = [{0,0,0,0;
0.03,0.001,0.002,0.003;
0.05,0.002,0.006,0.008;
0.10,0.03,0.01,0.013;
0.15,0.04,0.014,0.018;
0.20,0.05,0.02,0.025}]

raQuestion = WorksheetFunction.VLookup(SalesGrowth, Tbl, Tier + 1)
End Function

--
Dana DeLouis

Hi ra,

It is not completely clear to me want you are looking for.

I have cooked a function which returns the values from your table:

Function raQuestion(SalesGrowth As Double, Tier As Integer) As Double
Select Case SalesGrowth
Case Is <= 0.03
raQuestion = 0
Case 0.0301 To 0.05
Select Case Tier
Case 1: raQuestion = 0.001
Case 2: raQuestion = 0.002
Case 3: raQuestion = 0.003
End Select
Case 0.0501 To 0.1
Select Case Tier
Case 1: raQuestion = 0.002
Case 2: raQuestion = 0.006
Case 3: raQuestion = 0.008
End Select
Case 0.1001 To 0.15
Select Case Tier
Case 1: raQuestion = 0.003
Case 2: raQuestion = 0.01
Case 3: raQuestion = 0.013
End Select
Case 0.1501 To 0.2
Select Case Tier
Case 1: raQuestion = 0.004
Case 2: raQuestion = 0.014
Case 3: raQuestion = 0.018
End Select
Case Is > 0.2
Select Case Tier
Case 1: raQuestion = 0.005
Case 2: raQuestion = 0.02
Case 3: raQuestion = 0.025
End Select
End Select
End Function

HTH,

Thank you! that works exactly. The problem I was having was using two
variables however I can see where I was going wrong now. Always good
to learn something new, cheers.