Unexpected Results from Excel 2007
Posted on 2011-10-20
While comparing two worksheets, I came across an interesting and disturbing problem. The two worksheets appear to have values that are exactly 1000 times different for corresponding values. However, a subtraction of the two values does not always result in zero as you might expect. This odd result seems to caused by the use of parens in a cell formula.
For instance, consider a value in cell A1 with value 0.000702 and a value in B1 of 0.702 in another worksheet. [Note: I am looking at the values in the cells themselves, not the formatted results.] These two values appear to be different by exactly a factor of 1000. However, if you apply the formula (B1/1000 – A1), which you would expect to be zero, you get a nonzero result -- specifically, -1.1 * 10^-19. Clearly, very close to zero, but not quite. Interestingly, if you do the same computation without the parens, such as B1/1000 – A1, the resulting value is zero.
This strange result occurs for many, but not all, cells I am working with.