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:
Wemight 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 weare 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, wewould only want those formulas calculated if the clicks are not zero.So in thecalculated cell, start typing theIF 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 thetext in quotation marks - we would enter “-“. If you want the cell left blank, enter two quotation marks: “”.Value_if_false: What do wewant 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!
What’s a Rich Text element?
The rich text element allows you to create and format headings, paragraphs, blockquotes, images, and video all in one place instead of having to add and format them individually. Just double-click and easily create content.
The rich text element allows you to create and format headings, paragraphs, blockquotes, images, and video all in one place instead of having to add and format them individually. Just double-click and easily create content.
Static and dynamic content editing
A rich text element can be used with static or dynamic content. For static content, just drop it into any page and begin editing. For dynamic content, add a rich text field to any collection and then connect a rich text element to that field in the settings panel. Voila!
How to customize formatting for each rich text
Headings, paragraphs, blockquotes, figures, images, and figure captions can all be styled after a class is added to the rich text element using the "When inside of" nested selector system.
