This content has been marked as final.
Show 20 replies

1. Re: Java double floating point precision vs MS Excel
807588 May 8, 2009 6:47 AM (in response to 807588)Jaume_Saura wrote:
It is my understanding that Excel, in common with some C compilers printf(), does not display to the the full accuracy but rounds to about 1 significant figure less than the full accuracy. I could be wrong.
Some hint? 
2. Re: Java double floating point precision vs MS Excel
807588 May 8, 2009 6:53 AM (in response to 807588)If the problem was only a "display" issue in MS Excel, the conditional formula in Excel should reveal the difference, and it doesn't shows any difference in the sample.
Edited by: Jaume_Saura on May 8, 2009 4:52 AM 
3. Re: Java double floating point precision vs MS Excel
807588 May 8, 2009 7:05 AM (in response to 807588)Jaume_Saura wrote:
Not really. Excel may by taking the APL approach and using a comparison tolerance OR Excel may be rounding prior to any equality. I don't know. What I do know is that Java allows you, the programmer, to decide what to do where Excel tries to take these decisions away from the average Joe who uses Excel.
If the problem was only a "display" issue in MS Excel, the conditional formula in Excel should reveal the difference, and it doesn't shows any difference in the sample. 
4. Re: Java double floating point precision vs MS Excel
807588 May 8, 2009 7:16 AM (in response to 807588)That's not the case because I'm able to put 5,985 in a cell, then 5.984999999999999 in another cell, and I can see differences both in screen when I configure enough precission and also when I compare both cells with a formula.
Moreover, I showed the Hex representation of these two IEEE numbers from a Java program and they differ only in the least significant bit.
So, excel is able to store them at full precission and treates them like different numbers when you operates with them. 
5. Re: Java double floating point precision vs MS Excel
800282 May 8, 2009 7:38 AM (in response to 807588)Jaume_Saura wrote:
Since you get the expected round off error in Java, but not in Excel, my hint is to post in an Excel forum and ask how Excel does this.
...
Both Java and Excel use the IEEE 754 floating point format with double precission, so I could expect the same roundoff problems in both.
Some hint? 
6. Re: Java double floating point precision vs MS Excel
807588 May 8, 2009 7:44 AM (in response to 807588)Jaume_Saura wrote:
I hope you meant 5.985 and not 5,985.
That's not the case because I'm able to put 5,985 in a cell, then 5.984999999999999 in another cell, and I can see differences both in screen when I configure enough precission and also when I compare both cells with a formula.
>Moreover, I showed the Hex representation of these two IEEE numbers from a Java program and they differ only in the least significant bit.
Which is what I would expect. But which of the IEEE numbers is closest to 5.985 decimal? I would guess that one is just greater and the other is just less than the exact value of the IEEE representation.
>So, excel is able to store them at full precission and treates them like different numbers when you operates with them.
I must be missing your point. Java stores literal doubles as the closed IEEE format number to the desired literal. I can't find a reference to this at the moment but I'm sure Google will find one. It may be in the JLS.
I have computed values in C++ on a PC using both Linux and Windows and I get exactly the same IEEE format values. I did this because I was trying to find why I had a very small difference between my C++ version of the FFT and my Java version. This difference turned out to be caused by my using two slightly different approaches to generating the sin and cos arrays. The C++ stdout displays of double values always seem to be rounded slightly different to the Java stdout displays. 
7. Re: Java double floating point precision vs MS Excel
JoachimSauer May 8, 2009 9:35 AM (in response to 807588)Excel doesn't use simple IEEE floating point numbers, it uses its own format that is a hybrid between fixedpoint decimal and floating point (can't find the name and/or reference right now, that's left as a challange to the reader).
Therefore Excel doesn't behave the same in all number ranges. 
8. Re: Java double floating point precision vs MS Excel
807588 May 8, 2009 1:19 PM (in response to 800282)@prometheuzz: I'm not sure if, in this case, the round off error is "expected". The most accurate result (in base 10) should be the excel one, so I don't discard to discover an enhanced calculation precission in excel not implemented in JVM.
For example: imagine that excel makes use, for intermediate calculations, of the extended long double format (80 bitlong) available in the intel pentium FPU and the Sun's JVM does not. In this scenario, could be possible to get a more accurate result in excel than in JVM. 
9. Re: Java double floating point precision vs MS Excel
807588 May 8, 2009 2:34 PM (in response to JoachimSauer)JoachimSauer wrote:
Microsoft states here ( [http://support.microsoft.com/kb/78113/enus] ) that "Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floatingpoint numbers....". This seems a very well documented article that even explicits the aspects in which Excel don't adhere to IEEE 754.
Excel doesn't use simple IEEE floating point numbers, it uses its own format that is a hybrid between fixedpoint decimal and floating point (can't find the name and/or reference right now, that's left as a challange to the reader).
Therefore Excel doesn't behave the same in all number ranges. 
10. Re: Java double floating point precision vs MS Excel
807588 May 8, 2009 2:58 PM (in response to 807588)I hope you meant 5.985 and not 5,985.
Yes of course!
I want to show that even for IEEE numbers differing only in the least significant digit (like the decimaltobinary conversion of 5.985 and 5.984999999999999) Excel preserves the full precision and handles them like different numbers, without rounding of any kind.Moreover, I showed the Hex representation of these two IEEE numbers from a Java program and they differ only in the least significant bit.
Which is what I would expect. But which of the IEEE numbers is closest to 5.985 decimal? I would guess that one is just greater and the other is just less than the exact value of the IEEE representation.
>>
Previously you stated that perhaps Excel does some kind of rounding or using a comparison tolerance and I want to show that this is not the case because two IEEE numbers differing only in the least significant bit are treated like different numbers both when displayed and when operated with formulas.So, excel is able to store them at full precission and treates them like different numbers when you operates with them.
I must be missing your point. Java stores literal doubles as the closed IEEE format number to the desired literal. I can't find a reference to this at the moment but I'm sure Google will find one. It may be in the JLS. 
11. Re: Java double floating point precision vs MS Excel
DrClap May 8, 2009 3:40 PM (in response to 807588)I have lost track of the question here. But lately it seems to be "I want to show (something)". Well, okay. Go ahead.
But if the question is "How does Excel do (something)?" then remember that this is a Java programming forum. 
12. Re: Java double floating point precision vs MS Excel
807588 May 9, 2009 12:57 AM (in response to DrClap)DrClap wrote:
Some people suggested that excel could be doing some "tricks" (basically rounding results artificially) to appear like more precise in the calculus. In response to these comments I was looking for evidence that this is not the case showing some samples.
I have lost track of the question here. But lately it seems to be "I want to show (something)". Well, okay. Go ahead.
>But if the question is "How does Excel do (something)?" then remember that this is a Java programming forum.
The question could be if it is possible that JVM is less precise than excel in the calculus when working in floating point arithmetic.
I showed evidence that excel don't seems to get rounded results and that Microsoft admits working in IEEE 754 and, in consequence, admits possible roundoff results. Afterwards I showed a sample in which excel "seems" more precise than Java, so my question is: could it be a fault in java?.
I even suggested a possible notilegaltrickybutlegalimprovement scenario that could justify this behavior. I'll quote myself:
For example: imagine that excel makes use, for intermediate calculations, of the extended long double format (80 bitlong) available in the intel pentium FPU and the Sun's JVM does not. In this scenario, could be possible to get a more accurate result in excel than in JVM.
This topic could be discussed in forums of any of the two systems affected and talinkg about them only in an excel forum could end with some comment like: excel does this calculus perfectly, without any loss of precission, so you should go to a java forum to look for possible bugs in JVM. 
13. Re: Java double floating point precision vs MS Excel
800308 May 9, 2009 2:13 AM (in response to 807588)[Google: Excel floating pointhttp://lmgtfy.com/?q=Excel+floating+point].
First hit: [MSKB: Floatingpoint arithmetic may give inaccurate results in Excelhttp://support.microsoft.com/kb/78113].
Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floatingpoint numbers.
Note that MS don't claim "conformance".... Interesting...
Cheers. Keith. 
14. Re: Java double floating point precision vs MS Excel
YoungWinston May 9, 2009 2:58 AM (in response to 807588)Jaume_Saura wrote:
A lot of these inaccuracies have nothing to do with the number of bits in the floatingpoint number, simply the fact that floatingpoint is a binary notation (I seem to remember that 0.1 is a problem value, regardless of length).
Hello,
I know that in floating point computations there are precission issues related to decimaltobinary conversions and also related to IEEE 754 specification restrictions (basically the number of bits used for fp numbers) shared with a lot of languages and computer systems and that Java language makes simply more evident because it shows doubles with full precision by default when you print them to screen.
...
But now I've at hand another calculation that shows a little (decimal) inaccuracy in Java and shows an EXACT result in Excel, and I dont understand why. This is the sample:
double a = 0.21;
double b = 28.5;
double c = a * b;
The result, converted to decimal, shows in screen like 5.984999999999999 but in MS Excel 2000 this time I get the exact result even showing it at full scientific precision and even if I do a conditional formula that compares the result with 5.985.
Both Java and Excel use the IEEE 754 floating point format with double precission, so I could expect the same roundoff problems in both.
The IEEE standard also allows for "denormal" numbers (numbers less than 1 ulp away from 0), so it seems possible that Excel could be using those to determine whether a value is 'exact' or not.
That said, it's just a guess. I'm no expert (and definitely no mathematician).
BTW: Most of these problems can be eliminated using BigDecimal, rather than floatingpoint.
Winston