Populate a new value based on a range

[marcus314]

Hello all,

I have a range of cells with either one or more “Yes” values, one or more “No” values, or a combination of both “Yes” and “No” values.

I’m wishing to populate in a new cell based on the following logic.
-If one or all cells in a given range contain “Yes”, then populate the result cell with “Yes”
-If one or all cells in a given range contain “No”, then populate the result cell with “No”
-If there is a mixture of both “Yes” and “No” in a given range (irrespective of the combination), then populate the result cell with “Ambivalent”

Book2
	A	B	C	D	E
1	Q1a	Q1b	Q1c	Q1d	RESULT
2	Yes
3		Yes
4			Yes
5				Yes
6				No
7			No
8		No
9	No
10	Yes	Yes
11			Yes	Yes
12			No	No
13	No	No
14	Yes		No
15	No		Yes
16		Yes		No
17		No		Yes
18	No	No	No	Yes
19	Yes	Yes	Yes	No
Sheet1

Any easy ways of accomplishing this?

Thanks!

 

[JamesCanale]

MrExcelPlayground19.xlsx
	A	B	C	D	E
4	Q1a	Q1b	Q1c	Q1d	RESULT
5	Yes				Yes
6		Yes			Yes
7			Yes		Yes
8				Yes	Yes
9				No	No
10			No		No
11		No			No
12	No				No
13	Yes	Yes			Yes
14			Yes	Yes	Yes
15			No	No	No
16	No	No			No
17	Yes		No		Ambivalent
18	No		Yes		Ambivalent
19		Yes		No	Ambivalent
20		No		Yes	Ambivalent
21	No	No	No	Yes	Ambivalent
22	Yes	Yes	Yes	No	Ambivalent
Sheet5

Cell Formulas
Range	Formula
E5:E22	E5	=IF(ISNA(MATCH("Yes",A5:D5,0)),"No",IF(ISNA(MATCH("No",A5:D5,0)),"Yes","Ambivalent"))

 

[Joe4]

Try this (for the range A10:D10):

=IF(AND(COUNTIF(A10:D10,"Yes"),COUNTIF(A10:D10,"No")),"Ambivalent",IF(COUNTIF(A10:D10,"Yes"),"Yes",IF(COUNTIF(A10:D10,"No"),"No","")))

 

[marcus314]

You guys are awesome! I'm working through to deconstruct the logic behind the formulas now. Thanks a million!

 

[marcus314]

Hi all,

What if I wanted to simplify the output and simply input "Multi" if there is more than one value in a given range? Below is my expected output:

Book4
	A	B	C	D	E
1	Q1a	Q1b	Q1c	Q1d	RESULT
2	Yes				Yes
3		Yes			Yes
4			Yes		Yes
5				Yes	Yes
6				No	No
7			No		No
8		No			No
9	No				No
10	Yes	Yes			Multi
11			Yes	Yes	Multi
12			No	No	Multi
13	No	No			Multi
14	Yes		No		Multi
15	No		Yes		Multi
16		Yes		No	Multi
17		No		Yes	Multi
18	No	No	No	Yes	Multi
19	Yes	Yes	Yes	No	Multi
Sheet1

Any ideas?

Thanks again!

 

[Joe4]

Hi all,

What if I wanted to simplify the output and simply input "Multi" if there is more than one value in a given range? Below is my expected output:

Book4
	A	B	C	D	E
1	Q1a	Q1b	Q1c	Q1d	RESULT
2	Yes				Yes
3		Yes			Yes
4			Yes		Yes
5				Yes	Yes
6				No	No
7			No		No
8		No			No
9	No				No
10	Yes	Yes			Multi
11			Yes	Yes	Multi
12			No	No	Multi
13	No	No			Multi
14	Yes		No		Multi
15	No		Yes		Multi
16		Yes		No	Multi
17		No		Yes	Multi
18	No	No	No	Yes	Multi
19	Yes	Yes	Yes	No	Multi
Sheet1

Any ideas?

Thanks again!

This should do what you want:

=IF(COUNTA(A10:D10)>1,"Multi",IF(COUNTIF(A10:D10,"Yes"),"Yes",IF(COUNTIF(A10:D10,"No"),"No","")))