Convert numbers to 1 or 0

In this example, the goal is to convert the numbers in column B to either 1 or 0, depending on whether the number is greater than zero or not. If the number in column B is greater than zero, the result should be 1. If the number in column B is less than or equal to zero, the result to should be zero.

With the IF function

One way to handle this problem is with the IF function.  In the example shown, the formula in D5, copied down, is:

=IF(B5>0,1,0) 

The logic is simple: when the number B5 is greater than zero, IF returns 1, otherwise IF returns 0. The logical test inside IF can be adjusted to apply different logic if needed.

With Boolean logic

Another option for solving this problem is to use Boolean logic directly like this:

=--(B5>0)

Here, the expression B5>0 evaluates to either TRUE or FALSE. The double negative (--) is used to convert the TRUE and FALSE into the numeric equivalents, 1 and 0. This conversion can also be handled with the N function:

=N(B5>0)

Read more about converting TRUE and FALSE values to 1 and 0 in this article.

Video: Boolean algebra in Excel