What is the Excel formula to convert boolean values {FALSE, TRUE}
into {0, 1}
?
Supposing there is one shorter than =IF(cond,1,0)
.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityWhat is the Excel formula to convert boolean values {FALSE, TRUE}
into {0, 1}
?
Supposing there is one shorter than =IF(cond,1,0)
.
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)
=ROUND(A1,0)
does the same thing in this context.
Aug 30, 2012 at 18:49
--
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
--
method, but I must admit that using the formula INT
is far more clear and elegant. :)
Jan 25, 2019 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.
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 ""
=a1+a2
will return the numeric sum of logicals, but=sum(a1:a2)
will not. Excel 2013.