Convert Numbers into True and False in Excel

Add to Favorites

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:

280dcd8c45a9ea31889b430a6016f120.png

Enter the logic formula:

=A1=0

5223ef23651b8e24baaee7b03d13779a.png

Result:

1805c4067d7e0aa5edc927d653ecbffb.png

Copy the formula down:

fab6e79624dc76c8e0f1244bffde920e.png

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

=A1=1

edd160a8e41e872876de8a5e1949e914.png

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.

a219eaac6b993e932a340be2b7eb0f33.png

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.


Excel Function: FALSE(), TRUE()
Downloadable Files: Excel File

Similar Content on TeachExcel
Multiply by True and False in Excel
Tutorial: You can multiply numbers by TRUE and FALSE in Excel. This effectively allows you to elimi...
Count Cells Containing TRUE or FALSE in Excel
Tutorial: Count the number of cells that contain TRUE, FALSE, or one of the two. This lets you count...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Quickly Convert Formulas into Their Output Values in Excel
Tutorial: This tutorial teaches you how to convert a formula or function into its displayed output ...
Introducing Logic into Formulas and Functions in Excel
Tutorial: In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a ...
NOT Function - Change False to True and True to False in Excel
Tutorial: Change True to False and False to True with this simple function in Excel. To do this, we ...
Tutorial Details
Excel Function: FALSE(), TRUE()
Downloadable Files: Excel File
Similar Content
Multiply by True and False in Excel
Tutorial: You can multiply numbers by TRUE and FALSE in Excel. This effectively allows you to elimi...
Count Cells Containing TRUE or FALSE in Excel
Tutorial: Count the number of cells that contain TRUE, FALSE, or one of the two. This lets you count...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Excel Forum