What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}?
Supposing there is one shorter than =IF(cond,1,0).
4 Answers
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)
-
1Similar to this,
=ROUND(A1,0)does the same thing in this context. Aug 30, 2012 at 18:49 -
-
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"...– anolAug 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
-
-
I had seen on the web the
--method, but I must admit that using the formulaINTis 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.
answered Aug 30, 2012 at 18:32
-
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"?– anolAug 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!– anolAug 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.– ExcellllSep 3, 2012 at 22:12
-
This is
numificationof a sort. In Perl,+0is the standard numification operator, but I like the use of*1for 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 ""
=a1+a2will return the numeric sum of logicals, but=sum(a1:a2)will not. Excel 2013.