Formula Errors

How to Use Excel Formula to Return Zero or Difference Between Two Cells

Bushra ParveenBushra Parveen Nov 11, 2026 870 views

Question details

The user wants to calculate the difference between two cells (A1 and B1) but cap the minimum result at zero instead of showing negative numbers.

How to Return Zero or the Difference Between Two Cells in Excel
Product
Excel
Device & OS
not provided
Scenario
Calculating variances, inventory shortages, or differences where negative results should be displayed as zero rather than a negative integer.
Observed behavior
Needs a formula that returns 0 if B1 is greater than A1, and returns the result of A1 minus B1 if A1 is greater than or equal to B1.
Before you start

Identify the two cells you want to compare and ensure they are formatted as Numbers and contain numeric values without hidden text characters.

Solution 1Recommended

Use the MAX Function to Return Zero or the Positive Difference

The MAX function is the most efficient way to return 0 instead of a negative difference without writing a complex IF statement.

The MAX function compares a set of values and returns the largest one. By comparing your subtraction calculation against zero, the spreadsheet will automatically output 0 whenever the subtraction yields a negative number.

1
Select the target cell

Click on the blank cell where you want the final calculated result to appear.

2
Enter the MAX formula

Type `=MAX(0, A1-B1)` into the formula bar at the top of the worksheet.

3
Calculate the result

Press the Enter key. If B1 is larger than A1, the formula outputs 0. If A1 is larger or equal, it outputs the positive difference.

Use the MAX Function to Return Zero or the Positive Difference
Efficiency Tip: This method is highly recommended over the IF function because it requires less typing and processes significantly faster in large datasets.
Efficient Spreadsheet Calculations

Calculate Differences Easily with WPS Spreadsheet

WPS Office provides a powerful, free Spreadsheet tool that fully supports advanced formulas like IF, MAX, and Boolean logic to help you analyze data effortlessly.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open your existing workbook.
  2. 2. Access the Formula tool: Navigate to the Formulas tab on the top ribbon to utilize the Insert Function tool if needed.
  3. 3. Apply your calculation: Type `=MAX(0, A1-B1)` into any cell just as you would in Excel to return zero or the difference seamlessly.
Fully compatible with Microsoft Excel formulas, functions, and .xlsx formatsLightweight software that processes complex logic and large datasets quicklyBuilt-in function wizard to help you build and troubleshoot formulas without errorsCompletely free to use across Windows, Mac, Linux, iOS, and Android
microsoft office alternative - wps office

Frequently Asked Questions

Can I apply this formula to an entire column at once?

Yes, once you enter the formula like `=MAX(0, A1-B1)` in the first row, you can click and drag the small square fill handle at the bottom-right corner of the cell downward to apply the formula to the rest of your column.

Why am I getting a #VALUE! error when using the formula?

This error usually occurs if one of the referenced cells (A1 or B1) contains text, spaces, or unrecognized characters instead of a number. Ensure both cells are formatted as Numbers and contain valid numeric data.

How do I return a blank cell instead of a zero?

You can modify the IF formula to output an empty string. Use `=IF(B1>A1, "", A1-B1)` and Excel will leave the cell visibly blank when B1 is greater than A1.