# How do I get “yes” or “no” in Excel if the information comes from “true” and “false”?

first of all, I have to point out that I am a German visiting this forum (I couldn't find anything useful on the German websites), so excuse my English if it is bad here and there ;-)

Here is my "problem":

I have quite an extensive spreadsheet with different formulas. In columns R and S I have a formula that will give me "true" or "false" as a result.

Now, I need an additional formula, which will give me a "yes" or "no" answer if:

• Column R and column S = "true" --> "yes"
• Column R and column S = "false" --> "no"
• Column R unequal column S --> "no"

I am sure there is a formula for this. Can somebody help me? :-/

-

This sounds like a straightforward `AND` condition.

``````=IF(AND(R1,S1),"Yes","No")
``````
-
No - tried that one already :( Excel highlights (R1,S1) if I add the formula... – Guest089 Aug 25 '14 at 14:21
@Guest089 I have no idea what that means. What do you mean "highlights"? How are you using the formula? In a cell? – Excellll Aug 25 '14 at 14:28
@Guest089 Did you try replacing my commas with semicolons? I have different regional settings than you. – Excellll Aug 25 '14 at 14:30
Yes - thanks. That was it :) It worked... – Guest089 Aug 25 '14 at 14:33

If you mean `TRUE`/`FALSE` instead of `"TRUE"`/`"FALSE"` (the former set being Boolean values, the latter being strings), I personally prefer the former, and would keep any outputs to a Boolean format as well wherever possible. It's useful to keep these as Boolean, because it makes writing formulas based on them (and returning them) a lot easier. I'll give solutions for several variations below. Use whatever works for you. Solutions are for Row 2.

Case 1: Values in columns R & S are Boolean, and you want Boolean output.

This is the simplest one to resolve.

``````=AND(R2,S2)
``````

Case 2: Values in columns R & S are Boolean, and you want string output.

Only slightly more complex.

``````=IF(AND(R2,S2),"Yes","No")
``````

Case 3: Values in columns R & S are strings, and you want Boolean output.

This begins to demonstrate the advantage of having Boolean outputs instead of strings.

``````=AND(R2="TRUE",R2=S2)
``````

Case 4: Values in columns R & S are strings, and you want Boolean output.

Finally, the least preferable (IMHO) situation and solution.

``````=IF(AND(R2="TRUE",R2=S2),"Yes","No")
``````

It gets even worse if one is a Boolean and the other is not, but you probably get the idea by now.

Below is a screenshot of all of the above in action. Columns A & B are stand-ins for R & S, where the values are in Boolean format. Columns F & G are stand-ins for R & S, where the values are strings. Column C shows example output for the solution in Case 1 above, D for Case 2, H for Case 3, I for Case 4.

Finally, here's one set of formulas which should cover all true/false input variants.

Boolean output.

``````=AND(UPPER(TEXT(R2,""))="TRUE",UPPER(TEXT(R2,""))=UPPER(TEXT(S2,"")))
``````

String output.

``````=IF(AND(UPPER(TEXT(R2,""))="TRUE",UPPER(TEXT(R2,""))=UPPER(TEXT(S2,""))),"Yes","No")
``````

This should account for all of the following cases:

• Both R & S are Boolean
• Both R & S are string
• One column is Boolean, the other is string
• There is a case mismatch between columns (e.g.: Column R says "TRUE" where column S says "true")
-
I did mean true/ false... My formula in R is =IF(\$O\$4:\$O\$1080>0;TRUE;FALSE) Still, the solutions don't work :( – Guest089 Aug 25 '14 at 14:28
Ok, it looks like this may be in part due to regional variances. Replace commas in my solutions with semicolons. Also, what's the formula for S? – Iszi Aug 25 '14 at 14:32