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 at 7:47

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