Classify cases according to 2 categorical variables

[mabras]

hello everyone,
I would need to classify subjects by crossing two existing categories.
I have two variabiles with 4 categories each, giving rise to a 4x4= 16 categories classification.

|var01|var02|NEW|
| A | A | 1
| A | B | 2
| A | C | 3
| A | D | 4
| B | A | 5
| B | B | 6
(...)
| D | D | 16

What should I do in order to automatically classify subjects according to these two variables?

Example:
ss|var01|var02|
01| A | C |=> RESULT: 3

thanks for the help!
Mabras

 

[mancemonster]

Here's how I would do it..


Create a table...

A16:D16

In column A list Variable 1
In Column B List Variable 2
In Column C Formula =A1&B1 Dragged down to =A16&B16
In Column D 1-16 listed down.


Assuming F1 & G1 are where your valiables are to be entered & H1 is where you want your result..

H1 =IF(ISERROR(VLOOKUP(F1&G1,C1:D16,2,0)),"Need Variables",VLOOKUP(F1&G1,C1:D16,2,0))

If G1 or H1 are missing/invalid it will result in Need Variables otherwise it should work.

Regards


Roger

 

[Rick Rothstein]

Assuming columns A and B will always get filled together, you could use this formula in your RESULT's column...

=(2+SEARCH(A2&B2,"AA AB AC AD BA BB BC BD CA CB CC CD DA DB DC DD"))/3

If you don't want to see the intermediary calculation that results when one of the cells is filled in while waiting for the other to get filled in, you could use this extended form of the above formula to hide then displayed value until both cells are filled in...

=IF(OR(A2="",B2=""),"",(2+SEARCH(A2&B2,"AA AB AC AD BA BB BC BD CA CB CC CD DA DB DC DD"))/3)

 

[mabras]

thank you so much, it works! I do not have missing values though... could then the forumla be simplified?

 

[Rick Rothstein]

thank you so much, it works! I do not have missing values though... could then the forumla be simplified?

If you were referring to the formulas I posted, the first one is the simplified version. If you use it, what happens is when you put a value in one of the cells, a number, usually a floating point one, is displayed, but the correct value won't be displayed until the second cell is filled in. The second formula I posted does the identical thing as the first formula, but it holds back displaying anything until both cells have been filled in. So, you have a choice of which function to use depending on whether the display of an intermediary floating point number bothers you or not.

 

[mabras]

thanks Rick, it works also! So simple and elegant!
Mabras