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 communityYou 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.