Microsoft Excel 2010 bug

In cell H:212, a formula is calculating a very tiny decimal error.

I have several columns. Column C is entered manually, Column D is the formula =C3+A4-B4, and Column E is the formula =C4-D4.

15.01 -    1,168.29 1,168.29 -   
  -   43.00  1,125.29 1,125.29 -   
  -   1,125.00 0.29  0.29  0.00
950.00 -      950.29  950.29  -   

I noticed the 0.00. When I increased the decimals to 30 places, it's calculating 0.29-0.29 as 0.000000000000036359804056473900.

Haven't the slightest idea why, although I suspect that there may be something wrong in the underlying code that corresponds to cell H:212; since I've repeatedly copied those formulas through the spreadsheet, thousands of rows deep. The teensy decimal doesn't appear anywhere else but cell H:212. I've deleted the formula and started over. I've also copied the formula in other locations, where it works properly, and once I hit cell H:212, I get the same error.

March 10th, 2015 10:16am

Haven't the slightest idea why, although I suspect that there may be something wrong in the underlying code that corresponds to cell H:212

That is not the reason and that is no bug. The issue is related to the IEEE number format, resp. the max. possible precision of the IEEE.

http://en.wikipedia.org/wiki/IEEE_floating_point

Excel works (as most of any program around the world) with the Double data type, which has a precision of 16 decimal places. Means any calculation is rounded by the IEEE format.

In your case the 0 is the problem, as example, write this values in column A:

0.05
-0.07
0.02
0

then use SUM to calculate the sum of this values and format the cell with 30 decimal places. As you see the result is not 0 as expected. Remove the 0 at the end and the result is correct.

The solution is to use ROUND and "cut off" the unwanted decimal places.

Andreas.

Free Windows Admin Tool Kit Click here and download it now
March 10th, 2015 2:15pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics