Formula Errors

Prevent #VALUE! and #DIV/0! Errors in an Excel Formula

Khadija KhanKhadija Khan Nov 20, 2026 869 views

Question details

Create a formula that divides a total by a cell value without displaying #VALUE! or #DIV/0! errors.

Prevent #VALUE! and #DIV/0! Errors in an Excel Formula
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In Excel, click an empty cell where you want the result to appear.

2
Enter the formula

Type =IFERROR(SUM(K4:K18)/E19,0) in the cell or formula bar, then press Enter.

3
Adjust the references

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 to replace calculation errors with zero
IFERROR hides other errors too: IFERROR returns the fallback for any error in the calculation, not just #VALUE! or #DIV/0!. Check the source cells if you need to identify the cause.
Calculate without visible formula errors

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. 1. Open your spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Select a result cell: Click an empty cell where you want to display the calculated result.
  3. 3. Enter the formula: Type =IFERROR(SUM(K4:K18)/E19,0) and press Enter.
  4. 4. Customize the calculation: Edit the range, divisor reference, or fallback value in the formula bar to match your worksheet.
Replace division and value errors with a chosen fallbackOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet cells and formulas
microsoft office alternative - wps office

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.