0

I'm implementing in Java an algorithm written with Excel formulas, based on Markov's chains, and comparing the results with Jxl library. The problem is, as I have read, that the two languages haven't the same precision. In fact, they return equal results only to the second decimal digit. Is there a method to "emulate" Excel precision in Java, to test if the algorithms behave exactly the same way?

Lore
  • 1,286
  • 1
  • 22
  • 57
  • relevant xkcd: https://xkcd.com/1926/ jk, what you are doing is fine, but whoever wrote the algorithm in Excel should be stoned. Can't you just compare results up to the second decimal digit? – kutschkem Feb 22 '18 at 08:06
  • Who wrote in excel was an engineer XD yes, but in this particular case I prefer more accurate comparison. – Lore Feb 22 '18 at 08:13
  • "the two languages haven't the same precision": If you are talking about `Double precision` for both, then the differences are not as much. In `Java` the maximum count of significant digits variates between 15 and 17 while in `Excel` it is exactly set to 15. "In fact, they return equal results only to the second decimal digit": Then maybe `Java` is using `BigDecimal` instead of `double`? This `Excel` cannot do. – Axel Richter Feb 22 '18 at 09:20

1 Answers1

1

You can find an analysis how Excel precision works here:

https://community.oracle.com/thread/2053255?start=15&tstart=0

It appears that the 16th decimal digit is rounded down when processing.

See this question for a method to emulate the floating point precision:

Matching Excel's floating point in Java

I suppose that this all taken together means that internally, meaning inside the formula of a single cell, Excel is using normal doubles. But when setting the value of the cell, the 16 decimal place is rounded down. Which means for your algorithm that whenever there used to be an intermediate result written to a cell, you need to use the rounding method from the linked answer. Test this, for me it is not clear whether these intermediate values need to be rounded or not.

kutschkem
  • 7,826
  • 3
  • 21
  • 56
  • It exists a method to emulate this behavior in Java? – Lore Feb 22 '18 at 08:34
  • @Lore edited with more information. Hope this helps. – kutschkem Feb 22 '18 at 08:45
  • From the post seems that I must use BigDecimal to achieve my result. But how can I use it in a complex algorithm? I have to transform doubles in BD only at start? After every calculation? Intermediate results of calculus must be transformed? – Lore Feb 23 '18 at 09:31