What should I use for boolean values in formulas, true, "true" or true()

When writing an Excel formula, does it make a difference whether you set a value to true, "true", or true()?

In other words, which of the following is the be开发者_Go百科st? Or does it depend on the circumstances?

if (A1 = 1, true, false)

if (A1 = 1, "true", "false")

if (A1 = 1, true(), false())

---

I would strongly suggest avoiding the second method, as you're using a string literal. If you us the second method in a cell, then need to refer to the second cell's value later, you won't be able to use TRUE or TRUE() to compare it against.

As far as I'm aware, first or third method really doesn't make much of a difference. TRUE() is simply an Excel function that returns TRUE.

---

Better still would be simply:

=A1=1

This puts TRUE or FALSE into the cell with less complexity.

---

Try this little experiment. Copy the following formulae into Excel:

    A       B           C           D
1   TRUE    =NOT(A1)    =A1=TRUE    =A1=FALSE
2   FALSE   =NOT(A2)    =A2=TRUE    =A2=FALSE
3   'true   =NOT(A3)    =A3=TRUE    =A3=FALSE
4   'false  =NOT(A4)    =A4=TRUE    =A4=FALSE

I think you'll be surprised at the results:

    A       B       C           D
1   TRUE    FALSE   TRUE    FALSE
2   FALSE   TRUE    FALSE   TRUE
3   true    FALSE   FALSE   FALSE
4   false   TRUE    FALSE   FALSE

NOT() treats the string values 'true and 'false as if they're the boolean values TRUE and FALSE. However, = treats the string and boolean values as different.

For consistency, you should use the boolean literals, not the string values (option 1), otherwise your results could be unexpectedly incorrect at times (option 2). There's not much point to using the formulae that just return the literal values (option 3).