• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return number based on two multi-faceted criteria

P

PsychPhD

New Member
This is an introduction and question post all in one.

Hi there, I'm a newbie to this site and a total noob with Excel. Well, not total, but easily confused by formula jargon.


I am trying to create a Rorschach (inkblot test) scoring worksheet to help make my life easier. But of course it is merely confusing me.


Here's the situation:


1. In scoring, certain types of answers are given certain scores, which have different values according to which card they refer to. So in my workbook, in column one, I identify the card number, I - X (1 through 10 in Roman Numerals). I have this column as a validated data list, with 10 possible values (obviously I - X).


2. In column 11, I assign a Z-score. This is a validated data list with four possible answers: ZW, ZA, ZD, or ZS


3. Each Z-score is worth a different number, depending on which card it refers to. So, for example, on Card I, ZW=1.0, ZA=4.0, ZD=6.0 and ZS=3.5. Whereas on Card X, ZW=5.5, ZA=4.0, ZD=4.5, and ZS=6.0


4. Rather than having to manually look up the values every time I score a protocol, I want to be able to just enter the Z-score in column 11 and have the correct Z-score number appear in column 12. So there are two conditions: In each row, what is the value of the cell in column one (I, II, III, IV, V, VI, VII, VIII, IX, or X), and then what is the value of the cell in column 11 (ZW, ZA, ZD, or ZS)


5. I have no idea if this will help matters, but within the same workbook on a separate sheet, I have created a list consisting of three columns: column 1 has four instances of each Roman Numeral, column 2 has each Z-score, and then column 3 has the appropriate number. So it looks like this:

I ZW 1.0

I ZA 4.0

I ZD 6.0

I ZS 3.5

II ZW 4.5

II ZA 3.0

etc.


How do I do this? I don't think If/Then will work because of the number of possibilities. I don't know how to properly combine VLookups and Ifs and ANDs and the like to make this happen. I would greatly, tremendously appreciate any brilliant advice anyone could offer. And I would appreciate it even more if it is written in language even a non-excel-ninja like me could understand.


Thank you so much!
 
PsychPhD - Welcome to Chandoo.


I'm sure there will be different ideas on your problem. It's always fun to watch the answers evolve. Here's a first attempt:


On your Z score sheet you should type the score values in a 4 X 10 matrix. For example, the value for Card I-ZW will be in A1, Card I-ZA will be in B1 ... through Card X-ZS in cell D10. Highlight the matrix and use the Name Manager to make a named range (I called mine ZScores).


Assuming you start with Card I in row 1, a function like this might work:


=INDEX(ZScores,ROW(D1),FIND(E1,"WADS"))


This will pick the correct card value from the ZScores matrix using the card's row number (D1) and rating (E1). This function assumes you entered the Z score in capitals using only the final letter in the z-score (the z is redundant since it occurs in all choices).
 
Hmm. That didn't quite work as expected.


I've uploaded the file here:

http://speedy.sh/J9n2a/Rorschach-Sequence-of-Scores.xlsx


I tried using a combo of INDEX and MATCH, based on something I saw on another site, because there aren't always the same amount of responses per card. So there could be 5 Card I's, 2 Card II's, 1 Card III, 6 Card IVs, etc. So I need it to look at the first column on the sheet to see what the Card is, then match with the corresponding value based on what is entered in the Z-score column.


I greatly appreciate the help!
 
I figured it out!

I had to start on a new workbook and copy the info, because apparently at one point I had put on protections that were just messing everything up.

But I ended up with this formula:

=INDEX(Sheet2!$B$2:$E$11,MATCH(Sheet1!A4, Sheet2!$A$2:$A$11, 0), MATCH(Sheet1!K4, Sheet2!$B$1:$E$1, 0))


and it works like a charm!
 
Even better, I did this:

=IF(ISERROR(INDEX(Sheet2!$B$2:$E$11,MATCH(Sheet1!A4, Sheet2!$A$2:$A$11, 0), MATCH(Sheet1!K4, Sheet2!$B$1:$E$1, 0)))=TRUE,"",INDEX(Sheet2!$B$2:$E$11,MATCH(Sheet1!A4, Sheet2!$A$2:$A$11, 0), MATCH(Sheet1!K4, Sheet2!$B$1:$E$1, 0)))


So now no more ugly NAs and it only enters a value when the conditions are met.
 
You must log in or register to reply here.
Back
Top