-1

It seems as though Excel 2010 is subtracting 0.00000000000001 from a currency value. I have found this in several tabs of my spread sheet. It looks something like this:

(blank cell) (blank cell) $135.57 135.57

-$42.38 (blank cell) $93.19 $93.19

(blank cell) $1000.00 1093.19 1093.19

In column E I have the equation =IF(C3=D3,"P","False") which shows a P for the first row, False in the second row and P in the third row. The second row should have P displayed.

Looking at the length of the values I see the length of the first $93.19 is 16 characters! expanding it out I get 93.1899999999997

How does this happen at different points of the excel spreadsheet/workbook and how can I correct this?

Thanks

Jeffy
  • 3

1 Answers1

0

This occurs because Excel uses IEEE-754 floating point numbers internally, as do nearly all programs written today. IEEE-754 uses base 2 rather than base 10, so some numbers which can easily be rendered in base 10 (such as 1/10th) must be approximated using the base 2 notation.

This is not trivial to fix, because this is a feature of modern CPUs, not just Excel. However, in a knowledge base article, Microsoft suggests that you can work around this by going to File->Excel Options->Advanced Options and turning on "Precision as Displayed," and making sure that every cell in the workbook has the correct precision for the arithmetic you are looking for.

Alternatively, you can use the round() function to round away those tiny errors to your preferred precision at key points in your equation.

Cort Ammon
  • 2,376