Re: sorting "bug"

[Prof J C Nash <nashjc uottawa ca>]

Whether this is a bug or a feature or ?? is not clear. What is clear is
that it is TROUBLE. My colleague Andy Adler sent me a msg pointing out
that spreadsheets (OO, Gnumerice, Excel) all take the sheet

        A      B
1       3      1
2       2     =B1  (<- set to 1)
3       1      3

and sort it on A to

        A      B
1       1      3
2       2     =B1 (<- set to 3)
3       3      1

It is also interesting to sort on B.

Longer examples show that the cell with the reference can be moved to 
where it makes no sense and gives a #REF error.
For example:

A     B
1     6
2     7
3     6 = B1
4     9

and sort A in reverse order. Gives an error as
=B1 points to =B0 (doesn't exist).

The sorting is taking the data in the cells and ignoring the reference.

The big danger is when we don't get any indication that we've made a 
reference, but the data is changed, as in marks for 600 students in my 
statistics for business courses. Nasty!

What should we be doing?

1) formulas with references INSIDE the sort area should not be sorted. 
Indeed, one could say that sort should pre-check this and red-flag it.
As far as I know (I'd be delighted if there is a way to do this), no 
spreadsheet does this yet.

2) formulas with references OUTSIDE the sort area should be OK.

3) references across a row for row sorts or down a column for column 
sorts should be OK, but we should warn the user that the references are 
there. (yellow flag)

Burns in his Spreadsheet Addiction paper 
http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html
warns of this danger but in more general terms.

Is this worth putting into the bugs system? Or is it a feature request?
Or ???

J Nash

--
John C. Nash, School of Management, University of Ottawa,
Vanier Hall 451, 136 Jean-Jacques Lussier Private,
P.O. Box 450, Stn A, Ottawa, Ontario, K1N 6N5 Canada
email: nashjc on mail server uottawa.ca, voice mail: 613 562 5800 X 4796
fax 613 562 5164,  Web URL = http://macnash.admin.uottawa.ca
"Practical Forecasting for Managers" web site is at
http://www.arnoldpublishers.com/support/nash/