Excel 2013 formula problems

quickbeam

Has anybody else noticed a problem with Excel calucations in Excel 2013? When I have decimals in a cell, and make a simple calculation then the answer appears with a dozen or so extra numbers after the decimal. For example:

A1 100.3
A2 100
A3 =A1-A2

Answer on A3 appears to be 0.3. That's fine, but if you increase the number of decimal places showing, or Paste Values, you see that the answer is actually 0.299999999999997.

It might be only a tiny fraction out, but this can screw up if things later on, for example I'm using conditional formatting to highlight when two answers that should be the same are incorrect, and this slight fractional difference is causing the conditional formatting to show them as different answers.

Is this a bug? Is there any fix? Do I have to resort to using the Rounding function - that would be a pain in the ar$e if I had to do that.

homer911

Its to do with the way numbers have to be stored in binary - its not caused by Microsoft

https://support.microsoft.com/en-gb/kb/214118

quickbeam

All my years using Excel and I never noticed it before. So, I guess rounding function is the only workaround?

bonzodog2

If you're trying to check if one cell equals another, try instead checking if the difference if less than some tiny number.