Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

share|improve this question
up vote 16 down vote accepted

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:

share|improve this answer
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

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:


Note: --True, True+0 and True/1 have the same effect.

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

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


returning the total sales from region that is 9 or lower:

Team    Sales
1       $20
2       $30
11      $90




=1 * $20 + 1 * $30 + 0 * $90
=$20 + $30 + $0
share|improve this answer
Just came across this old Q&A. The -- isn't ringing a bell with me. Could you add a sentence to your answer with an example of how to actually use it? Thanks. – fixer1234 Feb 25 '15 at 20:59
@fixer1234, Does that edit help? – SeanC Feb 25 '15 at 23:44
Thanks. I wasn't familiar with that. Good answer. – fixer1234 Feb 25 '15 at 23:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .