2

I've a little problem with a double value in my excel sheet. The double value is the result of a macro computation. It is displayed into the cell as 1.0554. The cell format is Number with 4 decimal points displayed. If I choose more decimal, it will be displayed with trailing 0s (E.G. 1.05540000).

My problem is I'm reading this file with jxls (which is using Apache POI), and the value I'm getting is not 1.0554 but 1.0554000000000001 (the last 1 is at the 16th digit).

I've convert my xls to xlsx, check directly inside the xlsx (which is a zip file containing xml files, so ascii file) and the saved value is 1.0554000000000001; but even if I format my cell as a number with 32 decimal, excel still displays 1.0554000...000 without any trailing 1.

So I suppose that in xls, the real number is also saved as 1.0554000000000001 and not 1.0554.

So how to display in excel the real raw value? or to force excel to save 1.0554 instead of 1.05540000...01? Or to check with macro that the display value is not the raw value?...

Pokechu22
  • 4,984
  • 9
  • 37
  • 62
Alexxx
  • 766
  • 1
  • 11
  • 19
  • I've made a few minor edits to this question - mostly for minor grammar corrections (nothing major; your English is fairly good). Just note that I changed "Leading" to "Trailing". Displayed with leading 0's would look like this: `00001.0554`, while trailing 0's would look like this: `1.05540000`. It's nothing too important but it's something that could lead to confusion in the future. – Pokechu22 Feb 04 '15 at 14:51
  • Read this [link](http://superuser.com/questions/522106/why-does-an-excel-column-formatted-as-number-lose-its-leading-zero-when-the-colu), maybe it can help you. – zoit Feb 04 '15 at 14:52

2 Answers2

1

You have run afoul of the IEE754 gods. Double values have 1 sign bit, 11 bits of exponent and 52 bits of mantissa (the actual representation is a bit more complicated, but what you should take away from this is that the number 1.0554 is not expressible correctly within 52 bits, so the double implementation of your PC chooses the nearest value.

llogiq
  • 13,815
  • 8
  • 40
  • 72
1

Floating point rounding error comes into play as soon as you convert a number that cannot be represented exactly. It tends to increase as you do calculations.

The closest representable value to 1.0554 is 1.0553999999999998937738610038650222122669219970703125. The closest representable value to 1.0554000000000001 is 1.055400000000000115818465928896330296993255615234375 - that is probably what came out of your macro calculation. The bad news is that you don't get exactly the value you meant. The good news is that the difference is often, as in this case, far too small to matter, or even be measured, in real life.

Generally, you do not want the exact value printed. The default toString behavior in Java is to produce the shortest decimal expansion that would convert to the internal value. That is also not usually what you want for end-user displays.

The right thing to do, in most cases, is exactly what you are doing in Excel - display only the digits you think matter for what you are doing. In Java, you can use DecimalFormat to convert a double to a String with control over the displayed digits.

Patricia Shanahan
  • 25,849
  • 4
  • 38
  • 75