3

In a new worksheet (I'm using Excel 2013), starting in the top left, I type:

A B C
=18.6 + 67.2 =A1-85.8 = 0 =(A1-85.8) * 1000000000000

Expectation

A B C
85.8 TRUE 0

Actual Result

A B C
85.8 FALSE 0.014210855

I'm well aware that floating-point numbers can behave like this, but I'm still surprised at it happening with such simple operations on numbers each having a low number of significant figures and similar orders of magnitude.

Can anyone explain why this happens and why it only happens with some pairs of values (and always non-integers)?

A number of rows filled like this (and repeated use of F9):

A B C D E
=RANDBETWEEN(10, 1000)/10 =RANDBETWEEN(10, 1000)/10 =A1 + B1 =C1-ROUND(C1,1)=0 =(C1-ROUND(C1,1)) * 1000000000000

helps demonstrate the apparent random nature of this phenomenon.

phuclv
  • 26,555
  • 15
  • 113
  • 235
Bob Sammers
  • 206
  • 2
  • 8

1 Answers1

4

We can see this strange affect in action (what Excel is thinking it should be) by updating your B column from =A1-85.8 = 0 to =A1-85.8

So, use the ROUND to correct it

EG

=ROUND(A1-85.8, 0) = 0

To explain about why please view this Source which explains it all. (too much to copy)

Also, from the same link as above

In some cases, you may be able to prevent rounding errors from affecting your work by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, follow these steps:

In Excel 2003 and in earlier versions, click Options on the Tools menu. On the Calculation tab, click to select the Precision as displayed check box.

In Excel 2007, click the Microsoft Office Button, click Excel Options, and then click the Advancedcategory. In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.

In Excel 2013 and 2010, click File, and then Options, and then click the Advanced category. In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.

Community
  • 1
Dave
  • 25,297
  • 10
  • 57
  • 69
  • Thanks for the link. Actually rather than the bit you've quoted (which certainly could be useful for those who don't know about it), I think the first part of the page is the more relevant for the question. It reminded me that values with recurring fractional parts are different between binary and decimal. I'm sure this completely explains Excel's behaviour here. – Bob Sammers Jun 26 '14 at 13:23
  • @BobSammers, please look at the edits on my answer, I think I may have actually done that originally and then changed my mind!! – Dave Jun 26 '14 at 13:28
  • Indeed you did! If you change it back (possibly without the IEE 754 bit - I know it's relevant to the mechanics, but somewhat tangential to the question), I expect I'd be able to see this as an "acceptable" answer (I mean one I can officially accept - it's perfectly acceptable in the colloquial sense). – Bob Sammers Jun 26 '14 at 13:46