by Allen Wyatt
(last updated August 17, 2019)
Lynn uses Excel to keep track of her checkbook. She has the cells in the worksheet formatted to show values like $1,234.56. Sometimes, but not always, if Lynn changes the formatting of a cell it may look like 1234.56000000001. This seems like an error to her and she finds it annoying, so she is wondering why the figures look this way at times.
Lynn has inadvertently discovered the strange, wonderful world of Excel math. You see, when you do math in an Excel formula, because of the way that Excel does the calculations internally, you could easily end up with a rounding error at the most extreme ends of the calculation. (Excel maintains internal precision to 15 significant digits, which is why Lynn is seeing the "error" out at the very furthest reaches of her number.)
In routine calculations—like the ones being done by Lynn—this typically isn't a problem. In her usage, she only cares about numbers being correct to a precision of two decimal places. When the "error" is in the eleventh decimal place, it isn't going to affect the validity of what Lynn sees. It would only affect the results if a particular result relied upon thousands and thousands and thousands of previous calculations. This is the only way that the "error" could compound to the point where it affects Lynn's results.
In day to day usage, this shouldn't be an issue. The "error" is hidden easily by using a cell format such as Currency or Accounting that displays values, so they show only two decimal places.
If the rounding issues bug you a lot, the easiest solution is to force your formulas to round to the number of decimal places you want. For instance, let's say you have a very simple formula such as the following:
You could "wrap" the formula in the ROUND function, in this manner:
This forces the result returned by Excel to two decimal places, which is what Lynn would expect to see if she removes the cell formatting.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13663) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When you store the date and time in a single cell, it can be a bit confusing to count how many cells contain a particular ...Discover More
If you have a list of names in a column, and you want to separate those names into individual cells, there are several ...Discover More
ISBN numbers are used to denote a unique identifier for a published book. If you remove the dashes included in an ISBN, ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.