Excel Tip: Dealing With Rounding Errors | AccountingWEB

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!

Wait, there's more!
There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.
Premium content is currently locked

Editor's Choice

WHAT KIND OF FIRM ARE YOU?
As part of our continued effort to provide valuable resources and insight to our subscribers, we're conducting this brief survey to learn more about your personal experiences in the accounting profession. We will be giving away five $50 Amazon gift cards, and a $250 Amazon gift card to one lucky participant.
This is strictly for internal use and data will not be sold
or shared with any third parties.