derosa@motcid.UUCP (John DeRosa) (03/01/91)
I ran into a problem with Excel that I was sure was a bug. I was copying some cells from one spread sheet into another. I selected 20 cells, copy and then paste and ended up with 30 cells in the receiving spread sheet. THIS MAY SOUND SIMPLE ENOUGH BUT THE UNDERLYING PROBLEM CAN CAUSE SOME MAJOR LEAGUE CALCULATION ERRORS. I finally determined what the problem was after hours of head pounding. It all comes down to the mis-use of a feature of Excel. Most Excel users know that you can split the Excel window into 1, 2 or 4 windows. This is done by clicking and dragging on the black strip just above the "up" arrow or the strip just to the left of the "left" arrow. Another commonly used feature is changing the width of a column by clicking and dragging the column boundry. While this feature can change the height of a row, it is seldom used as the height of a row is automatically adjusted depending on the font point size. (BTW, I have been told that V3.0 will have automatic Column width) THE PROBLEM is that a row or a column can be shrunk to ZERO WIDTH. This makes a row or column disappear (visually) while still being active for copying and (worse) math. If you perform a sum on a row of cells, this calculation will include these invisible cells, throwing off your math. This might not be entirely obvious if the calculations are complicated. We found a $200,000 error in a $3,000,000 calculation caused by this problem, i.e. data was entered twice as we thought the information was missing. With a copy and paste, the invisible cells are copied and when pasted, appear in the receiving spread sheet. -- = John DeRosa, Motorola, Inc, Cellular Infrastructure Group = = e-mail: ...uunet!motcid!derosaj, motcid!derosaj@uunet.uu.net = = Applelink: N1111 = =I do not hold by employer responsible for any information in this message =
mdresser@pbs.org (03/02/91)
In article <5924@crystal9.UUCP>, derosa@motcid.UUCP (John DeRosa) writes: > I ran into a problem with Excel that I was sure was > a bug. I was copying some cells from one spread sheet > into another. I selected 20 cells, copy and then paste > and ended up with 30 cells in the receiving spread sheet. > > THIS MAY SOUND SIMPLE ENOUGH BUT THE UNDERLYING PROBLEM > CAN CAUSE SOME MAJOR LEAGUE CALCULATION ERRORS. > > I finally determined what the problem was after hours of head > pounding. It all comes down to the mis-use of a feature of Excel. > > Most Excel users know that you can split the Excel window > into 1, 2 or 4 windows. This is done by clicking and > dragging on the black strip just above the "up" arrow > or the strip just to the left of the "left" arrow. Why is this a problem? I think this is a great feature. > > Another commonly used feature is changing > the width of a column by clicking and dragging the > column boundry. While this feature can change the height > of a row, it is seldom used as the height of a > row is automatically adjusted depending on the font > point size. (BTW, I have been told that V3.0 will have automatic > Column width) > > THE PROBLEM is that a row or a column can be shrunk to > ZERO WIDTH. This makes a row or column disappear (visually) > while still being active for copying and (worse) math. I'm sorry, but I don't see this as a problem. I frequently want to close a row or column so it is not visible. If a user thinks they may have accidently closed up a few rows, you can select all and set the row height to standard. Being aware of it is good, but it is not to considered a *problem*. > > If you perform a sum on a row of cells, this calculation will > include these invisible cells, throwing off your math. > This might not be entirely obvious if the calculations > are complicated. We found a $200,000 error in a $3,000,000 > calculation caused by this problem, i.e. data was entered > twice as we thought the information was missing. Sometimes I want to hide certain figures and include others without having it visually mess up a printout, which is one reason I like being able to set a row or column to zero. > > With a copy and paste, the invisible cells are copied and > when pasted, appear in the receiving spread sheet. > -- > = John DeRosa, Motorola, Inc, Cellular Infrastructure Group = > = e-mail: ...uunet!motcid!derosaj, motcid!derosaj@uunet.uu.net = > = Applelink: N1111 = > =I do not hold by employer responsible for any information in this message = Again, be aware of it, but learn to use it to your advantage. Melanie Dresser -- ************************************************************** Melanie L. Dresser mdresser@pbs.org Bruce = Faithful Companion human male Max = Noisy, mouthy, siamese feline male Aravis = Quiet, neurotic siamese feline female Tristan = Big, silly German Shepherd canine male Quest = Trusty steed - Appaloosa equine male ********"Is it expensive? I got a horse ta feed, ya know"*****
blm@6sceng.UUCP (Brian Matthews) (03/02/91)
In article <5924@crystal9.UUCP> derosa@motcid.UUCP (John DeRosa) writes: |THE PROBLEM is that a row or a column can be shrunk to |ZERO WIDTH. This makes a row or column disappear (visually) |while still being active for copying and (worse) math. It's not a problem, it's an extremely useful feature. I can fit large amounts of data on the screen by eliding cells containing intermediate data or calculations I don't need to see. Just because you don't know how to use it doesn't mean it's a problem with the program. -- Brian L. Matthews blm@6sceng.UUCP
derosa@motcid.UUCP (John DeRosa) (03/03/91)
Well, several people have written to say that this
ability to reduce columns and rows to zero width
or height is a feature and not a problem.
This article was a warning of a potential pitfall
of Excel, not a bug report. Geeeeze.
I AGREE, this is a nice feature, but can also be
an easy way to really mess up a novice or even
not so novice user. If you are unaware that
some columns or rows are invisible, even if
you know about this feature, you can get some
very erroneous results. In a large spreadsheet
this can readily be lost in the details.
It is far too easy to be selecting a group of rows
by the click and drag approach and inadvertantly
click on the boundry between two rows and, in the
twinkling of an eye, reduce multiple rows of
data to invisibility.
IMHO I believe that some sort of tag should be
shown, indicating that rows or columns are
hidding. Anyone seen V3.0 yet?
"Once bitten, twice shy", but I bet that I get
stung again by this problem.
--
= John DeRosa, Motorola, Inc, Cellular Infrastructure Group =
= e-mail: ...uunet!motcid!derosaj, motcid!derosaj@uunet.uu.net =
= Applelink: N1111 =
=I do not hold by employer responsible for any information in this message =