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 |
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 |