I (and others) have written previously of the danger of using Excel for
anything more than trivial data handling. As further evidence that this
is not sheer anti-MS prejudice, here is a genuine happening from today.
A colleague gave me a spreadsheet with two sets of annual data. The
obvious way to compare them was to bring equivalent figures together, so I
inserted a column and typed in a group variable. The data then looked
like this, where ... indicates omitted rows and "val" a data value:
[Col headings] group year x1 x2 etc
1 1960 val val
1 1961 val val
1 1962 val val
...
1 2000 val val
2 1960 val val
2 1961 val val
2 1962 val val
...
2 2000 val val
Tools / Sort on year then group, and the result was a sheet with years
[Col headings] group year x1 x2 etc
1 1960 val val
2 1960
1 1961 val val
2 1962 val val
1 1963 val val
...
2 2040 val val
I assumed [sic] I had blundered, so did "undo" and the years went back to
their previous values. Having convinced myself that sorting the data
changed the values, the reason was quickly spotted. The values were as
seen, but were generated by a formula, with [cell above+1] except when the
year was 1960. Freeze the values (I leave it as an exercise to track that
down in the Help system, with the comment that the menu and toolbar are
inconsistent) and the data could be sensibly sorted.
I do not see how anyone can rely upon results from a program that allows a
sort operation to corrupt the data and not even issue a warning. This was
only spotted because it was in the column being sorted. Presumably any
formula relying upon data from other rows (eg annual differences) would be
similarly rendered into garbage.
R. Allan Reese Email: [log in to unmask]
|