# 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. – Carl Witthoft Jan 25 '17 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. – dangowans Aug 30 '12 at 18:49
• Or `=CEILING(A1,1)` – dangowans Aug 30 '12 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 '12 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? – SeanC Feb 25 '15 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. :) – loved.by.Jesus Jan 25 '19 at 7:47
• It is strange that double negation became the excel standard for numification. Looks like decrement when taken out of context. – stevesliva Nov 26 '20 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. – dangowans Aug 30 '12 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 '12 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 '12 at 18:50
• Both answers work on the same principle --applying a numerical operation to a Boolean value will return a numerical answer. – Excellll Sep 3 '12 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. – stevesliva Nov 26 '20 at 4:23