I agree with John here that it's a pretty big issue, especially since more
and more people seem be using Excel (or spreadsheets) for formal statistical
analysis (and the fact that the scientific community is ok with it). I've
made it clear to graduate students and residents that they have to use a
"real" statistics package, not Excel, to analyze their project data.
It seems that Calc is more consitent with things than Excel is (see John
Kane's original message and his test calculations). I was using Excel 2003
also, but in my program, a number entered as text (999 in John's example)
was converted to a number. It was flagged as an error ("Number entered as
text") so the program knew it happened but it seemed to ignore it (and I
never told it to do that). I do find it rather frightening that in Excel
the text number '999 was converted to an actual number 999 and calculations
proceded ignoring the fact that it was NOT input as a number. [IMO, that's
just another example of how Office thinks it knows more about what's going
on than the user.] Granted, this difference could very well be operator
error on my part.
On the up side, it's good (or, less bad, shall we say) that Calc is
consistent (no re-formatting of numbers entered as text). And it is good
that the average and sum functions are treating the text or "text numbers"
as missing and ignored rather than assuming it is a 0 and throwing off the
calculation of the average, which can come in handy as Jan noted; however,
I'm not sure that all users are aware of this issue. I do feel it is very
important that this be noted in the Users Guide. Should it also be
mentioned in the Migration guides or some guide that discusses differences
between Calc and Excel?
Have a great day!
seh
================================
Spencer E. Harpe, PharmD, PhD, MPH
Assistant Professor of Pharmacy
VCU School of Pharmacy
P.O. Box 980533
Richmond, VA 23298
(O) 804-828-3245
(F) 804-828-8359
-----Original Message-----
From: John Kane [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 06, 2005 8:34 AM
To: authors OpenOffice Authors
Subject: Re: [authors] Serious problem in Calc that we should address in the
Guide
On Tue, 6 Dec 2005 05:16:41 +0000, "Jonathon Blake"
<[EMAIL PROTECTED]> said:
> John wrote:
>
> >Multiplying/adding a string variable by a number should be
> >impossible
>
> Sometimes test strings are used for null values.
> For example, if something is not scorable, rather than putting "0",
> which will throw numbers off, the entry should read "not scorable".
>
> All calculations involving that cell should silently the value of the
> cell.
>
> > easy way to convert from text to numerical but I think an automatic
conversion is almost certain to cause errors.
>
> Is., and has caused errors. [somebody published a paper on the number
> of errors in _The Human Genome Project_, because Excel turned every
> genome that contained an "E" into a number, and thus corrupted the
> data.]
You wouldn't happen to have a reference to that paper would you? I find
spreadsheets very useful but I would not use them for complicated analyis or
data base purposes if I had any choice. I like to collect ammunition to use
when I make such a suggestion to others.
> [Whilst that is an example of the stupidity of the people that
> participated in that project, it is indicative of how common the total
> lack of thinking about appropriate tools is.. And unfortunately,
> spreadsheet developers have to take into consideration the lowest
> common denominator (IE Thinking skill level is zub zero) ]
I think you may be a bit harsh here. It may be a case of if the only tool
you have is a hammer then every problem looks like a nail. I have known
people to do their résumés and correspondence in a spreadsheet because it
was the only computer tool they knew how to use.
Unfortunately in this case I think the spreadsheet developers made some very
serious wrong decisions.
-----
John Kane
Kingston ON Canada
[EMAIL PROTECTED]
(613)888-2399
--
http://www.fastmail.fm - Access your email from home and the web
