Prevent #VALUE! and #DIV/0! Errors in an Excel Formula
Question details
Create a formula that divides a total by a cell value without displaying #VALUE! or #DIV/0! errors.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculate the sum of a range divided by a value in another cell.
- Observed behavior
- The formula should display a fallback value instead of an error when the calculation fails.
Identify the range to total, the cell containing the divisor, and an empty cell for the result. In this example, the range is K4:K18 and the divisor is E19.
Use IFERROR to replace calculation errors with zero
Use this formula when you want the worksheet to display zero if the division produces an error.
In Excel, click an empty cell where you want the result to appear.
Type =IFERROR(SUM(K4:K18)/E19,0) in the cell or formula bar, then press Enter.
Edit K4:K18 to match your total range and E19 to match your divisor cell. Replace the final 0 if you prefer a different fallback value.

Use IFERROR in WPS Spreadsheet
In WPS Spreadsheet, wrap the sum and division in IFERROR to display a fallback value when the calculation returns an error.
- 1. Open your spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
- 2. Select a result cell: Click an empty cell where you want to display the calculated result.
- 3. Enter the formula: Type =IFERROR(SUM(K4:K18)/E19,0) and press Enter.
- 4. Customize the calculation: Edit the range, divisor reference, or fallback value in the formula bar to match your worksheet.

Frequently Asked Questions
Why does dividing by E19 return #DIV/0!?
The error appears when the divisor is zero or blank. Check E19, or use =IFERROR(SUM(K4:K18)/E19,0) to display zero instead.
Can I display a blank instead of zero?
Yes. Enter =IFERROR(SUM(K4:K18)/E19,"") in the result cell to display a blank when the calculation returns an error.
Will IFERROR hide errors other than #VALUE! and #DIV/0!?
Yes. IFERROR replaces any error returned by the calculation. Remove IFERROR temporarily or inspect the referenced cells if you need to diagnose an error.
What if Excel does not accept commas in the formula?
Some regional settings use semicolons as argument separators. Try =IFERROR(SUM(K4:K18)/E19;0) in the result cell.




