Sean Killeen bio photo

Sean Killeen

Just a guy trying to get better at writing bios.

Email Twitter Facebook Google+ LinkedIn Instagram Github Stackoverflow Foursquare
Edit this page | Issue? Question?

Problem

If you ever receive a #DIV/0 error in Excel that you expect to be there (i.e. calculating percentages from other data which may not have accumulated yet), you can suppress it by using an IF statement in the formula.

Let’s say you have three columns – A, B, and C. C calculates the percentage of B and A (i.e B/A), but if A is 0 or empty, you’ll get the DIV/0 error which throws off formulas, coloring, highlighting, etc.

Solution

Instead of = B1/A1, do something along the lines of the following:

=IF(A1=0, 0, B1/A1) or:

=IF(A1=0, "", B1/A1) 

This does a simple check first: If A is zero, it will substitute a default value. Otherwise, your calculation will go on as planned.

I’ve used this tip many a time to help with the presentation of an Excel sheet.