* Sometimes Microsoft Excel format conversion functions should be disabled
A tool can be the best in its class, but if it is misapplied, it can cause havoc. Imagine the likely damage if a 4-year-old were to use a top-quality surgical scalpel to peel an orange. Now consider the following story that appeared in Risks 24.19.
Risks moderator Peter G. Neumann noted that biomedical researchers had reported in 2004 that Microsoft Excel converted some gene names into dates, damaging data sets and causing rejection of the damaged data. The authors listed 30 gene names, such as DEC1, that got converted to dates (e.g., to Dec. 1). A worse problem occurred when data identifiers contained the letter E in a string of digits; these identifiers were irreversibly converted to floating point numbers in scientific notation.
The authors wrote:
“There is another default conversion problem for RIKEN clone identifiers… of the form nnnnnnnEnn, where n denotes a digit. These identifiers are comprised of the serial number of the plate that contains the library, information on plate status, and the address of the clone. A search… identified more than 2,000 such identifiers out of a total set of 60,770. For example, the RIKEN identifier ‘2310009E13’ was converted irreversibly to the floating-point number ‘2.31E+13.’ A non-expert user might well fail to notice that approximately 3% of the identifiers on a microarray with tens of thousands of genes had been converted to an incorrect form, yet the potential for 2,000 identifiers to be transmogrified without notice is a considerable concern. Most important, these conversions to an internal date representation or floating-point number format are irreversible; the original gene name cannot be recovered.”
Neumann commented:
“If some computer virus or Trojan did this sort of damage to the results of thousands of high-cost biomedical experiments, I imagine that we’d see a serious effort to put some people in jail. I’m not suggesting that any similar sort of retribution is appropriate here, but perhaps some rehabilitation would be in order.”
Warn your users to turn off automated format conversion functions in Excel (or other spreadsheets) when working with production spreadsheets where complex alphanumeric codes are to be entered. It would be better to note and correct an error than to have the software silently make assumptions and modify their input, resulting in data rejection or – worse – acceptance of faulty data.
In Excel, they can use the menu sequence Tools | AutoCorrect Options | Autocorrect and uncheck the options for “Correct TWo INitial CApitals,” “Capitalize first letter of sentences,” “Capitalize names of days,” “Correct accidental use of cAPS LOCK key” and “Replace text as you type.”
Next, use the Tools | Options | Edit sequence and uncheck the “Enable AutoComplete for cell values” box. The “Enable automatic percent entry” is also worth unchecking because it has two different rules in effect. With that option enabled, input numbers _greater_ than 1 are _divided_ by 100; e.g., entering 10 stores the value 10% (i.e., 0.1) and entering 1 stores 1% (i.e., 0.01). However, numbers _smaller_ than 1 are _not_ converted; thus 0.1 is stored as 10% and 0.01 is stored as 1%. As you can see, there are two different numbers that can result in the same stored value (yecchhh). If the data contain numbers that cross the boundary between these (not particularly obvious) rules, the numbers stored in the spreadsheet will not be those intended by the operator.
As you can understand, putting this kind of tool in the hands of a novice is almost certain to end in tears, much like the scalpel in the hands of the child.
