On Tue, 6 Dec 2005 12:29:27 -0500, "Spencer E. Harpe" <[EMAIL PROTECTED]>
said:
> 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.
Thank you. I did not think I was exaggerating but I could have been.
And I was mistaken to include the SUM() in my complaint. As was noted
earlier it was performing as expected as Jan and Rick Shadow pointed
out. Whether or not is is doing what we want is a different matter.
> 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 noticed that but I actually had taken it as a comforting assurance
that the number was being considered to be text until I multiplied it by
a scalar and got a numeric answer. When I checked the Excel help I
found:
If you don't want numbers to be calculated, you can format them as
text. A number that is formatted as text
will be left-aligned instead of right-aligned. It will be stored as
text and cannot be included in any
calculation.
Tip Alternately, you can apply the Text format to the empty cells
and then type the numbers.
To use decimal places in numbers that are stored as text, you
must include the decimal points when
you type the numbers.
This is not completely true as the corresponding information from Calc
Help
You can format numbers as text in OpenOffice.org Calc. Open the
context menu of a cell or range of cells
and choose Format Cells - Numbers, then select "Text" from the
Category list. Any numbers subsequently
entered into the formatted range are interpreted as text. The
display of these "numbers" is left-justified,
just as with other text.
TIP When numbers are formatted as text, they cannot be used in
calculations or formulas.
> 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?
I believe it should be. If nothing else an illegal computatation that
does work in Excel (e.g. 5*'99=495)and which may actually give the
"correct" answer in Excel, will give quite different results in Calc
(5*'99=5) which could play merry hell with spreadsheet results.
A bit of further experimentation shows that Quatro Pro and Calc behave
similarly but that Excel does not. I have not checked others.
-----
John Kane
Kingston ON Canada
[EMAIL PROTECTED]
(613)888-2399
--
http://www.fastmail.fm - I mean, what is it about a decent email service?
