Description: Calc produces a different (Incorrect) result than Excel or Google Sheets in a formula that contains SUMPRODUCT and MOD. Steps to Reproduce: 1.Enter a 14 length text field of numbers in cell E5 (69765050007777) 2.Enter Formula "=MOD(10-MOD(SUMPRODUCT(--(MID(E5,{2,4,6,8,10,12,14},1)))*3+SUMPRODUCT(--(MID(E5,{1,3,5,7,9,11,13},1))),10),10)" in cell F5 3. Actual Results: Result 2 Expected Results: Result in Excel & G Sheets 6 Reproducible: Always User Profile Reset: No Additional Info: Calculate correctly. This is my first time loading a bug, I do not see a method of attaching a spreadsheet with formulas in it.
Created attachment 150427 [details] Spreadsheet with formula
Sorry In original example, actual result is 4 and expected is 6. I attached a spreadsheet and can open up in excel and calc and enter values and see differences in results of two (Excel is calculating correctly)
Created attachment 150435 [details] The example file with the formula cut into pieces. With the formula cut into pieces I can't see the error. I have not excel to test. Maybe excel gives negative values for MOD() function over negatives input.
Error is caused by double negative sign which returns négative number. Replacing "--" by "1*" gives expected results.
Created attachment 150442 [details] simple test simple test file to demonstrate the bug
It is in relation about when LibreOffice does the conversion from text to numbers. Automatic conversion doesn't work with arrays because only direct references to a cell can get the automatic conversion (with GeradF sample) SUM(B2;B3;B4;B5;B6) works but not SUM(B2:B6), except forcing the conversion with an expression like @GerardF comments. Or replacing "--" by "--1*" gets the results. Please take a look to Eike comment https://bugs.documentfoundation.org/show_bug.cgi?id=120577#c12 IMO this is not a bug.
(In reply to m.a.riosv from comment #6) > It is in relation about when LibreOffice does the conversion from text to > numbers. > > Automatic conversion doesn't work with arrays because only direct references > to a cell can get the automatic conversion (with GeradF sample) > SUM(B2;B3;B4;B5;B6) works but not SUM(B2:B6), except forcing the conversion > with an expression like @GerardF comments. > Or replacing "--" by "--1*" gets the results. The "--" thing is an explicit conversion, though, just like the "1*" is, unless text variables have a uni-operator "-" that I'm not aware of. "--" is actually a common way to convert texts to numbers, and is taught in a lot of "Excel tips and tricks" kind of books.
may be off topic, but astonishing for me ... 1 -> 1 +1 -> 1 =1 -> 1 =+1 -> 1 =++1 -> 1 -1 -> -1 =-1 -> -1 =--1 -> 1 -(-1) -> 1 --1 -> -1 last line conflicts with my 'simple minded user expectations' and with what i was taught in school. different evaluation of an expression if 'calculated' or or 'explicitly advised to be calculated', who the fu... invented things like this, did he ... ? which user should spend how much time rtfm before using calc till getting this special knowledge and remembering it in cases where he would create faults with SIU 'simple intuitive use'? tested with ver. Version: 6.3.0.0.alpha0+ (x64) Build ID: 9956cf0692058414ef3efdb0e8058fbb0b39f6bc CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; TinderBox: Win-x86_64@42, Branch:master, Time: 2019-04-04_02:20:31 Locale: de-DE (de_DE); UI-Language: en-US Calc: threaded reg. b.
It appears Excel & Google Sheets treat "--" differently than Calc. As others have stated, Since "--" is actually a common way to convert texts to numbers, and is taught in a lot of "Excel tips and tricks" kind of books, this makes using Calc looking like it is calculating (to the norms) but not calculating to what is expected. Therefore, either 1) there should be a warning when "--" is used that this calculates differently than the expected (at least on the first calculation of the spreadsheet) or 2) Calc should calculate the same as Excel and Google Sheets.
Created attachment 157384 [details] file_with_some_more_conversion_curiosities c#9: > It appears Excel & Google Sheets treat "--" differently than Calc. and compatibility is considered a strong argument in other decisions designing calc, sometimes even to keep bugs for compatibility, why isn't it respected here? > As others have stated, Since "--" is actually a common way to convert texts to numbers, and is taught in a lot of "Excel tips and tricks" kind of books, it's just a statement, i'm missing citations / references, on a quick search i found only one site (https://exceljet.net/the-double-negative-in-excel-formulas) about handling of "--" for logical values: ‚The first negative will convert TRUE to -1, and the second negative will convert 1 to 1‘ that is someway plausible, but what does calc do instead: the double negation converts a number – or number representating text – to its negative … > this makes using Calc looking like it is calculating (to the norms) but not calculating to what is expected. ‚calculating to the norm‘ … which norm? did i miss something? > Therefore, either 1) there should be a warning when "--" is used that this calculates differently than the expected (at least on the first calculation of the spreadsheet) or 2) Calc should calculate the same as Excel and Google Sheets. yes! if double negation sign is not interpretet as two calculation operators but as one conversion sign both ‚-‘ signs should be away after conversion … handling of '--' by calc is using one of the '-' for conversion and leaving one for a - wrong - result. c#7: > the „minus minus“ thing is an explicit way to convert text to numbers, ok, but why should it convert 1 to -1? regardless of text or numbers. simple user expectations say the highlighted cells in the attachement provided are questionabe, thus i suggest to change that behaviour, especially the change from col. E to F, and col. G to H are 'not intuitively understandable' i'm aware that a change would break compatibility into calc itself :-( thus would need a warning to users or one more options switch? :-( but it should be possible to find ways to do things like that otherwise most progress is blocked. reg. b.
(In reply to b. from comment #8) > may be off topic, but astonishing for me ... > --1 -> -1 That is clearly a bug, though not related to the formula expression behaviour discussed here otherwise, but of the input number scanner instead. Anyway, I stumbled over the causing code and in search found this bug here and now created bug 134490 for that.
The problem here is not applying the (negation) matrix operator on values that are converted from strings.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/7ead920f7015bb8e7a4343b863333bb26187d9a3 Resolves: tdf#124454 exec the matrix operator on values converted from strings It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending https://gerrit.libreoffice.org/c/core/+/98338 for 7-0 https://gerrit.libreoffice.org/c/core/+/98339 for 6-4
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-0": https://git.libreoffice.org/core/commit/ef0f60f28ac83c55f260f61346f03b5f928764d8 Resolves: tdf#124454 exec the matrix operator on values converted from strings It will be available in 7.0.0.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-4": https://git.libreoffice.org/core/commit/e2713a0a5677dd92e3a88edc534a2e35ce91a0fb Resolves: tdf#124454 exec the matrix operator on values converted from strings It will be available in 6.4.6. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/96547693533aa0261ed46c19f922b3b8172591e3 tdf#124454: sc: Add unittest It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
hello @Eike, looks better now, thanks for fixing this issue :-)
Solved: now the value is 6 Version: 7.1.0.0.alpha0+ Build ID: abea0d6647c7f1f7e76c73c26cb80e6a67dc5111 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: ro-RO (ro_RO.UTF-8); UI: en-US Calc: threaded