Description: i'm starting to get the bad feeling that: - most developers don't care if calc produces wrong results on the simplest computational tasks ... - and that they propagate 'IEEE double precision' accuracy even though they know it's not true, calc: '=99999999,9999999 - 99999999,9999998' -> 0,000000000000, weitz.de/ieee (64 bit mode): 99999999,9999999; 99999999,9999998; '-' -> 8.940696716308594E-8, round that to 7 decimals digits and you are 100% accurate, calc: 100% fail, maybe it can motivate that calc is weaker than ex$el (calculates less accurate): - in the above task ex$el fails too, but: '=99999999,9999999 - 99999999,9999997' they can! do in Redmont, while calc can't even compute: '=99999999,9999999 - 99999999,9999996', result still '0', isn't that a bit embarrassing? no, no, no, NO! ... no stupid comments like 'fanboy' please, i am NOT!!! here because i want to have everything like this rip-off company, i am here because i want to support a better product ... 'the spreadsheet you ever wanted', 'the spreadsheet that fulfills your needs' ... please, please, please! ... give us our 15-digit accuracy back ... and please, please, please! ... if you need to do some rounding here or there ... do ROUNDING! (at best accurate rounding), no silly truncations ... it would make calc a better spreadsheet, and this world a better one ... !!! other more complicated calculations build on correct results of such simple tasks ... such errors undermine the reliability of calc in general !!! such things have to be solved first, the search for other errors is hopeless or at least very difficult as long as the basics don't work, solutions there degenerate to guessing games or 'trial and error' mumbo jumbo and! inject errors in other areas that only later become apparent ... a downward spiral ... at least! 15 digit accuracy please, no compromises ...! shall / can we introduce a 'round-trip check' like for the dec -> bin -> dec conversion of values? the result of a calculation is correct if it holds in the inverse calculation, i.e. "z as result of '=x - y' is only accepted if '=z + y' results in x again"? but then - please - with reliable comparisons, not with a calc which calculates '=99999999,9999999 = 99999999,9999996' to 'true' without turning red. didn't test versions prior 4.1.6.2 but assume 'inherited' (hey guys, such stuff dates back to 16- and 32-bit systems), didn't test linux but assume 'all', didn't reset user profile as error is stable and apparent in different versions and installations, Steps to Reproduce: 1. see above description, 2. calculate '=99999999.9999999 - 99999999.9999998' in calc, 3. calculate the same with 'weitz' (www.weitz.de/ieee), 4. if you don't trust weitz (it's calculating with possibly buggy math libraries on your! computer (javascript?)), recalculate with pencil and paper or switch on brain and try mental arithmetic Actual Results: '0' :-( Expected Results: at least! IEEE 754 compliant results, at best 'decimal correct': '0,0000001', Reproducible: Always User Profile Reset: No Additional Info: Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 62dff2844b0bf1d1bcb8eb4d6db529ef4a31bee4 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win Locale: de-DE (de_DE); UI: en-US Calc:
I have reproduced this in 7.0.4.6 (x64) and found the same result. Enter 99999999.9999999 in one cell and 99999999.9999998 in another. Format to show 7 decimal places to confirm. In another cell enter a formula to subtract the two cells. The result is 0.0000000 (not 0.0000001) In fact it seems to be exactly that. I then did the same thing in Excel and initially got the same result. If I entered the formula =(A1-A2)*1 The result changes to 0.0000000894. Very Interesting. Changing the formula makes no difference in LibreOffice. Will attach the Excel spreadsheet soon and do a bit more investigation.
(In reply to b. from comment #0) > please, please, please! ... if you need to do some rounding here or there > ... do ROUNDING! (at best accurate rounding), no silly truncations ... It's not a matter of rounding or precision. I haven't checked the code, but I'm guessing the case here is when to treat the result of a calculation as zero.
@Peter S Anderson, thanks for checking, pls. do me a favour and set to 'new', (In reply to Aron Budea from comment #2) > It's not a matter of rounding or precision. I haven't checked the code, but > I'm guessing the case here is when to treat the result of a calculation as > zero. hello @Aron Budea, i assume - only assume - it's a matter of 'rounding by scaling' or similar in something @erAck in 'ask' mentioned as 'approxAdd', the idea was indeed to let 0.1 + 0.2 look like 0.3, or 0.3 - 0.2 - 0.1 be '0', https://ask.libreoffice.org/en/question/274247/calc-wrong-calculation-would-like-a-recheck/ and then it's not a 'new' complaint from me but just another flavor, filed that as i thought showing difference and weakness 'behind ex$el' might get some more attention by devs ... as far as i understood @erAck, it is not done by rounds but by truncation, i didn't analyze it exactly because i was - first - too annoyed that a program claims 'IEEE accuracy' for itself, and then throws away 4 bits and reduces 'doubles' to 48 bits, @@all: to eliminate errors in the last bit! with such methods might ???? make sense / be allowed, but it is hard to catch this last bit when it has moved forward by 'domino rounds', so it would need a more intelligent algorithm?, larger deviations should be fought by finding and eliminating their causes, 'mayonnaise rounds' by more than one bit inevitably lead to errors in other places, already one bit is dangerous, ... all 'imho', corrections by experts welcome ... @erAck mentioned scaling 'too early', is it done by scaling the operands? (yes?, brutalizing the last bits of the result wouldn't harm that much?) crazy? in subtractions the last bits of the operands become quite important when shifted left by 'cancellation', one shouldn't touch them without exactly knowing how and why, and ... what might be good for additions ... might be bad for subtractions ... 'weitz' is not the holy grail, but better than calc and ex$el for this case, it evaluates '8.940696716308594E-8', thus either that or a correctly rounded '1E-7' is acceptable as result for '=99999999,9999999 - 99999999,9999998', nothing else ... calc knows! what is correct, just try '=RAWSUBTRACT(99999999,9999999;99999999,9999998)' or better '=ROUND(RAWSUBTRACT(99999999,9999999;99999999,9999998);15-MAX(LOG(99999999,9999999);LOG(99999999,9999998))) it's just a little uncomfortable to think of and type such complex formulas in everyday use, programmed one time it could work for millions of users thousends of times every day, did - again - read 'what every computer scientist should know ...': 1. 'The standard puts the most emphasis on extended precision, making no recommendation concerning double precision, but strongly recommending that Implementations should support the extended format corresponding to the widest basic format supported, ... ', 2. 'However, when using extended precision, it is important to make sure that its use is transparent to the user. For example, on a calculator, if the internal representation of a displayed value is not rounded to the same precision as the display, then the result of further operations will depend on the hidden digits and appear unpredictable to the user.' there it is!, all both sins of calc in two sentences ... hey guys, that stuff is 30 years old! calc doesn't use the 'extended precision' of the FPU (80 bit) but tries to make it's own 'extension' by taking some (but too few) bits / digits away from the normal result, hiding them from the user, and does not! make it 'transparent' by 'correcting' the value used for calculations to that used for display, but introduces a multi-fold / multi faced model combining a background of sometimes 'precise', sometimes 4-bit rounded/truncated, and in other places rounded to 12 sig. decimal digits values with an already inconsistent in itself UI of sometimes 16 (integers) and sometimes 15 digit display (other values) ... that is a sixpack of ways to trick the user in a guessing game sometimes adapting the calculation value and sometimes not: twelve ways ... !!! then the result of further operations will depend on the hidden digits and appear unpredictable to the user !!!
Created attachment 170956 [details] Excel Sheet showing Calculation differences to Calc I have reproduced this in 7.0.4.6 (x64) and found the same result. Enter 99,999,999.9999999 in one cell and 99,999,999.9999998 in another. Format to show 7 decimal places to confirm. In another cell enter a formula to subtract the two cells. The result is 0.0000000 (not 0.0000001). In fact it seems to be exactly that. I then did the same thing in Excel and initially got the same result but if I use the formula =(A1-A2) the result was changes to 0.0000000894. Very strange. Changing the formula makes no difference in LibreCalc, ie. the result is still zero and incorrect. I have attached a spreadsheet created in Excel and included a number of interesting examples. Column A includes another formula : =((A1/100)-(A2/100))*100 Excel gives 0.0000000931 (arithmetically correct to 15 significant figures) vs the correct answer of 0.0000001 whereas Librecalc gives 0.00000000 again. Column B is that same as Column A except that the initial data values differ in the 6th decimal place rather than the seventh, ie. 99,999,999.999999 and 99,999,999.999998 Interestingly, the calculated values in Excel and Calc are the same suggesting different processing logic in the two columns. In column C I have changed the two initial values to be 999,999,999,999,999 and 999,999,999,999,998 with the same formulae as in column A. This time Librecalc and Excel give the same correct value of 1.0 for C1-C2 and (C1-C2) BUT =((C1/100)-(C2/100))*100 incorrectly gives 0 in Calc but a more accurate 0.9765625000 in Excel. Finally, Column D has the initial values of 99,999,999,999,999 and 99,999,999,999,998. In this case Calc and Excel give exactly the same answers. To me it is very clear that Excel and Calc are giving different answers. The question is what if anything to do about it. My view is that someone needs to take a look at the code to see what it happening. My simplistic view is that Excel is giving better answers, ie. correct to 15 significant figures (notionally compliant to IEEE 754) but a more detailed examination is required. If any change is made to arithmetic processing in Calc it will probably be necessary to introduce a settings flag to allow legacy sheets to give the same “incorrect” results that they previously did.
Status changed as per my comments included with the attachment 170956 [details].
Repro Version: 7.3.0.0.alpha1+ (x64) / LibreOffice Community Build ID: 229123ccc6f90ebf66b3e659bebbd53f8a9bdd3a CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: en-US Calc: CL Version: 7.4.8.0.0+ (x64) / LibreOffice Community Build ID: f8ba7c6f77497e2dc7bfef8378511e2074ce01f9 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: en-US Calc: CL Version: 7.5.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: 1c629ca0048670db4bed5e7d8d76bcf8e81f2158 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: en-US Calc: CL threaded Version: 7.6.0.0.beta1+ (X86_64) / LibreOffice Community Build ID: 1b5cee822e0bc15ddbdfc86926678ca35ab3e082 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: en-US Calc: CL threaded