Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula help, please.
I have a table in which the entries in column A can be A, B or C and the entries in column B can be D, E or F (A through F are text entries, not numbers). I want column C to return numbers representing the combination of the text entries in columns A and B. For example, if cell A1 is A and cell B1 is D, I want cell C1 to return the number 10. Similarly, if cell A2 is B and cell B2 is E, I want cell C2 to return the number 4.
These are the possible combinations: Column A/Column B/Column C A/D/10 A/E/6 A/F/4 B/D/6 B/E/4 B/F/2 C/D/4 C/E/2 C/F/1 How do I do this? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula help, please.
saroman wrote:
I have a table in which the entries in column A can be A, B or C and the entries in column B can be D, E or F (A through F are text entries, not numbers). I want column C to return numbers representing the combination of the text entries in columns A and B. For example, if cell A1 is A and cell B1 is D, I want cell C1 to return the number 10. Similarly, if cell A2 is B and cell B2 is E, I want cell C2 to return the number 4. These are the possible combinations: Column A/Column B/Column C A/D/10 A/E/6 A/F/4 B/D/6 B/E/4 B/F/2 C/D/4 C/E/2 C/F/1 How do I do this? Thanks. Paste this into C1 and then copy down (watch the wordwrap; this is all one line): =IF(A1="A",IF(B1="D",10,IF(B1="E",6,IF(B1="F",4,"" ))),IF(A1="B",IF(B1 ="D",6,IF(B1="E",4,IF(B1="F",2,""))),IF(A1="C",IF( B1="D",4,IF(B1="E",2,IF(B1 ="F",1,""))),""))) There's probably a better way to do it, but this works. -- He carried himself like an angry ox. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula help, please.
On Fri, 29 Jun 2012 00:52:10 +0000, saroman wrote:
I have a table in which the entries in column A can be A, B or C and the entries in column B can be D, E or F (A through F are text entries, not numbers). I want column C to return numbers representing the combination of the text entries in columns A and B. For example, if cell A1 is A and cell B1 is D, I want cell C1 to return the number 10. Similarly, if cell A2 is B and cell B2 is E, I want cell C2 to return the number 4. These are the possible combinations: Column A/Column B/Column C A/D/10 A/E/6 A/F/4 B/D/6 B/E/4 B/F/2 C/D/4 C/E/2 C/F/1 How do I do this? Thanks. Since you indicate the range of possible combinations, then you can try: =INDEX({10,6,4,2,1},MATCH(A1,{"A","B","C"},0)+MATC H(B1,{"D","E","F"},0)-1) Any other combination will result in an error message. The match is case INsensitive. If you need a case SENSITIVE match, post back. |
#4
|
|||
|
|||
Thanks so much. Your solution worked. The only difference between your solution and the first one suggested is that, as you indicated, the other combinations returned "#N/A" to the cells in column C whereas the first solution left those cells blank.
Quote:
|
#5
|
|||
|
|||
Perfect! Thanks very much. I really appreciate it.
Quote:
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula help, please.
On Fri, 29 Jun 2012 16:07:29 +0000, saroman wrote:
Thanks so much. Your solution worked. The only difference between your solution and the first one suggested is that, as you indicated, the other combinations returned "#N/A" to the cells in column C whereas the first solution left those cells blank. Glad to help. Thanks for the feedback. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Build formula using field values as text in the formula referencing another workbook | Links and Linking in Excel | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
extract formula result form cell without running formula again | Excel Programming | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |