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
  • 30,396
  • 15
  • 136
  • 260
Bob Sammers
  • 226

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,513