I have been using the same Excel spreadsheet to calculate ranking for over a decade with Excel and recently Calc. After I upgraded to the 4.3 release, I noticed Calc had corrupted all of my spreadsheets so badly that I needed to restore them from a backup and switched back to an old copy of Excel. Please address this issue before more people's spreadsheets are harmed! Version: 4.3.2.2 Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d
Created attachment 108589 [details] Simplified example without data before 4.3 I can't attach the actual spreadsheet as it had confidential information. Here’s a simplified example.
Created attachment 108590 [details] Spreadsheet mangled by 4.3 ;-(
Hello, RankBefore: Total Hours Top =B2+B4 Total Hours Bottom =B3+B5 RankAfter Total Hours Top =B5+B3 Total Hours Bottom =B4+B2 B2 = 1 B3 = 1 B4 = 1 B5 = 2 Calculations is correct. Please, where is the problem?
Another manifestation of mess put in Data > Sort feature. *** This bug has been marked as a duplicate of bug 81633 ***
raal, The formulas in RankAfter should be the same as RankBefore. I've been using spreadsheets for over a decade now and never seen anything like this. According to bug 81633, Calc 4.2 and Excel correctly sort formulas with relative references by not changing their value. Calc 4.3 suffers from a serious regression where relative references are tread as absolute references scrambling user's spreadsheets.
Please, could you explain step by step what to do, starting from RankBefore (attachment 108589 [details]) to get RankAfter (attachment 108590 [details]). I do not understand what changed in the data. Sorting, ok, but sorting exactly which range of cells and on which key ? Are you sure of your formulas in RankBefore? According to A8 and A9 I had put D8=B2+B3 and D9=B4+B5 instead. Set status to NEEDINFO. Please set it back to UNCONFIRMED once you have provided requested informations. Thank you for your understanding. Best regards. JBF
Jean-Baptiste Faure, Please understand that this is an extremely simplified version of the model I use at work. It ran for years perfectly in both Excel and until recently Calc. To run the model, you open the "good" spreadsheet attachment 108589 [details] and select B1:C5 and sort by descending rank. The totals hours for the top and bottom half are given at the end. Attachment 108590 [details] shows how the “new” sorting behavior in 4.3, silently breaks spreadsheets causing them to give invalid results. For example it incorrectly calculates the top as taking 3 hours , 1 hour + 1 hour = 3 hours. This bug wasted days of work and made me look like an idiot, because I trusted the invalid results. I see you have now changed the sorting behavior of 4.2.7 to break more spreadsheets for users of the stable branch. This clearly shows that TDF does not care about business users. MS would not intentionally break interoperability, especially on a point release.
(In reply to stephanie park from comment #7) > Jean-Baptiste Faure, > > Please understand that this is an extremely simplified version of the model > I use at work. It ran for years perfectly in both Excel and until recently > Calc. It was not my question. My test file in bug 81633 ran perfectly for years in Calc too. > To run the model, you open the "good" spreadsheet attachment 108589 [details] > [details] and select B1:C5 and sort by descending rank. The totals hours for > the top and bottom half are given at the end. Thank you for your answer. I am still confused by your formulas in D8 and D9. I do not understand their link with "top" and "bottom". Are your sure that the formulas shouldn't be D8=B2+B3 and D9=B4+B5 ? That said it is clearly a duplicate of bug 81633. As a workaround you could do one of the following: 1/ go back to version 4.2.6.3 (if you do that take care of https://www.libreoffice.org/about-us/security/advisories/cve-2014-3693/ 2/ jump to the development version 4.3.4.0.0+ or 4.3.4 RC1 which will be released soon Best regards. JBF *** This bug has been marked as a duplicate of bug 81633 ***