Convert Negative Numbers to Zero in Excel

by

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.

Worked Example:   Calculate total hours that fall between two times in Excel

1. Select cell C1.

2. Enter the formula =A1-B1

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

Worked Example:   Maximum value if in Excel

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.

Worked Example:   nth largest value in Excel

Result:


Result:

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

Leave a Reply

Your email address will not be published. Required fields are marked *