CODEWITHSUNDEEP
javafloating-point
Alexxx
Alexxx

Reputation: 786

Floating-point number in Excel

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?...

Upvotes: 2

Views: 2350

Answers (2)

Patricia Shanahan
Patricia Shanahan

Reputation: 26185

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.

Upvotes: 1

llogiq
llogiq

Reputation: 14511

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.

Upvotes: 1

Related Questions