If the number in column B is greater than zero, IF returns 1, otherwise IF returns 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: 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: 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: Read more about converting TRUE and FALSE values to 1 and 0 in this article. Video: Boolean algebra in Excel
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
