Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function
Hello Team,
I would like some help creating a multiple variable function. The inputs a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function
On 26 Feb, 20:45, RadarEye wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function
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 "ra" wrote in message ... On 26 Feb, 20:45, RadarEye wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function
On Feb 27, 9:20 am, "Dana DeLouis" wrote:
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 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 he 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function
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 "scorman" wrote in message ... On Feb 27, 9:20 am, "Dana DeLouis" wrote: 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 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 he 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function
On Mar 3, 11:56 am, "Mike Middleton" wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using dcount function within user-defined worksheet function | Excel Programming | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |