Get Excel to Remove The Dreaded #DIV/0 Error

BY STANLEY ZAROWIN

Q. I prepare model Excel spreadsheets for different groups of people who generally are not—how should I say it—financially or spreadsheet nimble. So when they see that dreaded #DIV/0 error they panic, even though I’ve explained to them that all it means is that Excel (or anyone else, for that matter) can’t divide a number by 0 and Excel shows it as an error by displaying #DIV/0. Is there a way to eliminate that error message?

A. Well, you can’t change the fundamental laws of mathematics, but you can program Excel to explain to novice users what they must do to correct the “error.” One easy way is to apply the IF() function so that if someone tries to divide a number by 0, instead of showing the dreaded #DIV/0, the cell’s message will read something like this: “Please enter a divisor other than 0.” The formula to do that is:

IF(DIVISOR = 0, “Please enter a divisor other than 0”, DIVIDEND/DIVISOR)

So, if you’re dividing B2 by C2, the formula would be:

=IF(C2=0,“Please enter a divisor other than 0”,B2/C2)

This is what it looks like in Excel—with and without the IF formula:

Of course, you can tailor the message to say something else.

Shortcuts

A fast way to find a name in your Windows’ address books: Click on Start, Search (in Windows 98, it’s Find ), People to open this window:

Highlight People and enter the name (first, last or both) and your computer will search all the Windows databases for matches. Once the name is found, click on Add to address book and it does just that.

To delete a file without it’s automatically going to the Recycle Bin, hold down the Shift key while pressing Delete.

To return to the place in a document where you left off before saving, open the document and press Shift+F5.

For a full list of all the Word commands, their descriptions and default shortcuts, go to www.mvps.org/word/FAQs/General/CommandsList.htm . The list is downloadable in Excel format.

SPONSORED REPORT

How the election may affect taxation of business income

This report summarizes recent proposals to reform the U.S. business income tax system and considers the path to enactment of any such legislation.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

Did you follow 2016’s biggest accounting news?

CPAs will remember 2016 as a year of new standards and new faces. How well did you follow the biggest accounting events? The 7 questions in this quiz will help you find out