Convert TRUE or FALSE to 1 or 0

Reading Time: 2 minutes

Basic logical test in Excel

In Excel, any logical test returns TRUE of FALSE.

For instance, to compare the contains of 2 columns, you have to write the following test.


Test to compare the value of 2 columns

In this second example, we want to know if our customer have 21 years-old or more. So we create the following test with an absolute reference to the limit cell.


Age Greater than 21

How to convert the test to numeric value?

So by default, when you create a test in Excel, the result is TRUE or FALSE. And the result is always in the center of the cell, ALWAYS 😉

But, you can convert TRUE or FALSE to 1 or 0 with 2 different method

Multiply by 1

The first method is to multiply the logical test by 1

=(logical test)*1

Convert logical test by multiply by 1

Write 2 dashes

The other technique is to write 2 dashes before the test like in this example.

=--(logical test)

Convert logical test with 2 dashes

And the result is the same ; the test returns 0 or 1.

Related posts

Create test on String Length

Frédéric LE GUEN

Functions AND – OR

Frédéric LE GUEN

Function SUMIF

Frédéric LE GUEN

Leave a Comment