-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Shareef Siddeek
Sent: Friday, January 04, 2002 1:22 PM
To: [EMAIL PROTECTED]
Subject: Excel2000- the same errors in stat. computations and graphics
Happy new year to all.
I frequently use Excel2000 for graphic presentation, spreadsheet maths,
simple nonlinear model fitting (using the Excel solver) with one or two
parameters, and simulations. I thought Excel2000 corrected those errors
found in the analysis tool pack and other in-built computational
procedures in the older 97 version. However, following articles point
out that the developers have done nothing to corrected those errors. I
would like your comments on this. Thanks. Siddeek
-------------------------------------------------------------------
1. I appreciate receiving your note and the URLs.
2. One really can't effectively use EXCEL without having to make the effort
of learning it from the books. Some of the complaints from Cryer have to do
with the fact that he never learned how to build charts in EXCEL. This
includes chart layouts, legends, scales, axis, labels, etc. One can use the
drawing overlay features to build up text on the charts. I always recommend
spending time reading the big commercial manuals available on EXCEL 2000. I
have several. EXCEL HELP is lousy for finding the information you really
need.
3. The EXCEL stat package was an add-on developer package by GreyMatter
International Inc, Cambridge, MA. back in the early 90's. Microsoft did not
write it. Being familiar with developers, the people writing the software
have to be familiar with an enormous lexicon of object links and protocols.
Stat is not one of the courses toward a degree in computer science.
Consequently much of the formula building comes from a convenient textbook.
I really am surprised at the developers/programmers out there that have no
knowledge of basic math, or how time works (calendar-time linkage). Much of
the problem has to do with the assumption that software built-in functions
work as the programmer thinks they work, not how they actually work. It is
obvious that Bill Gates has no interest in fixing EXCEL accuracy, only in
it's appearance and ability to fit in as a part of larger program packages.
His only interest now is .NET and the ability to pull off company data in
spreadsheet format using the internet as the company's internal network.
3. There is a problem with EXCEL histograms. This has been commented on in
previous edstat e-mails. In general EXCEL produces simple graphs, primarily
for business purposes. It does not produce good scientific graphics. All it
does is get you a quick graph with a minimum of effort.
4. Part of the inaccuracy problem has to do with the fact that each EXCEL
cell by default is treated as a variant variable. Unless you format all the
numerical cells properly (as decimal or integer), you are likely to have
problems. I Always format all my cells properly, declaring the type of cell
contents. If for example you are to precede a number by a space, EXCEL may
interpret the number as text. By use of the variant, empty cells can be
handled, and not cause computational halts.
5. The primary use of EXCEL is in business, doing the type of calculations
and reports described in Microsoft EXCEL User's Guide. In business
applications, accuracy is not that important, except when money is involved.
If for example if McCullough were to declare his numbers as currency instead
of variant, his accuracy would probably improve. Considering the type of
business applications for stat (for example see "The Complete Idiot's Guide
to Business Statistics") what EXCEL does is fine. From what I have observed,
many business type have a very limited math background, and even learning
simple business stat is a major problem. For example try getting them to
understand the difference between using z and t tests, and to understand
confidence intervals. Business people expect the computer to give them a
number. The statement by McCullough that "..it is important for the package
to determine whether the answer is likely to be corrupted by cumulated
rounding errors as to be worthless and if so, not to display the answer.
This policy is not acceptable to business types, and this is one of the
ongoing problems on the nets. They would rather get a wrong number, then
none. In most cases, the computed result is not the sole basis for a
business decision. (Please note here that these comments do not apply to
those in quality control, research or product improvement/development.)
6. The EXCEL solver was developed by Frontline Systems, at Incline Village,
NV. (Incline Village is an expensive skiing/condominium/housing area up at
the north end of Lake Tahoe. It was named for a huge inclined water 'trough'
that was used in the past to bring logs from the mountains down to sawmills
at the lake.) The solver algorithm has not been divulged, and obviously it
doesn't compare to better optimization methods developed since the 1980's. I
suspect there are several routines, each to fit a particular set of data and
value to be calculated. I would expect at the very beginning that any
non-linear regression problem would be beyond the scope of EXCEL.
7. Regarding McCullough's comment on RAND, there are two representations to
consider. The first is decimal as indicated on page 7, and the second is
binary, as a floating point number. In the later case the fraction would be
the mantissa. Most serious generators operate at the machine or binary level
using long integers (or floating point mantessa) with register shifts,
multiplications, additions, etc. However he is right on the lousy EXCEL
random number generator. To most user's, the importance of a good random
number generator is not important. If they win lotteries with EXCEL random
numbers, fine, it is a good one then. EXCEL is obviously not a package one
would do any serious simulations with.
8. I don't use EXCEL for any hard work. I write my own VB programs or EXCEL
macros/subroutines for data reductions. EXCEL is a great package for data
entry. ACCESS is a great package for output forms, data searching from many
tables and output reports (including EXCEL object charts).
DAHeiser
=================================================================
Instructions for joining and leaving this list, remarks about the
problem of INAPPROPRIATE MESSAGES, and archives are available at
http://jse.stat.ncsu.edu/
=================================================================
