TRUE and FALSE are Excel’s Boolean values — the result of any test you write. =1=1 returns TRUE, =1=2 returns FALSE, and every comparison operator (>, <, >=, <=, =, <>) produces one or the other.
You can also type them straight into a cell, or use the =TRUE() and =FALSE() functions, which take no arguments and exist mainly for compatibility with older spreadsheet software. Underneath, TRUE behaves as 1 and FALSE as 0, which is what makes them useful in calculations: =SUM((A1:A10>50)*1) counts how many values exceed 50.
To convert them explicitly, use the N function or a double minus — =N(A1) and =--A1 both turn TRUE into 1. The double minus is the one you’ll see inside SUMPRODUCT formulas for exactly this reason.
One thing to watch: SUM and COUNT ignore Boolean values sitting in cells, so =SUM(A1:A10) over a column of TRUEs returns 0 — you have to multiply or coerce them first.
TRUE and FALSE are the Boolean values. Both values represent a result of a condition. For example, 1 = 1 is TRUE, and 1 = 2 is FALSE. In both cases, we have a Boolean value which helps you understand the result. These are also called truth values.
In Excel, there are two ways to use these Boolean values.
- You enter them in a cell.
- You get them in return as a result.
In most cases (while working in Excel), you do not need to enter them in a cell manually. And there are further multiple ways that you can use to get TRUE and FALSE in a cell as a result.
TRUE and FALSE are equivalent to 1 and 0, respectively.
Use TRUE and FALSE Functions
You have two specific functions in Excel, TRUE, and FALSE, to get both Boolean values.
In these functions, you don’t need to specify any argument within the function.
=TRUE() =FALSE()[thrive_leads id=’112075′]
Get the Result by Testing a Condition
When you test a condition, the result is always in TURE or FALSE. And these are the situations where you deal with TRUE and FALSE a lot in Excel. If you test 1 = 1, your formula returns TRUE.
But if you test 1 =2, your formula returns FALSE.
TRUE and FALSE are the basics to express the result of a condition in Excel. Even though the IF function in Excel also works with TRUE and FALSE.
The entire work in the IF function is based on the TRUE and FALSE values.
Using Logical Operators
In Excel, you have logical operators, which help you get test conditions. These logical operators also return results in TRUE and FALSE values.
- Greater Than <
- Lower Than >
- Doesn’t Equal <>
Even when testing test values with each other, we get the result in TRUE or FALSE.
Converting TRUE-FALSE as 1-0
There are two ways to convert the Boolean values into 0 and 1.
- N Function
- Double Minus Sign
In the above example, we first used the function N and then the double minus sign (–), and both converted TRUE and FALSE into 1 and 0.
Out of both methods, using a double minus sign has little benefit. For example, if you want to count the cells with the TRUE, you can use it along with the SUM.
I have used excel for so long and didn’t know this. Thanks. I will spend at least 10 minutes ea day on excel formula brush-up.