Strange Value Results in a Cell

Written by Allen Wyatt (last updated February 8, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365


Lynn uses Excel to keep track of her checkbook. He 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 him and he finds it annoying, so he 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:

=A1-B1

You could "wrap" the formula in the ROUND function, in this manner:

=ROUND(A1-B1,2)

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, 2021, 2024, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Keeping a Picture Title with the Picture

Pictures and their titles go together like peanut butter and jelly. (Wow, did I just say that?) Seriously, pictures and ...

Discover More

Sequentially Numbering Footnotes

If you have a document with lots of footnotes, you might notice that some of those footnotes are not in numerical order, ...

Discover More

Entering Tabs in a Table

When you press the Tab key while entering info into a table, Word dutifully moves to the next table cell. If you don't ...

Discover More

Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!

More ExcelTips (ribbon)

Relative References to Cells in Other Workbooks

When you construct a formula and click on a cell in a different workbook, an absolute reference to that cell is placed in ...

Discover More

Ignoring N/A Values in a Sum

You can use some of Excel's worksheet functions across a range or worksheets, but not all of them. One that has problems ...

Discover More

Generating Random Strings

Do you need to generate strings of random characters? The ideas presented in this tip will help you do it in a hurry.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 - 6?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.