Re: Excel ate my DNA!

[Helmut Wollmersdorfer <helmut wollmersdorfer gmx at>]

Morten Welinder schrieb:
> Helmut Wollmersdorfer wrote:

> > Unchanged will _never_ be wrong.

> Let's see:

> A1 = 1-dec-2004
> A2 = 24-dec-2004
> A3 = A2-A1

> --> #VALUE! error.  File a bug, get "works as expected" answer, move on
> to next spreadsheet program.  

1) In your example this are dates and cannot be anything else - very 
unlikely.
2) A program is thinkable, which works with date calculations based on 
"D-MMM-YYYY".
3) By "unchanged" I mean, that an input of e.g. "1-dec-2004" should not 
be converted to e.g. "01.12.2004" automatically. In this case it's not a 
problem. But think about the other examples like input 01/04, which is 
converted by _Excel_ (not Gnumeric) into 01.04.2004. Reverting is not 
possible. 01/04 can mean anything: 1st quarter of 2004, 1st month of 
2004, 4th month of 2001 ...

> (Actually following the "unchanged" rule
> completely you would see "=A2-A1" as text; useful for a word processor,
> not for a spreadsheet.)

It's very unlikely, that the most used tag "=" is used for the beginning 
of text _and_ a user does not know the special meaning of "=".

> People have come to expect that spreadsheets "do the right thing" with
> data.  

But what is "the right thing"? If there is more than possibility to 
interpret a given input, you need rules to decide "the right thing". And 
there is no guarantee against conflicting rules.
Let me list some rules (my brainstorming, not a solution):
1) Keep it as near as possible to users input. The user is the king and 
is always right. The user (maybe) has a reason to input 1-dec-2004 and 
not 01.12.2004.

2) Interpret it in the most likely way. "1-dec-2004" will mean a date in 
 99.999 percent of the cases, but e.g. "01/04" has more than 4 possible 
interpretations, each of them below 25 percent likelyhood.

3) Using more artificial intelligence needs interpretation of context 
and needs user decisions. Maybe presenting a proposal to the user in the 
case of "autoconversion" is a good solution. Compare it to 
autocompletion or spell checkers.

4) Turning off or configuring automatic behaviour should be possible - 
on different levels.

5) The rules for autoconversion should be easy and well documented.

> For example it is expected that "4e5" means the same as 40000

Good example.
There are 3 possibilities:
1) It means text. Very unlikely, because a spreadsheet mainly deals with 
numbers.
2) The user enters "4e5" because he thinks in exponential formats. Excel 
assumes this and formats AFAIK to "4.00 E+5.00".
3) The user enters "4e5" as shortcut for "40000", and wants "40000" to 
be displayed. Gnumeric assumes this.

> and the biometrics researchers clearly haven't thought their do-not-
> interpret request through.

That's the main question. Should it be necessary to turn off or to turn on.

> If you want something not interpreted you simply poke a single quote in
> front of it.

I know. But beginners don't know.

> And don't use .csv files (or any other type-less format) for such data.

No problem, if somebody knows what he does on import and on export. And 
if he knows the disadvantages and risks of .csv, or generally "tag 
separated" formats.

> Use .xls or .gnumeric instead.

> (On top of that I have the feeling that these guys are using a spreadsheet
> to do a database's job.)

I see text only spreadsheets very often. There seems to be a strong need 
for a simple tool to enter and edit tabular data. Databases are to 
complex for the simple user.

Maybe these guys use a database, but export to .csv for public download, 
in conjunction with an easy spreadsheet tool. Not unusual in the 
scientific area.

Helmut Wollmersdorfer