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

Improve this question
  • 1
    Interestingly, =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
38

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)
Improve this answer
  • 1
    Similar to this, =ROUND(A1,0) does the same thing in this context. – dangowans Aug 30 '12 at 18:49
  • Or =CEILING(A1,1) – dangowans Aug 30 '12 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 '12 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
Improve this answer
  • @fixer1234, Does that edit help? – SeanC Feb 25 '15 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. :) – loved.by.Jesus Jan 25 '19 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 '20 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.

Improve this answer
  • This isn't necessarily more readable, but it is "shorter" than the formula in the question. – dangowans Aug 30 '12 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 '12 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 '12 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 '12 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 '20 at 4:23

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.