1 2 Previous Next 20 Replies Latest reply: May 10, 2009 9:48 AM by 807588 RSS

    Java double floating point precision vs MS Excel

    807588
      Hello,

      I know that in floating point computations there are precission issues related to decimal-to-binary 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.

      A good sample is this calculation: 100.39 - 100.35 which doesn't gives the exact result that a human (thinking in base 10 arithmetic) could expect.

      This substraction is a problem that hapens also in MS Excel ... but you can see it only if you shows the result with Scientific full precision or if you do a conditional formula that compares the cell with the substaction result with 0.04

      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.

      Some hint?

      Thanks.
        • 1. Re: Java double floating point precision vs MS Excel
          807588
          Jaume_Saura wrote:
          Some hint?
          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.
          • 2. Re: Java double floating point precision vs MS Excel
            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
              Jaume_Saura wrote:
              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.
              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.
              • 4. Re: Java double floating point precision vs MS Excel
                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
                  Jaume_Saura wrote:
                  ...
                  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?
                  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.
                  • 6. Re: Java double floating point precision vs MS Excel
                    807588
                    Jaume_Saura wrote:
                    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.
                    I hope you meant 5.985 and not 5,985.

                    >
                    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
                      Excel doesn't use simple IEEE floating point numbers, it uses its own format that is a hybrid between fixed-point 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
                        @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 bit-long) 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
                          JoachimSauer wrote:
                          Excel doesn't use simple IEEE floating point numbers, it uses its own format that is a hybrid between fixed-point 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.
                          Microsoft states here ( [http://support.microsoft.com/kb/78113/en-us] ) that "Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers....". This seems a very well documented article that even explicits the aspects in which Excel don't adhere to IEEE 754.
                          • 10. Re: Java double floating point precision vs MS Excel
                            807588
                            I hope you meant 5.985 and not 5,985.
                            Yes of course!
                            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.
                            I want to show that even for IEEE numbers differing only in the least significant digit (like the decimal-to-binary conversion of 5.985 and 5.984999999999999) Excel preserves the full precision and handles them like different numbers, without rounding of any kind.

                            >>
                            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.
                            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.
                            • 11. Re: Java double floating point precision vs MS Excel
                              DrClap
                              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
                                DrClap wrote:
                                I have lost track of the question here. But lately it seems to be "I want to show (something)". Well, okay. Go ahead.
                                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.

                                >
                                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 not-ilegal-tricky-but-legal-improvement 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 bit-long) 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
                                  [Google: Excel floating point|http://lmgtfy.com/?q=Excel+floating+point].

                                  First hit: [MSKB: Floating-point arithmetic may give inaccurate results in Excel|http://support.microsoft.com/kb/78113].
                                  Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers.
                                  Note that MS don't claim "conformance".... Interesting...

                                  Cheers. Keith.
                                  • 14. Re: Java double floating point precision vs MS Excel
                                    YoungWinston
                                    Jaume_Saura wrote:
                                    Hello,

                                    I know that in floating point computations there are precission issues related to decimal-to-binary 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.
                                    A lot of these inaccuracies have nothing to do with the number of bits in the floating-point number, simply the fact that floating-point is a binary notation (I seem to remember that 0.1 is a problem value, regardless of length).
                                    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 floating-point.

                                    Winston
                                    1 2 Previous Next