edge of sheet : clip / wrap / #REF! inconsistencies
Submitted by John Denker
Link to original bug (#765512)
Description
Created attachment 326648 demonstrate inconsistent, unfriendly, unsafe sheet-edge behavior
This is a follow-on to bug 765438. This requires a gnumeric version later than: commit e8b6151c Author: Morten Welinder terra@gnome.org Date: Sun Apr 24 16:07:07 2016 -0400
Recipe:
- Start a fresh recent version of gnumeric. Note that the chart is 256 columns wide. In cell B1 enter the formula =offset(A1,0,253) Fill across all the way to I1. Observe that cells that represent off-chart cells evaluate to #REF. This is the expected and desired behavior.
For this expression, copy/paste produces the same results as fill-across, which is the expected and desired behavior
-
Next, copy cell B1 and paste it into cell A1. Observe #REF! This is not just the value of the expression; part of the newly relocated expression has been changed to #REF! Again, this is expected and desired behavior
-
Next, in cell B3 enter the formula =columns(IQ1:IT1) Fill across all the way to I3. Observe that ranges that straddle the edge of the sheet get wrapped around, forming very large inverted ranges. From a GUI point of view, this is undesirable, in that it violates the principle of least surprise. There is essentially zero chance that this is what the user wanted. From a general coding point of view, it is undesirable, in that it is not defensive. If the formula cannot do what the user intended, that does not make it OK to return a numerically-incorrect answer. Also, it is not consistent with the offset() function.
The desired behavior is that filling should never wrap around. a) If a newly-relocated formula can be represented in a reasonable way, it should be left alone. If this means it evaluates to #REF!, that's fine. b) If a newly-relocated formula cannot even be represented, for example if it is syntactically impossible, the formula (or the relevant part thereof) should be replaced with #REF!, as in scenario (2) above.
- In cell B5, enter the formula =columns(IQ1:IT1) Copy that cell, and then paste it into C5, then D5 ... all the way to I5. This is the same as scenario (3), but using copy/paste instead of fill. Observe that some of the cells that would have overlapped the edge get clipped so that they don't overlap. From a GUI point of view, this is undesirable, in that it violates the principle of least surprise. There is essentially zero chance that this is what the user wanted. From a general coding point of view, it is undesirable, in that it is not defensive. If the formula cannot do what the user intended, that does not make it OK to return a numerically-incorrect answer. Also, it is inconsistent with the offset() function, and inconsistent with the fill feature.
The desired behavior is that copy/paste should never munge the formulas. Ranges that bump up against the edge should not be clipped. a) If a newly-relocated formula can be represented in a reasonable way, it should be left alone. If this means it evaluates to #REF!, that's fine. b) If a newly-relocated formula cannot even be represented, for example if it is syntactically impossible, the formula (or the relevant part thereof) should be replaced with #REF!, as in scenario (2) above.
Here is what my spreadsheet looks like:
#REF! 0 0 0 #REF! #REF! #REF! #REF! #REF! =offset(A1,0,253) 4 4 4 254 254 254 4 4 =columns(IQ1:IT1) 4 4 4 3 2 1 #REF! #REF! columns(IQ1:IT1)
See the attached .gnumeric file, which contains a page of live code and a page of paste-by-value observed results.
To repeat: The behavior of the offset() function makes sense. The corresponding fill-across behavior and copy/paste behavior seem inconsistent, unfriendly, and unsafe.
The design principle should be, if we can't do what the user requested, we should throw an exception (e.g. #REF!). We should never surreptitiously do something that looks like the requested calculation but isn't.
Attachment 326648, "demonstrate inconsistent, unfriendly, unsafe sheet-edge behavior":
odd-fill2.gnumeric
Version: git master