IF statements - 26 combinations

G

Guest

I have to incorporate this in a IF statement or whatever is simple. Does
anybody have a simple solution for this?

Cell A1 can have 5 values A,B,C,D or E
Cell B2 can have 5 values V, W, X, Y or Z

Value of Cell C1 is dependent on A1 & B1

ie IF A1 = A & B1 = V, then C1 = 1
If A1 = A & B1 = W then C1 = 2
If A1 = A & B1 = X then C1 = 3 & so on.... THere are 26 combinations. What
is a easy way of writing this formula

Kind regards
 
G

Guest

I think you mean 25 combos (5 squared)
if C1 is 1 through 25
one possibilty is
Set up a range
with A1 values in the first column and B1 values in the first row
and the c1 values at the intersections
=vlookup(A1,Range,match(B1,first_row,0),0)
 
R

Ron Rosenfeld

I have to incorporate this in a IF statement or whatever is simple. Does
anybody have a simple solution for this?

Cell A1 can have 5 values A,B,C,D or E
Cell B2 can have 5 values V, W, X, Y or Z

Value of Cell C1 is dependent on A1 & B1

ie IF A1 = A & B1 = V, then C1 = 1
If A1 = A & B1 = W then C1 = 2
If A1 = A & B1 = X then C1 = 3 & so on.... THere are 26 combinations. What
is a easy way of writing this formula

Kind regards
Click to expand...

Here's one way, making use of intersection references.

Set up a table with your values, e.g.:

A B C D E
V 1 6 11 16 21
W 2 7 12 17 22
X 3 8 13 18 23
Y 4 9 14 19 24
Z 5 10 15 20 25


Select the table, and then Insert/Names/Create
Check Top Row and Left Column.

Note that when you do this, Excel will change the "C" to "C_" as "C" is not a
valid Name.

Then use this formula:

=INDIRECT(A1&IF(A1="C","_","")&" "&B2)


--ron
 
G

Guest

In another column, F enter:

AV
AW
AX
AY
AZ
BV
BW
BX
BY
BZ
CV
CW
CX
CY
CZ
DV
DW
DX
DY
DZ
EV
EW
EX
EY
EZ


then:

=MATCH(A1&B1,F1:F25)

no "if"s required
 
G

Guest

Hi,

Yes there are 25 combi..
BTW how do i set up a range? Can you be bit more clearer?
 
R

Ron Rosenfeld

Here's one way, making use of intersection references.

Set up a table with your values, e.g.:

A B C D E
V 1 6 11 16 21
W 2 7 12 17 22
X 3 8 13 18 23
Y 4 9 14 19 24
Z 5 10 15 20 25


Select the table, and then Insert/Names/Create
Check Top Row and Left Column.

Note that when you do this, Excel will change the "C" to "C_" as "C" is not a
valid Name.

Then use this formula:

=INDIRECT(A1&IF(A1="C","_","")&" "&B2)


--ron
Click to expand...

Sorry, that formula does not work as I expected it to. Please ignore.
--ron
 
G

Guest

in this case with a 5 by 5 matrix

use a set of 6 by 6 cells
A11:F16 for example
set
A12="A"
A13= "B"
....
B11="V"
C11="W"
....
 
R

Ron Rosenfeld

Here's one way, making use of intersection references.

Set up a table with your values, e.g.:

A B C D E
V 1 6 11 16 21
W 2 7 12 17 22
X 3 8 13 18 23
Y 4 9 14 19 24
Z 5 10 15 20 25


Select the table, and then Insert/Names/Create
Check Top Row and Left Column.

Note that when you do this, Excel will change the "C" to "C_" as "C" is not a
valid Name.

Then use this formula:

=INDIRECT(A1&IF(A1="C","_","")&" "&B2)


--ron
Click to expand...

The above is incorrect,

But you could set up the table; Name it Tbl, and use this formula:

=INDEX(Tbl,MATCH(B2,{"V";"W";"X";"Y";"Z"},0)+1,MATCH(A1,{"A","B","C","D","E"},0)+1)

or, shorter:

=INDEX(Tbl,CODE(UPPER(B2))-84,CODE(UPPER(A1))-63)

--ron
 
G

Guest

Hi,

I used the MATCH function & its works perfect. Thanks a lot to all those who
replied.

Kind Regards
Rajula
 
R

Rick Rothstein \(MVP - VB\)

I have to incorporate this in a IF statement or whatever is simple. Does
anybody have a simple solution for this?

Cell A1 can have 5 values A,B,C,D or E
Cell B2 can have 5 values V, W, X, Y or Z

Value of Cell C1 is dependent on A1 & B1

ie IF A1 = A & B1 = V, then C1 = 1
If A1 = A & B1 = W then C1 = 2
If A1 = A & B1 = X then C1 = 3 & so on.... THere are 26 combinations. What
is a easy way of writing this formula
Click to expand...

This simple formula should do what you want...

=5*FIND(A1,"ABCDE")+FIND(B1,"VWXYZ")-5

Rick
 
G

Guest

Hi Rick,
I used this formula. I am getting the values. The values should be betn 1 & 25
1
16
23
33
46
66
81
88
98
111
121
136
143
153
166
201
216
223
233
246
261
276
283
293
306

=5*FIND(A1,"ABCDE")+FIND(B1,"VWXYZ")-5
 
G

Guest

Hi Gary,

It works almost fine. There are some problems.
The matrix is like this
V W X Y Z
A 1 2 3 4 5
B 2 4 6 8 10
C 3 6 9 12 15
D 4 8 12 16 20
E 5 10 15 20 25

If A1 is A & B1 is V then C1 = 1 or
If A1 is A & B1 is W then C1 = 2 or
If A1 is B & B1 is V then C1 2.

This makes it slightly complicated.. Any way of solving this?

Regards
Rajula
 
R

Rick Rothstein \(MVP - VB\)

I used this formula. I am getting the values. The values should be betn 1
& 25
1
16
23
33
46
66
81
88
98
111
121
136
143
153
166
201
216
223
233
246
261
276
283
293
306

=5*FIND(A1,"ABCDE")+FIND(B1,"VWXYZ")-5
Click to expand...

I don't see how you can be getting numbers like that... the largest value
each FIND function can return, as I set them up, is a number between 1 and
5. So, at maximum for each FIND function, the largest generated value
possible is 5*5+5-5 which is 25. Are you sure you are using my formula with
your A through E letter in cell A1 and your V through Z letter in cell B1?

Rick
 
G

Guest

Hi,

I set up my matrix like this.
Col F Col G Col H Col I Col J Col K

V W X Y Z
A 1 2 3 4 5
B 2 4 6 8 10
C 3 6 9 12 15
D 4 8 12 16 20
E 5 10 15 20 25

In Col A & Col B i gave the values & in column C i entered the formula
below. But it doesnt work. Sorry..

Col A Col B Col C
A V 1
B V 2

I used the formula like this =VLOOKUP(A2,F2:K7,MATCH(B2,G3,0),0)

Regards
Rajula
 
G

Guest

Hi Ron,

The longer formula works. THe shorter one doesnt seems to work.
But for now, finally its work, so i am happy with it. And thanks for all the
effort.

Regards
Rajula
 
R

Rick Rothstein \(MVP - VB\)

It works almost fine. There are some problems.
The matrix is like this
V W X Y Z
A 1 2 3 4 5
B 2 4 6 8 10
C 3 6 9 12 15
D 4 8 12 16 20
E 5 10 15 20 25
Click to expand...

Is that the list of numbers you want us to reproduce for you? Do you really
think we would have been able to deduce that from this explanation from your
first post?
ie IF A1 = A & B1 = V, then C1 = 1
If A1 = A & B1 = W then C1 = 2
If A1 = A & B1 = X then C1 = 3 & so on.
Click to expand...

Alright, if those are the values you want, give this formula a try...

=FIND(A1, "ABCDE")*FIND(B1,"VWXYZ")

Rick
 
G

Guest

Hi Rick,

I used Ron's formula & its working. So i am going to be happy with it for
today.
I will surely find where i went wrong with your formula later.

THanks very much for your help.

Regards
Rajula
 
G

Guest

Hi,

I tried the find formula as well. Doesnt seem to work.
One of Ron's formula works. Am going to go with it for the moment.

Thanks for for patience & effort.

Regards
Rajula
 
G

Guest

change the array in the match portion of your equation and check that you
absolute cell references and relative cell references where appropriate

=VLOOKUP($A1,$F$2:$K$7,MATCH($B2,$F$1:$K$1,0),0)
 
R

Rick Rothstein \(MVP - VB\)

=FIND(A1,"ABCDE")*FIND(B1,"VWXYZ")
I tried the find formula as well. Doesnt seem to work.
One of Ron's formula works. Am going to go with it for the moment.
Click to expand...

While I don't understand the problem you had with my first formula in the
other sub-thread, I just wanted to point out this formula is not the same as
that one. My first one produced sequential numbers numbers from 1 to 25. In
this sub-thread, you seem to be saying you don't want that; rather, you
wanted the values according to the matrix you posted. The formula in this
thread should do that for you (remember, it is not the same formula you
tried from my other sub-thread).

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
if then formula 1
VLOOKUP & IF or Sumproduct 7
How to set the formula? 4
copy and paste the cell with formula ="1" 3
Need Major Help on the Macro 12
Need formula 3
help with formula 1

Top