[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Off-topic: Excel errors?

PureBytes Links

Trading Reference Links

Fwd from another list. I think it's relevant since we all more or less
use Excel.

-------- Original Message --------
Subject: Re: Fwd: Excel errors?
Date: Sat, 12 Jan 2002 11:10:59 -0600

I use Excel 97 SR-1 for a number of tasks, for one, to maintain 
my bank account balance, and I can't tell you how many times I've 
had real goofy stuff happen.

For instance, many times I'll paste the values (Edit Copy, 
then Edit Paste Special, Values) back over a column of 
formulas once the account has been reconciled with the 
bank; many times I'll find that these two-decimal-place 
numbers have somehow been magically rounded off to the 
nearest several hundred nano-centavos way out at the 12,
13th and 14th decimal place.  And there was never any
source for the round-off error to begin with, since the
most complex operation conducted in this sheet is adding
or subtracting numbers with two-decimal-places, and I 
make sure they are formatted correctly before I start.
This stuff shouldn't happen!

Although I can't reproduce it regularly, one time I started 
off with the value of 10 in cell A1.  In the ten rows below 
that, I pasted the formula = (A1 - 1).  A school kid could 
have quickly figured out that cell A11 should equal to ZERO.  

Excel's answer?  Something like = 10*E^-14.

A friend of mine, now retired, was a process engineer for a 
major oil company.  This is a guy who was building huge chemical 
process plants back before there were PCs.  He is very bright, 
and he is also VERY computer savvy.  He tried to use Excel early
on for his process control calculations, and has said many times
that it is "fatally flawed."  In fairness, he has retested his
process control calculations in later versions of Excel, with
the same conclusion.

I don't care if stuff like this does get fixed down the road.
We're 12 years into the Windows version of Excel, and while
it's a very cool app in many, many ways, that's long enough.

More than I meant to write.

- Howard Brazzil

ztrader wrote:
> Is this still true?
> This is a forwarded message
> From: William C. Case <wcc@xxxxxxxxxxxxx>
> Date: Saturday, January 12, 2002, 12:23:43 AM
> ===8<==============Original message text===============
> 2 comments on Excel (& spreadsheets in general):
> 1. I worked (for 10 years) with LIMS software at a Swedish gov't DNA
> laboratory.
> The researchers there used Excel for statistical work, etc.
> Excel then (98?) had a number of potentially very serious calulation faults
> (yes, it would do the arithmetic  _wrong_  in certain situations, that's
> _exactly_  what I mean.)  M$ gradually admitted & corrected most of them,
> except for the ones that were considered to be "features", in M$-speak.
> These are/were well documented at an Aussie "Windows Watcher" (or something
> like that) site, & I have them archived somewhere.
> 2. It is a consensus in the LIMS community that spreadsheet routines are not
> possible to validate in any reasonable way (mainly because of the large no.
> of uncontrollable parameters affecting the way they work - compare the
> statistical expose I posted about earlier, showing why trading systems with
> large no.s of indicators cannot be effectively validated.), and thus
> _cannot_ be included in any reallife LIMS software, which must for example
> meet FDA approval, etc.
> 3. Excel has loads of goodies that you could no-way invent yourself or find
> in other software packages. Many of them (e.g. pivot tables) are little-used
> because they require some hard work to understand & use correctly. I could
> do all kinds of great statistical graphing thingies for the researchers in
> Excel. (Now they use SAS I believe - it's for more serious, heavyduty
> stuff.) But no-one ever seriously considered using the Excel stuff in any
> kind of "production" environment, you just can't trust it that far, period.
> ===8<===========End of original message text===========