Description: References to cells that are not included within the range of rows being sorted are changed but should not be. The results of the calculations become incorrect. Steps to Reproduce: 1.In the first column of a spreadsheet, enter identifiers, such as chemical compound names. For example, use ten rows. 2.In the second column, enter a property of the name in the first column, such as amount 3.After all the rows are entered, compute the sum of the second column in a new row, for example row 11. 4.In the third column, compute the percentage that the second column accounts for, for example, compute C1 as =$B1/$B11*100, C2 as $B2/B11*100, etc. 5.Enter a fourth column, such as a melting point. 6. Sort a subset of the rows, definitely not including the total row (B11 in the example), on the fourth column in either ascending or descending order. Actual Results: The references to cells in the denominators of the formulae in the third column are changed, even though the rows specified by those references are outside the range to be sorted. Expected Results: References to cells within the range of rows being sorted should be changed according to the sorting result. References to cells not within the range of rows being sorted should not be changed. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.0.4.2 Environment: CPU threads 4; OS: Linux 5.1.10 User Interface: UI render; default; VCL; kf5 Locale: en-US (en_US.UTF-8); UI: en-US Misc: Debian package version: 1:7.004-4+deb11u1 Calc: threaded
Created attachment 182454 [details] Test case. Sort rows 4-23 on column G to demonstrate the problem described in the report. Examine the formulae in rows 4-23 of columns D and F before and after sorting.
Created attachment 182470 [details] Fixed file Hi, the result you're getting is the expected result since you locked the cells incorrectly. You'll get the same result in Google Sheets or Excel with these formulas. To fix your spreadsheet, use the following formulas (I also attached the fixed file, which can be sorted by column G as expected) In column D use "=C4/$C$39*100" In column F use "=E4/$E$39*100" Tested with Version: 7.4.1.2 / LibreOffice Community Build ID: 40(Build:2) CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb) Locale: pt-BR (pt_BR.UTF-8); UI: en-US Ubuntu package version: 1:7.4.1~rc2-0ubuntu0.22.04.1~lo1 Calc: threaded
Thanks for the informative comment and correcting my ignorant mistake.