Checking A Bespoke Discrepancy Between Cells

[Atlas87]

OK - Not sure if that title even explains it properly...

I have a questionnaire which asks several questions about a certain topic, and then later on a "double check" single question and I want to set up a formula to check if the answers in the first section are in line with what they answer in the double check question.

For example, imagine the 3 first questions are;
1. Did you bite into the apple?
2. Did you chew the apple?
3. Did you swallow the apple?

And then later, you ask
Did you eat any apple at all today?

If they answered YES/YES/NO, to the first three but then NO to the double check you know they are mistaken.

The answers to the first set are being coded, for example, into 1 for Yes and 0 for No.

Can I say, "IF any of these cells contain anything but NO but they have later answered NO" ... flag it.

 

[Yongle]

If using 1s and 0s for all questions including check question (Q6) try..

=IF(Q6=1,Q1+Q2+Q3>=1,Q1+Q2+Q3=0)

Wrap in another IF for flag

=IF(IF(Q6=1,Q1+Q2+Q3>=1,Q1+Q2+Q3=0),"","FLAG")

 

[Scott Huish]

=IF(SUM(Q1:Q4),IF(OR(Q1:Q3)*Q4,"","FLAG"),"")

I think if you answered NO to the first 3 questions, but YES to the last one it should also return FLAG. I think my formula acknowledges both scenarios.

Excel 2010
	P	Q
1	1. Did you bite into the apple?	1
2	2. Did you chew the apple?	1
3	3. Did you swallow the apple?	0
4	4. Did you eat any apple at all today?	0
5		FLAG
Sheet1

Cell Formulas
Range	Formula
Q5	=IF(SUM(Q1:Q4),IF(OR(Q1:Q3)*Q4,"","FLAG"),"")

Excel 2010
	P	Q
1	1. Did you bite into the apple?	0
2	2. Did you chew the apple?	0
3	3. Did you swallow the apple?	0
4	4. Did you eat any apple at all today?	1
5		FLAG
Sheet1

Cell Formulas
Range	Formula
Q5	=IF(SUM(Q1:Q4),IF(OR(Q1:Q3)*Q4,"","FLAG"),"")

 

[Atlas87]

THANKS FOR THIS - I HAVE A QUESTION ABOUT IT THOUGH?

Why would SUM be involved? Is this not saying causing your first table to add up to 2, but the check question will only be 0 for NO or 1 for YES??

 

[Scott Huish]

Numerically speaking 0 equates to FALSE, and any non-zero number equates to TRUE. So SUM is involved to see if any questions have been answered as 1. 1,2,3,4 would all be considered TRUE because they are non-zero numbers.

I suppose you could use OR if you'd rather:
=IF(OR(Q1:Q4),IF(OR(Q1:Q3)*Q4,"","FLAG"),"")