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

Question

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? :-/

Answer 1

This sounds like a straightforward AND condition.

=IF(AND(R1,S1),"Yes","No")

Answer 2

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")