Explanation
In this example, the goal is to convert negative numbers in column B to zero and leave positive numbers unchanged. Essentially, we want to force negative numbers to zero.
With the MAX function
The MAX function provides an elegant solution:
=MAX(B5,0)
This formula takes advantage of the fact that the MAX function works fine with small sets of data — even two values. If the value in B5 is a positive number, MAX will return the number as-is, since positive numbers are always greater than zero. However, if the number is B5 is negative, MAX will return zero, since 0 is greater than any negative number.
With the IF function
This problem can also be solved with the IF function like this:
=IF(B5<0,0,B5)
This formula returns exactly the same result. If B5 is less than zero, IF returns zero. Otherwise, IF returns the original number. Both formulas work fine. The MAX formula above is just an elegant way to replace the slightly more complex IF formula. However, if you need to customize the results returned after a logical test , the IF function is more flexible.
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 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)
Video: Boolean algebra in Excel
