Exceljet

Quick, clean, and to the point

Convert numbers to 1 or 0

Excel formula: Convert numbers to 1 or 0
Generic formula 
=IF(A1>0,1,0)
Summary 

To convert numbers to 1 or zero (0), you can use the IF function. In the example shown, the formula in D5, copied down, is:

=IF(B5>0,1,0)

If the number in column B is greater than zero, IF returns 1, otherwise IF returns zero.

Explanation 

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

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.