LibreOffice Calc calculation error

[manas]

Hello,

I am using LibreOffice Calc for calculating numbers in recurrence relations and finding their residues through modular arithmetic. I am finding that the results produced by LibreOffice Calc are erroneous. I would like to find out what causes this error and how it can be fixed.

For forming the numbers of the recurrence relation, I manually type in two numbers to serve as the seeds. These numbers are typed into row 1 & 2, column A. The rest of these integers are calculated by using the formula A3=A1+A2 then autofilling for every subsequent row. The residue is calculated using B3=MOD(A3,360) and again autofilling the rest of the entries. Everything is fine until row 75 where I have the number 1304969544928660. The two numbers in row 73 & 74 are 498454011879264 & 806515533049393. The next number should be 1304969544928657, why does LibreOffice Calc produce 1304969544928660 instead?

Thank you,
Manas

[mmohrhard]

What you see is a typical floating point problem. Calc uses internally IEEE 754 double values which means it only has a limited precision. Double only support between 16 and 18 valid digits which means that every number that has in anyway more digits will be incorrect. For nearly all calculations this does not create a problem and inside calc we protect against a few common problems by some common techniques to deal with floating point errors. However this can only woraround some basic problems.

As Kohei already pointed out in the bug report there is no technical solution for this problem inside Calc. Use a program that does not need to care about the performance but you’ll have no luck with common spreadsheet programs.

[qubit1]

I believe this is the same situation as fdo#50299

In short, when LO provides the function MOD(), that isn’t actually an integer-safe modulo calculation. It’s a floating-point function that tries to approximate the result.

Certain types of integer math aren’t well-suited to LO. Perhaps something like Octave or Sage might be a better fit for your numerical analysis work?

[kumiponi]

MOD() is particularly misleading because it’s supposed to be all about integers. Let’s test if 20 is a prime number: MOD(FACT(20-1)+1;20) = 0. Hey, apparently it is…