How to Use Excel Formula to Return Zero or Difference Between Two Cells
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.

- 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.
Identify the two cells you want to compare and ensure they are formatted as Numbers and contain numeric values without hidden text characters.
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.
Click on the blank cell where you want the final calculated result to appear.
Type `=MAX(0, A1-B1)` into the formula bar at the top of the worksheet.
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 IF Function for Logical Comparisons
If you prefer standard logical statements or need to add more complex conditions later, the IF function provides a classic approach.
Use Boolean Multiplication
You can multiply the difference by a logical test to achieve the same zero-capping result without utilizing IF or MAX functions.
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. Open your dataset: Launch WPS Spreadsheet and open your existing workbook.
- 2. Access the Formula tool: Navigate to the Formulas tab on the top ribbon to utilize the Insert Function tool if needed.
- 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.

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.




