suppose I have a formula in cell C3:=AND(A1=B1)
it will give TRUE:FALSE
but i need Yes:No instead
so how do I format the cell C3 to get so?
Thanks!
Regards,
asked Mar 24, 2015 at 15:13
-
Try writing your formula using If instead of And.– OSol1tair3Mar 24, 2015 at 15:19
-
Hi can it be done by conditional formatting?– Hemant RupaniMar 24, 2015 at 17:22
-
No. Conditional formatting is used to change the format of the cell; fill color, font weight, font color, etc. It is not used to change the value of the cell.– CharlieRBMar 24, 2015 at 17:37
Add a comment
|
5 Answers
Actually, yes, it can be done through conditional formatting... Sort of. Use the screenshots below to setup a conditional format that uses a custom number format for cells that are TRUE. You should be able to extrapolate the setup for False = No.
It's important to note that it will only change the apparent value in the cell and not the actual value. If you try to do other calculations based on this cell, it will use the TRUE/FALSE values and not the YES/NO values. Therefore, this method is only helpful for when you're concerned about what's presented to the user and not what's used in underlying calculations.
See how the value would be still be TRUE but it's displaying "Yes" instead.
answered Mar 24, 2015 at 18:22
-
2You don't actually need it to be "Yes";"Yes";"Yes";"Yes". These are four different formats for positive numbers, negative numbers, zero, and then everything else; so if your value will always be TRUE or FALSE, you could use ;;;"Yes" leaving the first three blank. Mar 24, 2015 at 22:03
-
You would also need to add another conditional format using ;;;"No" when the value evaluates to FALSE, or if you are certain the only values will ever be TRUE or FALSE, just set that as the base formatting for the cells. Mar 24, 2015 at 22:03
-
@JasonClement To your first, I know that. To your second, I mention that in the last sentence of the first paragraph, although I don't explicitly state with the format should be. Mar 24, 2015 at 22:15
-
I was only adding that for the asker's benefit. I intended the second comment to highlight that it didn't require two conditional formats. Mar 24, 2015 at 22:29
-
Let me give my two cents.
Supposing you're just interested in the showing values, you may use the following trick.
In C3 insert the formula:
=AND(A1=B1)+0
Maybe, get rid off the AND part:
=(A1=B1)+0
Now, you can set the custom format for C3 to "Yes";;"No".
The +0 part forces Excel to treat the result as numeric, giving you the option to use the custom format.
Example:
Data Result Result
1 FALSE NO
12 TRUE YES
12 FALSE NO
33
Formula in C3:
=IF(AND(A4=A5),"YES","NO")
Formula in B3:
=AND(A3=A4)
-
-
It isn't clear why the OP inserted the AND function, it serves no purpose here. For your example of how to produce the result, consider leaving it out to demonstrate the proper way to do it. The AND is harmless, but it makes your answer look "wrong", since answers typically contain appropriate syntax to show how the problem can/should be solved. Just a suggestion. Mar 24, 2015 at 17:26
Use =--(A1=B1) instead of =A1=B1. The double negative flips the sign twice, and since it's a mathematical operation, it converts TRUE to 1 and FALSE to 0 (i.e. casts them as integers)
