Assign a value to cell based on which column contains text

[tjaques]

I have 45 columns broken up into 9 chunks. Each of the 9 chunks is a survey question to which they enter an X in one of the 5 columns on a rating system of 1 to 5. So question #1 is column B, C, D, E, and F. Only one column contains the X. If the X is in column B, I want it to assign a value of 1, column C, a value of 2; column D, a value of 3; and up to 5. Then repeat the same thing for question 2 on up to question 9. In the cell at the end of the row, I want it to add those values.
#1 has the x under the rating of 2 (cell c4)
#2 has the x under the rating of 5 (cell L4)
#3 - 5 (r4)
#4 - 5 (x4)
#5 - 5 (ad4)
#6 - 5 (aj4)
#7 - 5 (ap4)
#8 - 3 (at4)
#9 - 5 (bb4)

Is there an easy way to say "if the x falls under 1, 2, 3, 4 or 5 then it has the corresponding value to be added at the end of the row"?

 

[Jeffrey Mahoney]

Try this:
Cell G4: =IF(B4="X",1,IF(C4="X",2,IF(D4="X",3,IF(E4="X",4,IF(F4="X",5,0)))))
copy that to M4, S4....

 

[Rick Rothstein]

Does this formula do what you want...

=MATCH("X",B4:F4,0)

 

[tjaques]

Yes! Now can I nest them together to get a sum of the 9 questions?

 

[Rick Rothstein]

Yes! Now can I nest them together to get a sum of the 9 questions?

I am a little unclear what you mean by this question. I assumed the formula I gave you was going to be put on the same row as the question it applied to so that each question would have a cell with it rating on the same row. If you did that, then all you would have to do is sum the nine cells in that column. Are you doing something else instead?

 

[tjaques]

There are 9 survey questions and each question has 5 choices. The questions are broke out across 45 columns broken down into 9 "chunks" with 5 columns in each "chunk".
So question 1 has columns b4:f4.
Question 2 has columns h4:L4.
Question 3 - N4:R4.
Question 4 - T4:X4.
Question 5 - A4:AD4.
Question 6 - AF4:AJ4.
Question 7 - AL4:AP4.
Question 8 - AR4:AV4.
Question 9 - AX4:BB4.
Each row contains the name of the person taking the survey. I want to quickly and easily be able to see an average survey result for each person. The MATCH formula works for question 1. Now I need to add questions 2-9 and be able to average that sum. Confusing I know! I'm working with another user's spreadsheet and how she set it up.

 

[Jeffrey Mahoney]

So why don't you add a column next each name and average the results from all the questions?
=Average(G4,M4,S4...)