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.

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.

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.

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.

Perfect! Thanks very much. I really appreciate it.

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.