Convert Numbers into True and False in Excel

How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very important when working with more complex formulas, especially arrays.

Sections:

Convert Numbers into TRUE and FALSE

Real World Example

More Examples (Easier)

Notes

Convert Numbers into TRUE and FALSE

For the numbers, I will use 1 and 0, but it could be any series of numbers.

We use a simple logic formula to test if the value is a 1 or a 0 and this logic formula is what will output TRUE or FALSE.

Sample Data:

Enter the logic formula:

=A1=0

Result:

Copy the formula down:

If you want the 1 to evaluate to TRUE and the 0 to FALSE, just change the formula to this:

=A1=1

Now, I used 1 and 0 for the numbers but it could have been a situation where I had numbers like this: 0,1,2,3,0,1,2,3 and in that case I might want only the 0 values to evaluate to TRUE and the others to evaluate to FALSE. This occurs often with using the MOD() and ROW() function to do something at a set interval of rows in Excel; this is particularly true for the real world example in the next section and the additional links in the section after that.

Real World Example

The above example seems pretty useless and that's because this technique is often buried deep within a complex formula and the result of this technique is never directly seen by the user.

Usually, an array formula would use this technique.

Example:

=SUM(A1:A6*(MOD(ROW(A1:A6),2)=0))

This is an Array Formula and so you must enter it using Ctrl + Shift + Enter.

This will sum values that occur every so many rows in a list; so, it could add together the values from every second row, or third row, or 5th row, etc.

Here is a screenshot of it from the Sum Values from Every X Number of Rows in Excel tutorial.

MOD(ROW(A1:A6),2) is what returns the integer (number) that we can then use to get a TRUE and FALSE value.

=0 is added to the end of the previous piece of code and that is what causes the logical comparison to happen that then results in a TRUE or FALSE value being output.

The array formula that contains all of this then uses the TRUE and FALSE values to check which values it should sum. It will only sum the TRUE values.

More Examples (Easier)

The real world example that I used above is kind of complex so I wanted to include some of our other tutorials that use this same feature but that are not array formulas and are much easier to understand.

Increment a Value Every X Number of Rows in Excel

Automatically Shade Every Other Row When You Add Data in Excel

Shade Every Other Row in Excel Quickly

Notes

This little trick is something that may not seem important but it's easy to remember, just a logical comparison, and it will come in handy when you start to use more complex functions and also array formulas in Excel, I promise.

Make sure to download the sample file for this tutorial so you can work with this example in Excel.