When your Excel formula results turn out to be that ugly divide by zero error **#DIV/0!**, customize the way they are displayed using the IF function. The Productivity Portfolio blog explains how to test to see if your divisor is 0, and if so, display a blank value using something like **=IF(D2=0,'',C2/D2)**. If you're an Excel nerd who loves in-cell logical tests as much as I do, you'll also like conditional cell formatting.

Advertisement

Excel Dividing by Zero [Productivity Portfolio]

## DISCUSSION

The =0 part is unnecessary since Excel treats FALSE as 0. Therefore you can just say [ =if(D2,C2/B2,"") ].

If you know that the value returned will not be 0, then you can also use [ =if(D2,C2/B2,0) ] and then set the format to [ 0;0;;@ ] which will hide the zero if it returns it.

To test if the cell is blank, do not use isblank(). Instead, put the cursor in cell E2 and goto [ Insert -> Name -> Define ]. For the name, use [ blankTest ]. For the refers to, type [ =IF(OR(ISBLANK(D2),D2=""),TRUE,FALSE) ].

Now, any cell where you type [ =blankTest ] will return true if the cell directly above it is blank or contains an empty string ("") and false otherwise.