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

0 votes
asked Aug 24, 2010 by lfurness

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 best? Or does it depend on the circumstances?

if (A1 = 1, true, false)

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

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

3 Answers

0 votes
answered Aug 24, 2010 by littlebobbytables

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.

0 votes
answered Aug 15, 2012 by carl-manaster

Better still would be simply:

=A1=1

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

0 votes
answered Sep 15, 2017 by cj-dennis

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

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...