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

Solution 1:

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)

Solution 2:

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

Solution 3:

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.