View Question
 Question
 Subject: Excel formula Category: Computers Asked by: wenanator-ga List Price: \$115.00 Posted: 16 Nov 2003 17:35 PST Expires: 16 Dec 2003 17:35 PST Question ID: 276576
 ```I need to create a formula in excel. I have 10 categories of numbers that are based on letters D through L vertical, and 8 symbols horizontally. THESE NUMBERS AND FIGURES FOR THE 8 CATEGORIES HAVE ALREADY BEEN ENTERED ALREADY. category 1 if the item is ANYWHERE FROM to .30-.37 ITEM 1 ITEM 2 ITEM 3 ETC. ITEM 8 D E F G H I J K L category 2 if the item is EQUAL ANYWHERE FROM to.38-.45 ITEM 1 ITEM 2 ITEM 3 ETC. ITEM 8 D 60 -->50<-- ETC ETC E 62 F 63 G 59 H 56 I ETC J K L NEXT, I will be entering in 3 variables in another section. The first number is to do with the size, FROM .3 TO 1.5, the second is the letter, either D-L, and the final is whether it is Item 1-8. BASED ON WHAT I ENTER I NEED A FORMULA TO SAY "IF CELL B2 IS D, AND CELL C2 IS ITEM 2 AND CELL A2 FALLS IN CATEGORY 1, THEN APPLY THE FORMULA THAT WILL= (The SIZE entered [A2] * THE REFERENCED NUMBER {IE. D, ITEM 2 IN CATEGORY 2 * 70) BUT....IF THE ITEM IS D, ITEM 2, BUT IN CATEGORY 6 AS OPPOSED TO CATEGORY 2, THE ABOVE FORMULA IS IN ESSENCE FALSE AND A NEW FORMULA WOULD HAVE TO APPLY, OR IF IT WASN'T D, ETC. I AM NOT SURE IF 7200 FORMULAS NEED TO BE DONE (WHICH MAY HAVE NEED TO BE STRUNG TOGETHER SOMEHOW) , OR IF ARGUMENTS CAN BE ENTERED THAT FILTERS OUT RESULTS, IF THE FORMULAS NEED TO BE STRUNG TOGETHER, I NEED TO KNOW HOW TO DO IT PROPERLY WHILE KEEPING THE CELL REFERENCES IN PROPER SHAPE SO THAT THE REFERENCES CARRY THROUGH. I BELIEVE THAT MY INITIAL FORMULA IS NOT CORRECT FOR DOING MULTIPLE FORMULAS NOR FOR CARRYING THROUGH CELL REFERENCES. SEE BELOW =SUM(AND(((B2="D")),((C2="ITEM2")),(AND(A2<0.38,A2>0.29)))*\$G\$2*A2*0.7) <--LAST PART IS THE REFERENCED NUMBER i HAVE CREATED FORMULAS JUST LIKE THE ONE ABOVE THAT ALL POINT TO A2 AS THE BEGINNING CELL SO THAT IF I HAD TO STRING TOGETHER A FORMULA, THEN IT WOULD 'HOPEFULLY' MOVE DOWN TO APPLY TO A3, A4, ETC. IF RESTRINGING IS REQUIRED, I WILL NEED TO MAKE SURE THAT THIS CARRIES THROUGH SO THAT I DON'T SPEND TIME CHANGING CELL REFERENCES.``` Request for Question Clarification by answerguru-ga on 16 Nov 2003 19:53 PST ```Hi there! I can certainly code this formula for you, however just to eliminate any inconsistency in my code and the tables that you have already defined (for each category) it would be better if you could post your excel file somewhere for me to download. Otherwise, I could develop it in a generic fashion so that you could go and make the appropriate changes after I deliver the formula. At any rate, I have already started with an outline of what needs to be done and I'm fairly confident this can be achieved :) answerguru-ga``` Clarification of Question by wenanator-ga on 17 Nov 2003 14:05 PST ```A generic outline would be best. Sopmetimes ranges need to be changed, and sometimes the columns and the other items change as well. If you need anything from me let me know. thanks,``` Request for Question Clarification by answerguru-ga on 18 Nov 2003 07:59 PST ```Just a quick update for you - I have started on this function and estimate that it will be complete by tomorrow evening at the latest. Is this acceptable? answerguru-ga``` Clarification of Question by wenanator-ga on 18 Nov 2003 14:10 PST ```Not a problem. Better to do the job right the first time so that we don't have to go back and forth and that time isn't wasted if there are problems.```
 Subject: Re: Excel formula Answered By: answerguru-ga on 20 Nov 2003 00:17 PST Rated:
 ```Hi wenenator-ga, I have completed the formula for you as requested - I will just give you a brief insight into my process so that you can mimick it on your side when setting thing up: 1. Create an excel file with 10 worksheets 2. Each worksheet contains a grid as you defined in your question within the range A1:I10 (this is necessary as the code is currently written) 3. Opened the VB Editor from Excel (Alt+F11) 4. Created a new module (Module1) by right-clicking on the VBAProject for the current file and Insert > Module 5. Within the module I entered the following code: 'BEGIN CODE Function CategoryLookup(value As Double, letter As String, item As Integer) As Double ' Defining required variables Dim activeSheet As Worksheet Dim sheetIndex As Integer Dim rowVal As Integer Dim colVal As Integer ' Determine category sheet based on value argument If value >= 0.3 And value <= 0.37 Then sheetIndex = 1 ElseIf value >= 0.38 And value <= 0.45 Then sheetIndex = 2 ElseIf value >= 0.46 And value <= 0.53 Then sheetIndex = 3 ElseIf value >= 0.54 And value <= 0.61 Then sheetIndex = 4 ElseIf value >= 0.62 And value <= 0.69 Then sheetIndex = 5 ElseIf value >= 0.7 And value <= 0.77 Then sheetIndex = 6 ElseIf value >= 0.78 And value <= 0.85 Then sheetIndex = 7 ElseIf value >= 0.86 And value <= 0.93 Then sheetIndex = 8 ElseIf value >= 0.94 And value <= 1.01 Then sheetIndex = 9 ElseIf value >= 1.02 And value <= 1.09 Then sheetIndex = 10 ElseIf value >= 1.1 And value <= 1.17 Then sheetIndex = 11 Else sheetIndex = -1 ' value could not be found - error flag End If 'quit if value given is not in defined range If sheetIndex = -1 Then CategoryLookup = -1 'return error and quit End If 'resolve row based on letter provided If letter = "D" Then rowVal = 2 ElseIf letter = "E" Then rowVal = 3 ElseIf letter = "F" Then rowVal = 4 ElseIf letter = "G" Then rowVal = 5 ElseIf letter = "H" Then rowVal = 6 ElseIf letter = "I" Then rowVal = 7 ElseIf letter = "J" Then rowVal = 8 ElseIf letter = "K" Then rowVal = 9 ElseIf letter = "L" Then rowVal = 10 Else rowVal = -1 'invalid letter entered End If 'resolve row based on letter provided If item >= 1 And item <= 8 Then colVal = item + 1 Else colVal = -1 'invalid item number entered End If 'quit if row/col values cannot be determined If rowVal = -1 Or colVal = -1 Then CategoryLookup = -1 'return error and quit Else 'retrieve appropriate value and return it CategoryLookup = ActiveWorkbook.Sheets(sheetIndex).Cells(rowVal, colVal) End If End Function 'END CODE After saving you should be able to access this function like any other worksheet function by calling it from a cell within the workbook. For example, the function call: =CategoryLookup(0.40, "F", 4) will return the value corresponding to Item 4, letter F in the sheet for the category representing values containing 0.40. In general, providing arguments to the function that are invalid will result in a return value of -1. I assume this value will never occur within any of your category tables. I have tried to strategically place comments in the code so you know where certain things are occuring - if any of it doesn't make sense please post a clarification and I'll be happy to help :) Cheers! answerguru-ga``` Clarification of Answer by answerguru-ga on 20 Nov 2003 00:19 PST ```Just wanted to also point out that you had mentioned your input values ranged from 0.3 to 1.5, however the pattern you provided only resulted in values up to 1.17 being handled. You can change the ranges yourself as appropriate :) answerguru-ga``` Request for Answer Clarification by wenanator-ga on 20 Nov 2003 13:50 PST ```Hi Answer: Need a 2 1/2 Part Clarification Please I'm not able to get it yet. When I said that something was Item 1, Item 2, etc,.. that is an operator, a figurative reference,(i believe) similar to D through L. For the moment, Lets use AB as item 1, AC as Item 2, AD, etc for ease. One thing that I think may be preventing the full formula is that as the rest of the formula says if D, then... if E, etc, I don't see anything alloted for if column (or row or whatever) is AB, then..., if AC, then... I hope that you understand what I am saying with this. I am also having a bit of dificulty understanding how to properly reference the formula. When you say: "After saving you should be able to access this function like any other worksheet function by calling it from a cell within the workbook. For example, the function call: =CategoryLookup(0.40, "F", 4) will return the value corresponding to Item 4, letter F in the sheet for the category representing values containing 0.40." I know nothing about VB so I don't know how to reference any formulas that are created. All i know is to do a basic Excel reference like =[Formula.xls]Sheet1:Sheet8'!\$A\$1:\$I\$10 Should I be putting in my entries on the same file, or does it matter?``` Clarification of Answer by answerguru-ga on 20 Nov 2003 23:46 PST ```Hi wenenator-ga, In response to your clarifications: The reason I did not include a set of if statements for the columns is because the structure you intially provided was Item 1, Item 2, etc. I was able to use the number and add one to it in this section: If item >= 1 And item <= 8 Then colVal = item + 1 Else colVal = -1 'invalid item number entered End If Regarding the use of the formula, it is quite simple and very much like any excel worksheet function that is built into the program. For example, I'm sure you're familiar with Excel's SUM function, which you can use like this: =SUM(A1:A20) This information is entered directly into any worksheet cell and adds the values in the provided range. Similarly, you can use this function (once it has been saved in a Module) by typing the following formula into any cell in the workbook: =CategoryLookup(0.40, "F", 4) This has nothing to do with VBA from the user's point of view - just assume you are using a regular formula (that is how the custom formula has been designed in Excel). Regarding your question about "entries", I'm not quite sure what you are referring to - do you mean your category tables or the place where you actually use the formula? answerguru-ga``` Request for Answer Clarification by wenanator-ga on 21 Nov 2003 00:30 PST ```Hi Guru: I would definitely need the "if" statements for the 'item' columns, as mentioned previously, sometimes the names of the column items change and so there isn't necessarily a static number system. With respect to the category lookup, I think I get it now. Basically what is happening is that based on the numbers provided it is going to return a value using the parameters in the other sheet. It is THEN that I need to create whatever formula that applies to the number that is referenced. Got it. Almost there Guru. Thanks for the patience, and if you could just revamp the Item Columns into a generic "if" form like the others then I think that it should work out.``` Clarification of Answer by answerguru-ga on 21 Nov 2003 05:44 PST ```Actually, the way it currently works, the column lookup has nothing to do with the label of the column, so you can safely change that without affecting the formula. It is actually the order of the columns that matters here. For example, given one of your example tables: category 2 if the item is EQUAL ANYWHERE FROM to.38-.45 ITEM 1 ITEM 2 ITEM 3 ETC. ITEM 8 D 60 -->50<-- ETC ETC E 62 F 63 G 59 H 56 I ETC J K L The first column containing values is column two, therefore if the formula is: =CategoryLookup(0.40, "F", 1) Then we know the return value 63. You could equivalently change all the labels of the columns as follows: category 2 if the item is EQUAL ANYWHERE FROM to.38-.45 AB AC AD ETC. AX D 60 -->50<-- ETC ETC E 62 F 63 G 59 H 56 I ETC J K L Even under this scheme, the same formula: =CategoryLookup(0.40, "F", 1) would result in a value of 63. Therefore you don't really need to change the code - do you see how it works now? answerguru-ga``` Request for Answer Clarification by wenanator-ga on 21 Nov 2003 17:03 PST ```Hi Answer: Unfortunately it isn't going too smoothly. As mentioned above, I need to be able to do the following: BASED ON WHAT I ENTER I NEED A FORMULA TO SAY "IF CELL B2 IS D, AND CELL C2 IS ITEM 2 AND CELL A2 FALLS IN CATEGORY 1, THEN APPLY THE FORMULA THAT WILL= (The SIZE entered [A2] * THE REFERENCED [looked up] NUMBER {IE. D, ITEM 2 IN CATEGORY 2 * 70) Although I'm sure that you've seen it before, I am just going to put the exact two sheets that I am working with. We will call this Sheet 1 and the title of the document is simply FORMULA with the range applying only to .30-.39 COLUMNS A B C D E F G H I AB AC AD AE AF AG AH AI D 46 40 35 32 29 22 18 13 E 41 37 33 30 27 21 17 12 F 38 35 30 28 25 20 16 11 G 36 32 28 25 22 18 15 10 H 30 26 22 20 18 16 14 9 I 23 20 19 18 16 15 13 9 J 18 17 16 15 14 13 12 8 K 16 15 14 13 12 11 10 8 L 13 12 12 11 11 10 9 6 SHEET 2 FOR ITEMS WITHIN THE RANGE OF .40-.47 COLUMNS A B C D E F G H I AB AC AD AE AF AG AH AI D 75 60 54 48 44 38 31 20 E 60 54 50 46 43 36 29 19 F 54 50 47 43 39 33 27 18 G 50 45 42 39 34 29 24 16 H 44 39 35 33 29 26 21 15 I 35 31 28 26 24 22 20 14 J 27 25 24 22 21 20 19 13 K 23 22 21 19 18 17 16 12 L 21 20 20 18 18 17 15 10 IN A SEPEARATE FILE (<-I GATHER) I WILL BE ENTERING IN ITEMS SUCH AS THE FOLLOWING: LENGTH LETTER SYMBOL FORMULA? 0.30 H AG ??? 0.30 J AF ? 0.40 I AF 0.45 H AF 0.46 I AG 0.49 G AG 0.51 I AF 0.54 I AF 0.61 H AG 0.61 I AF 0.71 I AF 0.81 H AG 0.88 H AF 0.92 I AG 1.06 G AH 1.10 I AH 1.15 I AG 1.26 H AF 1.36 I AF in the above example, the first thing that should happen in the formula is that based on COLUMN A being .30,COLUMN B BEING H,& COLUMN C BEING AG it should use sheet 1 of the 'formula' document, and based on it matching 'H' & 'AG' a value of 16 should be spat out, THE SECOND ROW WOULD EQUAL 14, THE THIRD WOULD GO TO SHEET 2, using the same column/row parameters, THUS IT WOULD BE 24. i can handle the rest from there, but so far I haven't gotten anything near that and have only managed to get a REF#. Also, one area where I think I may be going off track is the formula script that you gave me. I am not sure if I am supposed to make any changes to it like entering in the workbook name, sheet number, etc, etc..and if so, what/how changes need to be made. If this is the case, please let me know somehow. So, in the area where it says formula, I need the EXACT formula that I should enter with the exact reference area, as well as if changes need to be made, which ones. I can't create a query for each operation or one cell that says, .30,f,ag it needs to be based on column a, then b, then c.``` Clarification of Answer by answerguru-ga on 21 Nov 2003 18:53 PST ```Hi sgain, I wish you had given me this information earlier - the reason the function will not accept that input is because it is expecting a numeric value in the last parameter. I will fix the code and have the result for you later tonight. answerguru-ga``` Clarification of Answer by answerguru-ga on 21 Nov 2003 18:59 PST ```I also wanted to point out that since you are supposed to be placing the code into a Module, you will need to use the formulas in the same workbook as where the tables of values are located. Therefore if you have 10 categories, Sheet1 through Sheet10 will hold data in tables, and then you can use the formula within that same book in any sheet number higher than 10. answerguru-ga``` Clarification of Answer by answerguru-ga on 21 Nov 2003 19:22 PST ```OK, here is the updated code according to the sample you have provided: 'BEGIN CODE Function CategoryLookup(value As Double, letter As String, symbol As String) As Double ' Defining required variables Dim activeSheet As Worksheet Dim sheetIndex As Integer Dim rowVal As Integer Dim colVal As Integer ' Determine category sheet based on value argument If value >= 0.3 And value <= 0.37 Then sheetIndex = 1 ElseIf value >= 0.38 And value <= 0.45 Then sheetIndex = 2 ElseIf value >= 0.46 And value <= 0.53 Then sheetIndex = 3 ElseIf value >= 0.54 And value <= 0.61 Then sheetIndex = 4 ElseIf value >= 0.62 And value <= 0.69 Then sheetIndex = 5 ElseIf value >= 0.7 And value <= 0.77 Then sheetIndex = 6 ElseIf value >= 0.78 And value <= 0.85 Then sheetIndex = 7 ElseIf value >= 0.86 And value <= 0.93 Then sheetIndex = 8 ElseIf value >= 0.94 And value <= 1.01 Then sheetIndex = 9 ElseIf value >= 1.02 And value <= 1.09 Then sheetIndex = 10 ElseIf value >= 1.1 And value <= 1.17 Then sheetIndex = 11 Else CategoryLookup = -1 'return error and quit End If If letter = "D" Then rowVal = 2 ElseIf letter = "E" Then rowVal = 3 ElseIf letter = "F" Then rowVal = 4 ElseIf letter = "G" Then rowVal = 5 ElseIf letter = "H" Then rowVal = 6 ElseIf letter = "I" Then rowVal = 7 ElseIf letter = "J" Then rowVal = 8 ElseIf letter = "K" Then rowVal = 9 ElseIf letter = "L" Then rowVal = 10 Else rowVal = -1 'invalid letter entered End If If symbol = "AB" Then colVal = 2 ElseIf symbol = "AC" Then colVal = 3 ElseIf symbol = "AD" Then colVal = 4 ElseIf symbol = "AE" Then colVal = 5 ElseIf symbol = "AF" Then colVal = 6 ElseIf symbol = "AG" Then colVal = 7 ElseIf symbol = "AH" Then colVal = 8 ElseIf symbol = "AI" Then colVal = 9 Else colVal = -1 'invalid item number entered End If If rowVal = -1 Or colVal = -1 Then CategoryLookup = -1 'return error and quit Else CategoryLookup = ActiveWorkbook.Sheets(sheetIndex).Cells(rowVal, colVal) End If End Function 'END CODE Make sure that you replace the old version of the code with this new one. Now you should be able to use the formula as follows: =CategoryLookup(0.30, "H", "AG") Equivalently, you can also just put cell references corresponding to these values into the formula. In the example you provided earlier, let's assume that the first row of data are in cells A2, B2, and C2 (length, letter, symbol). Then we can call the formula as follows: =CategoryLookup(A2,B2,C2) I have tested this thoroughly and you should not have any problems with it. answerguru-ga```
 wenanator-ga rated this answer: ```Very helpful and patient in helping make sure that everything is perfect on my end. I am Extremely Satisfied. Thanks AnswerGuru```

 `Just wondering - anything to do with diamonds?`
 ```I think you would find this website very usefull I know I have - and amazingly its free advice! http://www.mrexcel.com/index.html```