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)
--
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. :)
– loved.by.Jesus
Jan 25 '19 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.
– stevesliva
Nov 26 '20 at 4:23
=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