How to Prevent & Fix #DIV/0! Errors in Ratios, Margins & Averages

An undefined ratio is not zero

Revenue B2 and gross profit C2 must both be numeric. A zero denominator returns an explicit undefined message. With 180 profit and 450 revenue the ratio is 0.4, or 40% when percentage formatting is applied.

A missing input or a formula returning empty text is not a measured zero. Numeric text is deliberately rejected until cleaned. The raw ratio is retained; display rounding belongs to number formatting.

=IF(COUNT(B2,C2)<>2,"Enter numeric revenue and profit",IF(B2=0,"Undefined: zero revenue",C2/B2))

Count numeric observations, not matching labels

COUNTIF on region labels alone is insufficient to guard AVERAGEIF: a matching region can have no numeric revenue. This example builds D2:D5 as 1 for a matching row with numeric revenue and 0 otherwise. E2:E5 contains the corresponding revenue or zero.

The average divides the sum of E2:E5 by the sum of D2:D5. Real zero revenues count as observations. Missing values and text do not. EMEA starts at (450+550)/2=500. Region matching uses case-insensitive equality, with * and ? treated as literal characters.

Inspect source error cells separately. This numeric-observation policy excludes nonnumeric/error revenue cells and is not a claim that the source data is complete or error-free.

=IF(F2="","Choose a region",IF(SUM(D2:D5)=0,"No numeric revenue",SUM(E2:E5)/SUM(D2:D5)))

Try the missing-data cases

Clear B2 and B4 while keeping their EMEA labels. The result must say No numeric revenue. Then put a genuine zero in B2: the result is zero. Restore 450 and 550 and change the target to AMER to get (0+300)/2=150.

IFERROR can be useful when the intended fallback covers every error it catches. It should not silently convert an undefined margin into a measured zero.

Further reading

• Microsoft — AVERAGEIF function