Return Zero or a Blank for Zero Values in Excel Formulas
Question details
Create an Excel formula that either shows 0 instead of a negative result or shows a blank when the referenced value is zero.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user subtracts 25 from the value in cell E9 and needs to control how zero or negative outcomes are displayed.
- Observed behavior
- A standard subtraction formula can return a negative number, while the user may instead want a zero result or a visually empty cell.
Check that E9 contains a numeric value or is intentionally blank. Decide whether users should see 0 as a valid result or whether the cell should appear empty when E9 is zero.
Use MAX to return zero instead of a negative result
Use this formula when the calculation must subtract 25 but must never display a value below zero.
MAX compares the specified values and returns the largest one. By including 0, the formula keeps negative subtraction results at zero.
Click the cell where you want the calculation result to appear.
Type =MAX(0,E9-25) in the formula bar or directly in the selected cell.
Press Enter. If E9 minus 25 is negative, the result displays 0; otherwise, it displays the calculated value.
Use IF to leave the result blank when E9 is zero
Use this formula when a zero value in E9 should produce a visually empty result cell instead of displaying zero.
Control Zero and Negative Results with WPS Spreadsheets Formulas
WPS Spreadsheets supports the same MAX and IF formula logic, so you can prevent negative totals or leave results blank when a source value is zero. Open your workbook in WPS Office, enter the formula, and use the fill handle to apply it to additional rows.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the E9 values.
- 2. Choose the calculation cell: Click the cell where the adjusted result should be displayed.
- 3. Enter the required formula: Enter =MAX(0,E9-25) to cap results at zero, or enter =IF(E9=0,"",E9-25) to show a blank when E9 is zero.
- 4. Apply the formula to other rows: Press Enter, then drag the fill handle from the selected cell down the column to copy the formula to matching rows.
Frequently Asked Questions
Why does =MAX(0,E9-25) return zero?
MAX returns the largest value in its arguments. When E9-25 is negative, 0 is larger, so the formula returns 0.
How can I subtract 25 only when an Excel cell is not zero?
Use =IF(E9=0,"",E9-25). The IF test returns a blank when E9 equals 0 and performs the subtraction for other values.
Will the IF formula show a blank if E9 is empty?
Usually yes, because Excel commonly evaluates an empty referenced cell as zero for the test E9=0. To handle blank cells separately, use =IF(E9="","",E9-25).
How do I copy the MAX or IF formula down a column?
Enter the formula in the first result cell, select it, and drag the small fill handle at the lower-right corner of the cell down to the remaining rows.
Can WPS Spreadsheets open formulas created in Excel?
Yes. WPS Spreadsheets supports common Excel formulas such as MAX and IF and can open and save Microsoft Excel .xlsx and .xls files.
