Ask Your Question

LibreOffice Calc calculation error [closed]

asked 2012-11-19 01:35:27 +0200

manas gravatar image


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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-19 00:45:11.431565

2 Answers

Sort by » oldest newest most voted

answered 2012-11-19 23:08:55 +0200

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.

edit flag offensive delete link more

answered 2012-11-19 02:57:13 +0200

qubit gravatar image

updated 2012-11-19 02:59:57 +0200

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?

edit flag offensive delete link more


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

kumiponi gravatar imagekumiponi ( 2015-06-22 18:18:16 +0200 )edit

Question Tools


Asked: 2012-11-19 01:35:27 +0200

Seen: 2,403 times

Last updated: Nov 19 '12