Excel changes a 3 decimal number to full number despite formatting options and even in a formula

1
votes

A colleague of mine sent me their Excel sheet and asked me to take a look at it. The issue is that with a very specific number (56136.598), Excel is automatically extrapolating that number out to 10 decimal places completely regardless of the formatting options.

The cell displays the number to the correct 3 decimal places, but if you look at the number in the formula bar it displays all 10 decimal places. It even changes the number to 10 decimal places if I write the formula =round(56136.598,3) to =round(56136.5979999999,3).

Unfortunately, given the industry I am in, I need some explanation as to why this very specific number induces this change. It's not enough to just use a round or trunc function to lop it off at 3 decimal places, the fact that this number and this cell have a different set up then the rest of the parallel cell calculations is drawing some criticism. Has anyone ran into this before? I have tried it in Excel 2010 and 2019 and in new worksheets, same issue. It seems that excel refuses to accept the number at 3 decimal places and forcing an expansion to 10 decimal places on its own.

1
excelvbarounding
This sounds like a Floating Point Error. - Chronocidal
If you are using VBA, you do have the option of using decimal numbers to avoid rounding errors - plz see this recent post stackoverflow.com/questions/60558352/… for an example. - Tom Sharpe

1 Answers

2
votes

This is a normal behavior. See the image below where I just entered 56136,598 into the cell.

enter image description here

This happens due to the fact that Excel is a numeric calculation program and not an algebraic one. So it is a problem of precision. Also see Numeric precision in Microsoft Excel.

Excels results are not absolute but very close to correct. The difference between these to numbers is almost 0 (the difference is 0,0000000001).

And this is actually how most common calculators will act too (you just don't see that). It is just the nature of how calculators (and computers) work.

So there is nothing to worry about.

More about this: Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”