# Excel formula to convert boolean values into {0, 1}

What is the Excel formula to convert boolean values `{FALSE, TRUE}` into `{0, 1}`?

Supposing there is one shorter than `=IF(cond,1,0)`.

• Interestingly, `=a1+a2` will return the numeric sum of logicals, but `=sum(a1:a2)` will not. Excel 2013. Jan 25, 2017 at 19:46

You could do it by casting. The "int" function rounds to the nearest integer. If the boolean value is in A1, the formula would be:

``````=INT(A1)
``````
• Similar to this, `=ROUND(A1,0)` does the same thing in this context. Aug 30, 2012 at 18:49
• Or `=CEILING(A1,1)` Aug 30, 2012 at 18:51
• Thanks, the cast was the "standard" way I was looking for. It didn't occur me to try that, I was actually expecting it to be "harder"...
– anol
Aug 30, 2012 at 18:52

`--` is the most common way to convert boolean into int - that's why you see functions that have the `--` in them for this very reason. it will turn an array of {TRUE,FALSE,FALSE} into {1,0,0} which can them be used to multiply other arrays

Example:

returning the total sales from region that is 9 or lower:

```Team    Sales
1       \$20
2       \$30
11      \$90
```

formula:

``````=SUMPRODUCT(--(A2:A4<=9),B2:B4)
``````

Calculation

``````=SUMPRODUCT(--(True,True,False),(\$20,\$30,\$90))
=SUMPRODUCT((1,1,0),(\$20,\$30,\$90))
=1 * \$20 + 1 * \$30 + 0 * \$90
=\$20 + \$30 + \$0
=\$50
``````
• @fixer1234, Does that edit help? Feb 25, 2015 at 23:44
• I had seen on the web the `--` method, but I must admit that using the formula `INT` is far more clear and elegant. :) Jan 25, 2019 at 7:47
• It is strange that double negation became the excel standard for numification. Looks like decrement when taken out of context. Nov 26, 2020 at 4:25

Multiply it by '1'. ex. True * 1 = 1 and False * 1 = 0.

For example, if cell A1 contains the boolean value, in a neighbouring cell, enter the formula:

``````=A1*1
``````

Note: --True, True+0 and True/1 have the same effect.

• This isn't necessarily more readable, but it is "shorter" than the formula in the question. Aug 30, 2012 at 18:42
• That's nice. But does that mean there's no built-in formula for the conversion? Something like "BOOLVAL" or "BOOLINT"?
– anol
Aug 30, 2012 at 18:44
• That's quite useful, but I was looking for a more "proper" solution such as @dangowans's, so I'll accept his solution. Thanks anyway!
– anol
Aug 30, 2012 at 18:50
• Both answers work on the same principle --applying a numerical operation to a Boolean value will return a numerical answer. Sep 3, 2012 at 22:12
• This is `numification` of a sort. In Perl, `+0` is the standard numification operator, but I like the use of `*1` for making bools into bits. Nov 26, 2020 at 4:23

Since Excel 2007, there is the N() function that converts anything that's not a number or date into 0 or 1.

`=N(TRUE)` returns `1`

`=N(FALSE)` returns `0`

`=N("Text")` returns `0`

`=N("30")` returns `0`

`=N(30)` returns `30`

There's one for text as well, which returns an empty string if parameter is not a text.

`=T("Text")` returns `"Text"`

`=T(3104)` returns `""`