26

What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}?

Supposing there is one shorter than =IF(cond,1,0).

1
  • 1
    Interestingly, =a1+a2 will return the numeric sum of logicals, but =sum(a1:a2) will not. Excel 2013. Jan 25, 2017 at 19:46

4 Answers 4

39

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)
3
  • 1
    Similar to this, =ROUND(A1,0) does the same thing in this context.
    – dangowans
    Aug 30, 2012 at 18:49
  • Or =CEILING(A1,1)
    – dangowans
    Aug 30, 2012 at 18:51
  • 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"...
    – anol
    Aug 30, 2012 at 18:52
15

-- 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
3
  • @fixer1234, Does that edit help?
    – SeanC
    Feb 25, 2015 at 23:44
  • I had seen on the web the -- method, but I must admit that using the formula INT is 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.
    – stevesliva
    Nov 26, 2020 at 4:25
9

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.

5
  • This isn't necessarily more readable, but it is "shorter" than the formula in the question.
    – dangowans
    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"?
    – anol
    Aug 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!
    – anol
    Aug 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.
    – Excellll
    Sep 3, 2012 at 22:12
  • This is 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, 2020 at 4:23
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.