Spreadsheet calculations with (at least) the accuracy of a C double

Question

I was doing some calculations for planning an improved implementation of my prime sieve when I noticed that the Libre Office spreadsheet was showing the wrong values for numbers far below 2^53, which is the limit for precise integer calculations in FoxPro and a multitude of other languages that internally use a C double (a.k.a. IEEE 754 double).

Some quick checks showed wrong results for numbers as low as 2^50. What's worse, the flaky piece of software did not give any warning that it was working beyond its operational limits and that the displayed values were only approximate (even if the correct values were entered manually). I guess they thought what's good enough for POS like Excel then must be good enough for them (but they ought to have named it 'Guesstimate' instead 'Calc' then).

Anyway, is it possible to put the spreadsheet into some kind of accurate mode that gives at least the precision of a double, and/or get it to show some kind of warning when some calculation exceeds its numerical capabilities?

Or is there some other spreadsheet available that's suitable for programmers in an age where even phones and toasters are powered by 64-bit chips?

Background: in this particular case the need for precision arises because the exactness of the formulas is verified by comparing certain result columns with lists of well-known numbers (like the number of primes up to 2^k, available from OEIS) or with numbers derived from instrumented code. The idea is to get the formulas right by checking them for doable ranges - up to somewhere between 2^32 and 2^40 - and then to use those formulas for studying the behaviour for ranges up to 2^64 (where it is impossible to get a complete picture by running exhaustive tests).

P.S.: I found that the problem has been analysed extensively in On the Numerical Accuracy of Spreadsheets (pdf). It seems that gnumeric holds up a tad better than the rest but it's more a case of the one-eyed among the blind than of really passing muster; besides, it's not available for Windows and thus would require a Linux VM to be fired up on the platform I am developing on and for...

UPDATE/WORKAROUND

Up to 2^49 there are no problems, so it's possible to work normally. This is sufficient to get a precise idea of things. In the final tables, big numbers and high-precision results (computed elsewhere) are entered as pre-formatted text (or rather imported/pasted) so that Calc can't bork the display. The numbers can still be referenced by applying the VALUE() function, and for many inline calculations the internal precision is quite sufficient - especially for graphing.

The image below demonstrates what I posted in one of the comments, which is that the internal precision is higher than what's displayed. How are we supposed to find out then what the actual values are if the program doesn't show them? Play around with adding/subtracting small values, watch changes, create and test hypotheses regarding the relation of actual values, supposed rounding behaviour and displayed values? The mind truly boggles.

I'm offering a bounty for any of the following:

• a practical way of restoring Calc to sane behaviour in the sense of WYSIWI (what you see is what's inside, or rather if it's inside then it can be displayed) without reducing internal precision and without forking the whole bloody source tree and fixing it oneself

• a pointer to a spreadsheet with higher precision than IEEE 754 double (preferably arbitrary precision, like GP/PARI but as a spreadsheet) that's public domain or shareware; an online/cloud thingies would be acceptable

The reason for the first item is twofold: for one thing it's a bloody nuisance when spreadsheet precision is lower than the universal IEEE 754 double because it means that experience/knowledge regarding accuracy/precision/stability at that baseline level doesn't carry over. For another, it's an even greater nuisance if we can't trust that the displayed values are correct even when we know for certain that the actual values are.

The reason for the second item is the simple fact that it's rather cumbersome to have to script things in another program for computing things at high precision or with bignums, or to have to edit/compile/run programs for the purpose. Besides the awkward split into a spreadsheet and a bunch of scripts or source files, it would be more natural and convenient to work just inside the spreadsheet, period.

Answer 1

PrecisionCalc is an Excel add on that allows computations with arbitrary. precision.

There is a Free edition with 3,000 significant digits limit.