A #DIV/0! error occurs when you have a formula that involves a zero as the divisor. You can’t divide by zero, so excel shoots back an error. Now your spreadsheet looks messy – fight back with the Excel If Function.
The #DIV/0! error can be a frustrating occurrence, especially if the cell is only a zero temporarily. Example – a CPC calculation when the ad hasn’t received any clicks yet, or CPA without any conversions:
Perhaps this doesn’t bother you – you know why the error is there. It’s not due to an actual error on your part, and the error will go away once the campaigns get clicks and conversions. But if not everyone understands that, and even if they understand it, they may just not want to see it.
Case in point: this is part of an actual email thread I had with a relative, who wanted help putting together a weight loss tracking spreadsheet for their office:
We might not mind the error, but clearly some people will. And if that person is a client, we definitely want to keep them happy. So what can we do?
We could remove the formula and keep the calculated cell temporarily empty. But that requires us to insert the formula at some point, which might get forgotten about or overlooked in a large table. And if we are just setting up the table for someone else, they may be less comfortable with inputting formulas, or not know how to.
Solution? The Excel IF Function.
The Excel IF function asks that if a certain criterion is met, then a designated action is taken. If that criterion is not met, then a second designated action is taken instead.
For the ad campaign example given earlier, when the clicks are 0, we get a #DIV/0! error in the CPC column. To prevent this, we would only want those formulas calculated if the clicks are not zero.
So in the calculated cell, start typing the IF formula:
The IF function is made up of three parts:
Logical_test: The condition we want met, which will determine which action is taken. To calculate CPC, we want to determine if the value of the clicks is equal to zero. The logical_test would be C6=0.
Value_if_true: What do we want to happen if the logical_test conditions are met? If the clicks are zero, we don’t want the CPC calculated. Instead, determine what you would like displayed instead. We choose to display a dash (-). Enter the text in quotation marks – we would enter “-“. If you want the cell left blank, enter two quotation marks: “”.
Value_if_false: What do we want to happen if the logical_Test conditions are met? In this case, if the clicks don’t equal zero, we want the CPC formula. We would therefore enter the original calculation, B6/C6.
Our total formula should look like this:
Apply the formula to the rest of the column. If applied correctly, the other calculated cells should not change:
And there we go! Now you have a way to keep your spreadsheets neat and free of errors. There are a lot of ways to use the IF function – how would you use them? Tell us in the comments below!