Formula help, please.

[saroman]

I have a table in which the entries in column A can be A, B or C and th
entries in column B can be D, E or F (A through F are text entries, no
numbers). I want column C to return numbers representing th
combination of the text entries in columns A and B. For example, i
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 retur
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

 

[Auric__]

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.

 

[saroman]

Thanks so much. Your solution worked. The only difference between you
solution and the first one suggested is that, as you indicated, th
other combinations returned "#N/A" to the cells in column C whereas th
first solution left those cells blank.

;1603256']On Fri, 29 Jun 2012 00:52:10 +0000, saroma

I have a table in which the entries in column A can be A, B or C an 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 ca
try:

=INDEX({10,6,4,2,1},MATCH(A1,{"A","B","C"},0)+MATCH(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, pos
back

 

[Ron Rosenfeld]

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.