GNOME Bugzilla – Bug 59144
Sorting ranges that contain formulas
Last modified: 2010-07-11 23:39:29 UTC
1. B2=1 C2=23 D2=C2/B2 2. B3=100 C3=10 D3=C3/B3 3. Select C2:D3 4. Sort Ascending D2 & D3 should not change values, D2 now has C2/B3 it should have C2/B2. I found this bug with a real spreadsheet i was working on and actualy sorted the wrong column, i say this because this happened to me with real work, not while trying to find bugs.
It isn't clear to me what the methodology here is. Investigation with XL is needed. * Does XL simply leave all cells with formulae alone. If so, what happens with a column containing both numbers and formulae? * If cells with formulae are moved, what transformation are they subjected to?
When I read this bug report the first time I had thoughts similar as Morten, but just now it occured to me what Chema is thinking of: Cells D2 and D3 contain the same (the identical!) formula since these are relative references! In both cases the formula really says: divide the number to the left by the number 2 to the left. Since these formulas are identical, sorting should not change this!
This is not a bug it was intentional. I just don't recall why. Sorting uses a flag specificly created for it that causes this. I'll need to go back over the logs to see what it was.
This seems to be outdated. Trying the original example yields exactly what Chema said should be. Can we close this?
Please don't close it. I'm leaving it as a placeholder to see if anything breaks. The cause of this bug was a very specific workaround for something. I don't recall what. Neither does JP. So I'll leave this here for a bit just in case.
Let me play devil's advocate: attached is an image showing 2 cols of values (A & B) and a column of formulas adding corresponding values from the A and B columns. Selecting just the values in column C and sorting descending yields in 1.0.5: a nicely sorted column of values (isn't this what we would expect?) in cvs head: no change (is this _really_ what we want?)
Created attachment 7332 [details] example case
BINGO! That was the example. Note to self. Make a note of this in the source and the Changelog next time. We need a better heuristic obviously.
*** Bug 82931 has been marked as a duplicate of this bug. ***
Created attachment 57964 [details] example of sort bug
I think I am hitting a bug or something related to this in gnumeric 1.6.1. I have a bunch of data on Sheet1 lets say, and I want to see that same data sorted on Sheet2. In gnumeric 1.6.1 when I select the Sheet2 data and click sort, nothing happens. In OO.org Calc, the same operation results in sorted data. Example above.
I think we are trying to use the wrong tools here. If we have a column of identical formulas (identical in the sense of "divide the cell to the left by the cell 2 to the left" even if they look different because the names of those cells differ) then sorting them should give us identical formulas. (IF these weren't formulas we wouldn't even consider anything else.) The issue arises that we really want to sort the values, not the formulas. In fact if formula re-evaluation changes the values we surely want to have the sorting changing appropriately. Just performing a regular sort on the values will never achieve that. On the other hand if we had a function sort, then we could easily place the sorted version of the region A1:A10 anywhere else by using =sort(A1:A10) or by wrapping the formulas in the area to be sorted by a SORT array function.
I have added a SORT function which can be used to sort function values. This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.