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

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

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

News quiz: Taking an economic snapshot and looking to the future

Recent news included IRS actions that affect individuals and partnerships and a possibly influential move by a Big Four accounting firm.Take this short quiz to see how much you know about the news.