CALC: why are bits killed in subtractions with heavy cancellation and can this be turned off?

[giorgio_mcmlx]

For example, if n is positive and x(n) is calculated to be 1/(2^n), y(n) ← 1 - x(n), and finally z(n) ← 1 - y(n) in a CALC sheet, then the IEEE 754 DP format should be able to yield the mathematically correct result z(n) = x(n) for n <= 53. Yet, in several recent versions of CALC up to 4.1.4.2 running on MS-Win and Linux, this holds true only for n <= 48, whereas for 49 <= n <= 53, y(n) is artificially set to one so that z(n) equals zero.
My first question is why this “bit killing” feature has been implemented at all, and my second question is whether there is a means to force subtractions leading to heavy cancellation to be calculated to full DP precision?
At least in XL, which implements bits killing for 50 <= n <= 53, putting parentheses around a difference serves this purpose, but this trick does not work in CALC.

Any ideas please?

[oweng]

You probably need to provide some data from an example, but on the face of it, this sounds like the unsolvable problem described in bug fdo#37923 i.e., that subtraction of very large or near identical numbers is out of the bounds of the precision available to a Double (64bit) value. Does Excel produce a different result to Calc? What are the results produced by Calc / Excel?

[newbie-02]

hello @oweng, hello @AlexKemp,
this answer is not! correct, pls. remove the hook,
tdf#37923 isn’t unsolvable but solved (besides not fully),
the wrong result isn’t a problem of IEEE 754 precision, check the same calculation with www.weitz.de/ieee,
the wrong result in calc is most likely produced by one of the ‘4-bit truncations’ implemented in ‘approxAdd’ or https://bugs.documentfoundation.org/show_bug.cgi?id=124286 or similar, it’s a special error in calc, not only producing a wrong rounded display but intentionally neglecting differences falling below 4 bits
cluttering the precision available in IEEE’s! which work quite well with binary ‘even’ values as in this case, see ‘weitz’,

[newbie-02]

heyheyheeyyyy!, learned something again … ‘XL’ can be pronounced as ex$el and isn’t too bad! …
a silly ‘convenience rounding’ instead of exact calculations is silly, but becomes less silly if you can turn it off …
calc can do that too, but not with parentheses but by using ‘rawsubtract’ instead of ‘-’ … compared to ex$el awfully cumbersome … calc should catch up!

[newbie-02]

don’t know who re-checked this answer correct, but it isn’t,
the problem mentioned by the OP is neither unsolveable, nor regarding ‘large numbers’, nor an problem overstretching ‘doubles’.
it is - afais - solely due to how calc handles results, how calc uses IEEE, how calc rounds. I consider the procedures too crass, one can discuss about that, but to blame such problems simplistically on ‘fp-math is inaccurate’ is not fair.

[newbie-02]

better? answer:

1. this case is not ‘fp-math’ or IEEE 754, but rounding in calc, see [attached sheet - click to download],

2. it can! be turned off, rawsubtract calculates without rounding, see attached sheet,

3. ex$el in ‘normal rounding mode’ rounds less aggressive than calc and works til 1/2^49,

4. ex$el in ‘no rounding mode’ (place the formulas in parentheses, thanks to @giorgio_mcmlx for that tip), is as clean as calc, ‘TRUE’ til 1/2^53,

• ‘My first question is why this “bit killing” feature has been implemented at all’ - more or less sufficient attempt to hide problems like 0,1 + 0,2 = 0,30000000000000004 from the user? shot far above the target but might have been necessary in former times with 16 bit systems and weak ‘c’ libraries?

• ‘second question is whether there is a means to force subtractions …’ - yes, use rawsubtract, it’s less comfortable than ex$el but works …

P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …

[Lupp]

@newbie-02: Did you downvote Owen’s answer?
If so, that’s absurd. The question and the answer are more than seven years old, Owen has retired 5 yeras ago, and the behaviour of Calc was changed since.
“OwenG” was one of the most valuable contributors ever concerning user support for LibreOffice, also in this Q&A site, but mailny in the (Alas!) died well structured libreofficeforum.org. (We had some discussions concerning cancfellation and conversions Double<–>Decimal there, too.)
I would suggest you stick to more recent threads where it is more likely that the actual situation hasn’t changed basically since the time of the Q and the A as well.

[newbie-02]

hello @Lupp,

nice to hear you awake and well,

downvoten do not remember, can I check somehow?

I have ‘reopened’ because I did not want to leave too many misleading answers / comments which convey a ‘fp-math is inaccurate, we don’t bother’ feeling, and that a clear distinction is made between ‘calc’ - where we can do something and the developers are responsible - and IEEE 754 - that’s the way it is, we have to live with it. My point was not ‘against someone’.

I have also written that at that time … probably other conditions prevailed, have now looked again: ‘that subtraction of very large or near identical numbers is out of the bounds of the precision available to a Double (64bit) value’ is simply wrong and was it also at that time already. It is - in my opinion - not acceptable that calc rounds away 4 to 5 bit from the possible precision (in some places), and then claims to deliver IEEE 754 precision … continued …

[newbie-02]

… continued … or it would be due to international standards if results are garbage.

If the developers and supporters know what is going on in ‘their’ program and communicate that, you can discuss it, if everything is blamed on IEEE and ‘isso’ it chokes off any further effort.

If I ‘stepped on Owen’s toes’ or yours with this, it was unintentional and I apologize in all form.

b.

[newbie-02]

tried to look at http://libreofficeforum . org/, but says ‘Warning: Potential Security Risk Ahead’

[Lupp]

The libreofficeforum.org died years ago from lacking support by tdf. (The “founders” there may also have chosen the wrong CMS for it. When an update to a new version of that software was needed, the then administrator decided it was too complicated, and that very well structured and valuable forum closed. One of the unhappy users [charly.it] managed to create an archive without the CMS functionality, but that seems to also have died. For what reason -and to whom registered- the old URL still exists I don’t know.
See LibreOfficeForum.org is Shutting Down (View topic) • Apache OpenOffice Community Forum.
Bad decisions concerning software for user support may have some tradition concerning LibreOffice. Will it be continued?)
The still well mainatined forum.openoffice.org accepts threads concerning LibreOffice, but doesn’t empasize this on their mainpage (hint is white on light-blue!) - and the differences get more and more relevant.
Quo vadis? .