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? :-/
2 Answers
This sounds like a straightforward AND condition.
=IF(AND(R1,S1),"Yes","No") 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")
