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.

 

[scorman]

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.

FunctionraQuestion(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)
EndFunction

Dana,
I too am looking for a "general idea" so perhaps you can enlighten me,
since I have a different and perhaps more complicated math problem
involving arrays of data. (BTW ..I do not know VBA, but Fortran
subroutines I can understand)

To keep it simple, I have a three variable data set and I have
successfully used TREND to fit a fourth order polynomial in a two way
regression.

I would like to have a UDF, which passes two variables and returns the
third.
Is it possible to incorporate the existing xls TREND functions into
the UDF or am I asking too much?

a link to my "clumsy" spreadsheet is here:
http://www.otherpower.com/images/sci...urbine_RE_.xls

each of the 3 "xxx polar" sheets are replications to return the
varible "Cl" or "Cd" from the input variables in red "AOA" and RE#"
all the original data is on left side of those polar sheets

I am presuming that to use VBA, I would have to input the actual math
formulas to do the polynomial regression which is far from trivial??

Any assistance would be appreciated, or the final answer that it
simply cannot be done.
TIA,
Stew Corman from sunny Endicott

 

[Mike Middleton]

Stew Corman or scorman -

(1) Investigate the array-entered LINEST worksheet function for obtaining
regression coefficients (either in worksheet cells or in a VBA routine).

(2) Observe the usual cautions about overfitting the data (which might occur
using a fourth-order polynomial).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

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.

FunctionraQuestion(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)
EndFunction

Dana,
I too am looking for a "general idea" so perhaps you can enlighten me,
since I have a different and perhaps more complicated math problem
involving arrays of data. (BTW ..I do not know VBA, but Fortran
subroutines I can understand)

To keep it simple, I have a three variable data set and I have
successfully used TREND to fit a fourth order polynomial in a two way
regression.

I would like to have a UDF, which passes two variables and returns the
third.
Is it possible to incorporate the existing xls TREND functions into
the UDF or am I asking too much?

a link to my "clumsy" spreadsheet is here:
http://www.otherpower.com/images/sci...urbine_RE_.xls

each of the 3 "xxx polar" sheets are replications to return the
varible "Cl" or "Cd" from the input variables in red "AOA" and RE#"
all the original data is on left side of those polar sheets

I am presuming that to use VBA, I would have to input the actual math
formulas to do the polynomial regression which is far from trivial??

Any assistance would be appreciated, or the final answer that it
simply cannot be done.
TIA,
Stew Corman from sunny Endicott

 

[scorman]

Stew Corman or scorman -

(1) Investigate the array-entered LINEST worksheet function for obtaining
regression coefficients (either in worksheet cells or in a VBA routine).

(2) Observe the usual cautions about overfitting the data (which might occur
using a fourth-order polynomial).

- Mike Middletonhttp://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

So, Mike, if I read you correctly, you claim that the coefficients
generated by LINEST could then be passed into VBA equation of the form
y=b + a1x+a2x^2 etc ...
interesting approach, thx ..I'll look into that possibility

Stew