Negative Numbers to Zero

 

A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged.

Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps.

1. Select cell C1.

2. Enter the formula =A1-B1

3. Click the lower right corner of cell C1 and drag the formula down.

Negative Numbers to Zero Example

Result:

Negative Numbers

4. To change the negative numbers to zero but leave the positive numbers unchanged, instead of =A1-B1, use =MAX(0,A1-B1) and drag the function down.

Result:

Negative Numbers to Zero in Excel

Explanation: the MAX function returns the maximum of two values. If the result of the formula is negative, the function returns 0.

Previous articleLocate Maximum Value in Excel
Next articlePercentage Formula in Excel – Easy Calculations