Calculate drop-down values based on another dropdown

[Kallaks]

Hi

I am working with customer responses in drop-down values of yes, no, not sure in one column and based on the responses I would like to calculate the traction of the product

Which can be strong average or poor

So my questionnaire looks like

Q1 - yes, no, not sure
Q2- yes , no, not sure

And based on the responses I need to calculate traction

How do I achieve it

 

[Toadstool]

Hi Kallaks,

So which response pair would give Strong, Average or Poor? Do questions 1 or 2 have higher weight?

If the question doesn't matter by weight then you could just allocate Yes=1, Not sure=0 and No=-1 to the responses like this (So you'd get -2 to 2 and adding 3 let's you use CHOOSE to give the desired rating).

Kallaks.xlsx
	F	G	H	I	J	K
1	Q1	Q2		COUNTIF		Result
2	Yes	No		0		Average
3
4
5	Q1	Q2		COUNTIF		Possible
6	Yes	Yes		2		Strong
7	Yes	No		0		Average
8	Yes	Not sure		1		Average
9	No	Yes		0		Average
10	No	No		-2		Poor
11	No	Not sure		-1		Poor
12	Not sure	Yes		1		Average
13	Not sure	No		-1		Poor
14	Not sure	Not sure		0		Average
Sheet1

Cell Formulas
Range	Formula
I2,I6:I14	I2	=(COUNTIF(F2:G2,"Yes")*1)+(COUNTIF(F2:G2,"Not sure")*0)+(COUNTIF(F2:G2,"No")*-1)
K2,K6:K14	K2	=CHOOSE((COUNTIF(F2:G2,"Yes")*1)+(COUNTIF(F2:G2,"Not sure")*0)+(COUNTIF(F2:G2,"No")*-1)+3,"Poor","Poor","Average","Average","Strong")

Cells with Data Validation
Cell	Allow	Criteria
F2:G2	List	Yes,No,Not sure