CODEWITHSUNDEEP
google-sheets
staypuffinpc
staypuffinpc

Reputation: 337

Change value in a cell in google sheets based on multiple conditions and lookup

I have the results of a survey in one sheet and I'd like to recode the data to numerical values. However, some of the questions were coded positively and some negatively. This means I need to recode the negatively-worded questions. So, a "strongly agree" is a value of 6 on a positive question, but a value of 1 on a negatively-worded question. So, I need to first determine if the question should be positively or negatively scored, then look up the value based on a value chart in another sheet and the value in the cell with the data to be changed. Here's a visual:

Sheet 1: Responses

ID     (+) Q1         (+) Q2               (-) Q3            (+) Q4
001    Strongly Agree  Agree               Strongly disagree  Agree
002    Agree           Somewhat agree      Somewhat disagree  Somewhat agree
003    Disagree        Somewhat disagree   Strongly Agree     Disagree

Sheet 2: Scoring

Value     (+)                 (-)
6         Strongly Agree      Strongly Disagree
5         Agree               Disagree
4         Somewhat Agree      Somewhat Disagree
3         Somewhat Disagree   Somewhat Agree
2         Disagree            Agree
1         Strongly Disagree   Strongly Agree

In this example, the values for (+) Q1 would need to change to 6,5, and 1, respectively. The values for (-) Q3 would need to change to 6, 4, and 1.

Upvotes: 0

Views: 283

Answers (1)

TheMaster
TheMaster

Reputation: 50689

     =ARRAYFORMULA(IFERROR(VLOOKUP(B1:B4,{IF(REGEXEXTRACT(B1,"(\((?:\+|\-)\))")=$B$7,$B$7:$B$13,$C$7:$C$13),$A$7:$A$13},2,0),B1))

So, for the sake of simplicity, Your sheet 1 will be in A1:E4(with A as ID,B as Q1 with that exact tag (+) Q1 ) and your key will be in A7:C13 with those exact tags (Value, (+),(-))

If you enter the formula in B18, You'll get the answer key for Q1. Drag fill to the right for other Questions.

Trim your data, if you get any errors.

Edit: Mini Explanation:

      =IF formula is ERROR(VLOOKUP(Q1 column,{IF Title of Q1 column equal to title of Key II column [ (+)], then use Key II column, else use Key III Column), Value Key Column},2,0), Header of Q1))

Upvotes: 2

Related Questions