User Defined Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ra - 26 Feb 2008 16:36 GMT

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

Reply to this Message

RadarEye - 26 Feb 2008 20:45 GMT

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,

Reply to this Message

ra - 27 Feb 2008 08:37 GMT

> Hi ra,
>
[quoted text clipped - 40 lines]
>
> 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.

Reply to this Message

Dana DeLouis - 27 Feb 2008 14:20 GMT

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

Signature

Dana DeLouis

> Hi ra,
>
[quoted text clipped - 40 lines]
>
> 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.

Reply to this Message

scorman - 03 Mar 2008 16:31 GMT

> Don't know if you would be interested in this general idea. It has no error
> checking.
[quoted text clipped - 15 lines]
> --
> Dana DeLouis

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

Reply to this Message

Mike Middleton - 03 Mar 2008 16:56 GMT

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
[quoted text clipped - 47 lines]
> TIA,
> Stew Corman from sunny Endicott

Reply to this Message

scorman - 03 Mar 2008 18:02 GMT

> Stew Corman or scorman -
>
[quoted text clipped - 6 lines]
> - 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

Reply to this Message