Dear Assume,

An interesting discussion. I fully agree with Francois.

One strength and weakness of spreadsheets in general is that a beginner with
very little knowledge can come to a quick result.
  This is motivating for further spreadsheet use but at the same time
demotivating for in depth learning of spreadsheet capabilities.

Most of the problems I encounter with students is the undisciplined
"spaghetti"-style spreadsheet. Some of us used Fortran in our younger days
and were warned against spaghetti programming. In spreadsheets there is not
any obligation to structured use but as much as possible references in
formulas should be to cells above (and to the left) as a step to
"structured" formulas.  "Divide and rule" in formula making is advised; do
not try to pull a stunt with the longest formula possible in one cell but
use a few more cells or columns. A user defined function (UDF) is often the
most elegant solution for a more complex formula; etc..

The current discussion on sorting is yet another example where spreadsheet
in general allows a user to break some elementary rules. To sort formulas
goes against common sense.

Another comment nothing to do with sorting: Open-office (free) and
Star-office (free for educational institutions) have come to a degree of
maturity very close to other alternatives and are cross-platform, so
MS-Office with Excel is not anymore compulsory...


Kind regards,

Guido Wyseure








-----Original Message-----
From: Association of Statistics Specialists Using Microsoft Excel
[mailto:[log in to unmask]]On Behalf Of Francois Sermier
Sent: 10 December 2003 00:36
To: [log in to unmask]
Subject: Re: Response to comments on sorting


hello to all,

this is my (humble) contribution to this "classical" debate on Excel and
statistics. Or, better said on the statisticians that reprove the use of
Excel...

1. One of the main problems is that, for historical reasons, Excel (and
spreadsheets in general, this came before Excel was born) there is a
tradition of despising Excel for its various dangers.

If you remember, there was a case were someone sued Lotus (long time ago, it
was 1-2-3) for the loss of money he suffered, having inserted rows before
(or after, I don't remember) a block of rows that were totallized using a
@SUM function. He argued that nowhere in the documentation the "problem" was
mentionned. (no joke !)

So, spreadsheet, in prehistoric DOS times, were considered by "serious"
people (like IT persons and others) as a toy for users. Imagine, when the
mouse entered the scene...

This diffuse opinion is still lurking in the initial trainings to Excel
(when there is any, it'so easy to use...)

2. Now, in my opinion, Excel is a fantastic tool for Data Management, and
people feel it, that's why so many people use it. But the main problem is
that when you start with a blank spreadsheet, you are facing a "big
nothing", you can do absolutely what you want (or what you know) and, of
course, many of what you can do may be completely stupid or irrelevant. In
fact, I think that the "non structured" use of Excel, which is the main way
it is used generates a HUGE loss of productivity : people spend weeks to
reinvent the wheel (I met many examples, in various companies).

Excel is a real programming language, and I am talking of the spreadsheet,
not the VB environment, and should be considered as such. The paradigm is
not so evident and if you don't agree with the "conceptual model" (hello and
thank you Erich), or if you think that only garbage (or worst, occasionnaly
garbage) can come out of it's use, then leave it to others. As we say in
french : if you don't like don't disgust the others. Think, for example at
all the horribles things that may happen when you forget a semi-colon in a
SAS data step !

3. Now to the seminal fact :
As Erich says, when you use relative references across rows, you are
supposed to know what you do. When you tell "increment the preceding cell
(the row above)", you say exactly that. If you modify the ordering of the
rows, what you get is *still* "increment the preceding cell". But, horror,
the preceding cell's value has changed! well YOU moved the rows, and you're
supposed to know what you are doing.

> Several people (see below) have written along the lines of "caveat emptor"
> or implying that this is a feature and therefore allowable.  I can only
> say I profoundly disagree that the current case falls in this category.
> It is true that "computers do what you tell them, not what you only want"
> but the Excel sort function as implemented is counter-intuitive and
> inherently unsafe with no warning.  Software follows rules of logic, but I
> see no reasonable justification for having a sort command that silently
> changes the data it is working on.

Sorry for you, but the logic is very clear : when you sort formulas, you
sort formulas, not the values they compute. If the result of your formulas
depends on the ordering of the rows, then the values change. This is how
spreadsheets do, and again, if you "disagree profoundly", then either freeze
your formulas, either rethink to the conception of your sheet, either don't
use spreadsheet!

Now, in a certain sense, I agree with you: generating numbers like in your
example is a (very) bad practice and the one who generated them should have
done the Copy/Special Paste Values to "freeze" the values. But please don't
mix "bad practice" and "bad software". When people align text inserting
spaces in a word processor (yes, some still do this kind of things), this is
a very very bad practice, this is NOT a flow in the software.

Sorry for the flame, but please don't "Excel this, Excel that" when in fact
it's "non-aware user this, non-aware user that..." (thank you Cor!)



François Sermier



Le 9/12/03 13:19, « Allan Reese » <[log in to unmask]> a écrit :

> I have received several comments either on or off lists.  Perhaps the most
> interesting is from: JARROD FRANKLIN <[log in to unmask]>
>> tried your example and had no difficulties at all. I used my own values
>> in x1 and x2 and sorted it as you said with column headings, the same
>> years and groups and it came out fine.


         ---- CLIP, CLIP ------------


>
> Finally, I'll agree with Paul Rockett's comment "very good most of the
> time", but so was the curate's egg.  If we all "knew what we were doing",
> there would never be car accidents caused by drivers who "didn't see the
> oncoming car" or "misjudged its speed"; there would be no need for safety
> guards on machinery.  Perhaps I should refer the matter to the Health &
> Safety Executive - if only under "mental health hazards at work".
>
> R Allan Reese
> ------------------------------------------------------------------------
>