Created attachment 121748 [details] The table in trouble, for analysis. The password is CUSTOS2014 All formulas work, minus the SUMIFS function does not work in an imported spreadsheet of Microsoft Excel 2007 to LibreOffice Calc 5.0.3. The main error is the Esc. Ant tab. Vanya.
In which sheets / cells can we see the SUMIFS?
Not easy to search with such a big file... But I saw the difference of calculation. On sheet 'ESC. ANT. VANYA' (Sheet 82 on 109 Column F for example), You have the following formula in F11 : =SUMIFS($'12-14'.D:D;$'12-14'.C:C;1;$'12-14'.A:A;B11)-SUMIFS($'12-14-LS'.D:D;$'12-14-LS'.C:C;1;$'12-14-LS'.A:A;B11) The part of the formula causes the problem is $'12-14'.C:C;1 the formula search for the numeric criterion 1 in column C of sheet '12-14' but this column contains the text '1. Calc don't count the text 1 with SUMIFS having creterion 1 (as number). If I select column C on sheet '12-14' and make Data > Text to colums, data is converted to numeric. The same for sheet '12-14-LS'. Now I get the correct values. But I'm not sure this is really a bug... should we count all the 1 whatever they are text or numeric ?
erAck commented on IRC that we should find out if Excel is treating "strings as number" differently in SUM and SUMIFS.
This big of a file is just about useless - we need a trimmed down version with JUST the pertinent info. I just spent 10 useless minutes trying to look through this file to try to triage it (I have Excel) and it was a waste of time. Please provide a better test case - a single formula, 2 sheets max. Thanks!
Good afternoon. The error occurs mainly in Esc. Ant spreadsheet. Vanya. The spreadsheet is full of links, so I did not send only a small file. Have a nice week.
I opened the file in Excel 2010 and LO 5.0.4.2 (and 5.1.0.1) and the results on sheet 'ESC. ANT. VANYA' are exactly the same. Apparently the bug has been fixed. Answering erack's question, all the SUMxxx functions return the same value under Excel 2010 (using the example provided by Gérard Fargeot in the QA mailing list) Hope this helps
Hi, > > The error occurs mainly in Esc. Ant spreadsheet. Vanya. > > The spreadsheet is full of links, so I did not send only a small file. > > Have a nice week. The point is we need you to create a fresh document that is much simpler and demonstrates the same "bug" (if it is in fact a bug). Providing these ultra-complex documents makes triaging problems hard, and fixing problems even harder.
Good Afternoon. Problem resolved. The suggestion of Gerard was correct. Good week. Thanks.
Created attachment 121790 [details] Simple Test XLSX file Attached is a simple test file provided by Gérard Fargeot in the QA mailing list: http://nabble.documentfoundation.org/QA-Need-someone-having-Excel-td4171047.html#a4171083 When open this file with Microsoft Excel and with LibreOffice, the calculated results are different.
Created attachment 121791 [details] Screenshot showing the difference opening the Simple Test File in MSO 2010 and LibreOffice
(In reply to Kevin Suo from comment #10) > Created attachment 121791 [details] > Screenshot showing the difference opening the Simple Test File in MSO 2010 > and LibreOffice I can confirm these differences. The results obtained by LibreOffice are consistent with Apache OpenOffice. The question here is: does TDF want to follow the Microsoft lax logic (assume a text number was a mistake) or follow a strict logic (a text number is deliberate). If the results should be the same as Excel's then this is a bug. This is a philosophical discussion...
Excel in itself is iconsistent. Take for example the formula =SUMIF($B$3:$B$6,1,$C$3:$C$6) in B16. This apparently evaluates every B3:B6=1 as TRUE and thus sums all values in C3:C6 to 10. But, for =SUMIF($B$3:$B$6,1,$B$3:$B$6) the result is 2 and not 4 so it either sums only the numeric values in B3:B6 or only evaluates the numeric values in B3:B6 in the condition as TRUE. Same for =SUMIF($B$3:$B$6,1)
Brilliant.. in the sum-range only numeric content is taken (same as in SUM, so at least consistent there), but in the condition it does not care about numeric or not.
That at least seems to be consistent among all related functions, COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Created attachment 161385 [details] Inconsistency between comparison order
Confirmed in LibreOffice 6.4.3.2. See the attachment "Inconsistency between comparison order". I get the following results: 1) If comparing a number to a number (2nd and 3rd argument to SUMIFS), we get a match as expected. 2) If comparing a number to a string, we get a match. 3) If comparing a string to a number, we DON'T get a match. 4) If comparing a string to a number, but with an explicit equality operator ("="&) we get a match. In Excel, all cases (1-4) result in a match. Because of the unexpected inconsistency, I would say that this is indeed a bug, and that the Excel behavior of having all four cases result in a match should be followed.
Dear Klaibson Ribeiro, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug