Excel Tip: Dealing With Rounding Errors

Have you ever experienced the frustration of displaying a worksheet without full decimal places and having Excel round the numbers for presentation purposes, but if you look closely at the calculations, the displayed numbers don't quite add up?

For example, consider this calculation:

23.49
23.49
46.98

If you display your Excel spreadsheet without decimal places, the calculation is rewritten like this:

23
23
47

The answer is technically correct, but who's going to believe it?

One way in which you can solve this problem is to request that Excel present the worksheet in "Precision as displayed" mode, which means that all numbers will be rounded to the actual number that is displayed. Apply this option by selecting the Tools menu, then choosing Options, Calculation, and checking the "Precision as displayed" box. Click OK to execute this change.

The result will be a calculation that appears thus:

23
23
46

Note that this change will be made with respect to this worksheet only.

View more helpful tips!

You may like these other stories...

By Jason Bramwell When CPA Barry MacQuarrie started the SocialCPAs Social Media Survey in 2010, he said social networking was still relatively new to the accounting profession. The self-proclaimed "social media...
By Jason Bramwell In an effort to address the increased demand for comprehensive and intensive training on eXtensible Business Reporting Language (XBRL), the American Institute of CPAs (AICPA) and XBRL US Inc., the not-...
By Lauren Prosser, Director of ProfitCents Professional Services, Sageworks Staffing is among the top management issues facing CPA firms, according to a recent survey by Sageworks, a financial information company. That...

Already a member? log in here.

Upcoming CPE Webinars

Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 10
Transfer your knowledge and experience to prepare your team for the challenges and opportunities of an accounting career.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.
Sep 18
In this course, Amber Setter will shine the light on different types of leadership behavior- an integral part of everyone's career.