Quick Answer & Formula
Excel
Sheets
Beginner
=IFERROR((New - Old) / Old, 0) | =IF(Denominator=0, 0, Numerator/Denominator) The #DIV/0! error occurs when a formula attempts to divide by zero or by an empty blank cell. Fix by testing if the denominator is 0 with IF, or wrapping the formula in IFERROR.
How to Fix the #DIV/0! Error in Excel & Google Sheets
In mathematics and computer science, dividing any number by zero is undefined. When a spreadsheet encounters a divisor of 0 or an empty cell, it returns #DIV/0!.
🔍 The 2 Best Fixes for #DIV/0!
Method 1: The Explicit IF Check (Best Practice)
=IF(Old_Value = 0, 0, (New_Value - Old_Value) / Old_Value)Method 2: The Fast IFERROR Wrapper
=IFERROR((B2 - A2) / A2, 0)Or to return a readable message:
=IFERROR(A2 / B2, "No Data")Method 3: AVERAGEIFS #DIV/0! Handling
When AVERAGEIFS finds zero matching rows, it returns #DIV/0!. Prevent this with:
=IFERROR(AVERAGEIFS(Sales, Region, "West"), 0)
?
Frequently Asked Questions
Is it better to use IF(B2=0, 0, A2/B2) or IFERROR(A2/B2, 0)?
Using IF(B2=0, 0, A2/B2) is slightly faster and more explicit because it only targets the zero divisor without accidentally hiding other potential formula syntax errors.