View Question
Q: Cross reference a number based on data from two cells plus two modifiers. ( Answered ,   0 Comments )
 Question
 Subject: Cross reference a number based on data from two cells plus two modifiers. Category: Computers Asked by: thewenanator-ga List Price: \$50.00 Posted: 29 Sep 2006 13:20 PDT Expires: 29 Oct 2006 12:20 PST Question ID: 769535
 ```I think in a nutshell that is about it. Just kidding. Someone will enter a number is cell A1, then a number in B1, then a name in C1. I have two charts spanning from O3:Y14 and O18:Y29. The formula would first need to pick which chart to use based on C1. From there, it would need to cross reference the number on the chart based on A1,B1. So what will be entered is. (in column A it would be 2-12, Column B would be 3-12, Column C would either be F or T, Column D would either be P, 19, or if left blank would be the number referenced in the chart) 5 10 F PL (say F is the first chart listed.) With the graph below (i shortened it for ease), the answer would be 8.56 and this would then need to be multiplied by 1.6 if P is selected in column D, 1.18 if 19 is selected for Column D, and if nothing is selected in Column D then automatically the answer of 8.56 would be there. i would put this answer into column E as more calculations would need to be applied onto it later. 3 4 5 6 7 8 9 10 2 2.63 2.77 2.9 3.04 3.18 3.33 3.45 3.59 3 3.86 4.05 4.25 4.45 4.65 4.85 5.05 5.25 4 5.06 5.32 5.58 5.84 6.11 6.37 6.63 6.9 5 6.28 6.6 6.92 7.24 7.58 7.89 8.22 8.56 6 7.5 7.87 8.26 8.64 9.04 9.41 9.81 10.21 7 8.71 9.15 9.6 10.04 10.51 10.93 11.4 11.87 8 9.93 10.42 10.94 11.44 11.97 12.45 12.99 13.52 9 11.14 11.7 12.28 12.84 13.44 13.97 14.58 15.18 10 12.36 12.97 13.62 14.24 14.9 15.49 16.17 16.83 11 13.57 14.25 14.96 15.64 16.37 17.01 17.76 18.49 12 14.79 15.52 16.3 17.04 17.83 18.53 19.35 20.14``` Request for Question Clarification by maniac-ga on 30 Sep 2006 18:00 PDT ```Hello Thewenanator, I believe I have a solution for your question however I am not certain of a few points: [1] You indicate that C1 is a "name". Do you mean that C1 has a defined name such as created with the menu Insert -> Name -> Define or some other reference (e.g., a value used to look up a reference)? [2] You indicate the multipiers based on the D1 value are P - 1.60 19 - 1.18 blank - 1.00 Yet the example has PL as the value in D1. [3] I assume you want a formula in E1 that does the appropriate processing. Is that correct? Thanks. --Maniac``` Clarification of Question by thewenanator-ga on 02 Oct 2006 14:30 PDT ```Hi Maniac, C1 would be 'text'. I should have been a bit more clear on that. They are either going to use the letter F, or T. F meaning that the calculations should be for Chart one, T would have the calculations based on chart 2. I put PL, but it should have read simply "P". The only thing i might be concerned about would be if they accidentally type more of the word instead of the abbreviated "P"... I know that it will probably show an error if someone entered a variable that isn't recognized so don't worry about this. I will put a legend into the chart to guide them. E1 would have the appropriate formula/processing, thank you.```
 Answer
 Subject: Re: Cross reference a number based on data from two cells plus two modifiers. Answered By: maniac-ga on 02 Oct 2006 17:51 PDT Rated:
 ```Hello Thewenanator, Thanks for the clarification of the question. The formula (two version) to paste into cell E1 is included at the end of this answer. The first formula assumes that chart one (indexed by "F") starts at cell A3 (or row 3, column 1) and chart two (indexed by "T") starts at cell A17 (or row 17, column 1). I assume the values to look up are in different locations. I'll explain the values to adjust to correct the formula for your situation (or use the second version). Let me explain the formula in detail as well as the methods used to determine the correct operation. The functions I used are: IF INDIRECT TEXT if you type those phrases into the Microsoft Excel help, you can find additional information related to those functions. I also used the string operator & to concatenate two strings together. First, based on your description, multiple values need to be looked up for the multiplier. I used nested IF functions to do this as follows: IF(D1="P",1.6,IF(D1=19,1.18,1)) This compares D1 to the string value "P" and if true, returns 1.6. If D1 is not "P", the next check is for the number 19 (not the string 19) and if true, returns 1.18. If both are false, the value 1 is returned. If you want to explicitly check for blank, revise this to something like: IF(D1="P",1.6,IF(D1=19,1.18,IF(D1=" ",1,0))) which adds the explicit check & returns zero if none are matched. Alternatively, you could put 1/0 in place of 0 to generate an error value (#div/0!) if desired. Second, based on your description, we need to use another IF function to determine which table (or chart) of data to use. The construct is something like this: IF(C1="F",[formula for chart F], [formula for chart T]) Again, if you need to explicitly check for "T" (and not any other value), this can be rewritten like IF(C1="F",[formula for chart F], IF(C1="T",[formula for chart T],0)) You can also generate an error like described previously if desired as well. Third, based on your description, we need to look up a value based on the data from the two cells (A1 and B1). To do this, I used the "INDIRECT" function - one I find rarely used by Excel users. There are several good online references for this function as well - search online with a phrase like Excel indirect for those sites. Since I indicated that F started at cell A3 (or row 3, column 1), the formula to look up the value is: INDIRECT(TEXT(2+A1,"R#")&TEXT(B1-1,"C#"),FALSE) This is a little complicated, so let me explain the steps involved. INDIRECT takes a cell reference in A1 format or R1C1 (row / column) format. I am using the latter, so the second parameter must be False. The first TEXT function call generates the row reference (R5) using the two plus the value in A1 and the format R# (the letter R followed by the numeric value). The second TEXT function call generates the column reference (C9) using the value in B1 minus one and the format C# (the letter C followed by the numeric value). The two strings are concatenated to produce the string value R5C9 used to look up the value 8.56 as described in your question. In a similar manner, the values in "T" are referenced with the second INDIRECT function call using row and column offsets of +16 and -1 to compute the proper cell references. Since I expect your values (for "F" and "T") to be in different locations, adjust the values (+2, -1) and (+16, -1) to refer to the proper row / column to look up the values. As another alternative - if you have defined names for the two regions of values ("F" and "T"), you can use a pair of functions ROW and COLUMN to lookup the starting row & column of the defined names. I have included another version of the formula at the end using this method - assuming the defined names of "F" and "T" for the range of look up values. This second version will adjust the row / column references automatically if you add / remove rows or columns so that "F" and "T" moves. The first version would have to be fixed manually if you reorganized the spreadsheet in this way. If you have any difficulty adapting either formula for your specific situation, if any part of the answer is unclear, or you need additional information on this topic, please make a clarification request. I would be glad to help you further. Good luck with your work. --Maniac The formula is split into several lines so to make it more readable - combine into a single formula when you copy & paste into your cell at E1. =IF(D1="P",1.6,IF(D1=19,1.18,1))* IF(C1="F",INDIRECT(TEXT(2+A1,"R#")&TEXT(B1-1,"C#"),FALSE), INDIRECT(TEXT(16+A1,"R#")&TEXT(B1-1,"C#"),FALSE)) The second alternative, using names F and T to refer to the two ranges of look up values. This also includes an example of an error generated if the value isn't set properly in cell D1. =IF(D1="P",1.6,IF(D1=19,1.18,IF(D1=" ",1,1/0)))* IF(C1="F", INDIRECT(TEXT(A1+ROW(F)-1,"R#")&TEXT(B1+COLUMN(F)-2,"C#"),FALSE), INDIRECT(TEXT(A1+ROW(T)-1,"R#")&TEXT(B1+COLUMN(T)-2,"C#"),FALSE))```
 thewenanator-ga rated this answer: ```Excellent, thank you very much. I got it to work and i understand how the chart pulls off the numbers (so if i need to move my chart or add lines i understand how to make that work).```

 Comments
 There are no comments at this time.
 Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service. If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for

 Google Home - Answers FAQ - Terms of Service - Privacy Policy