Spreadsheets: Often Do Not Add Up

Chris Randles, CEO and President, Mathsoft

The spreadsheet has evolved quite nicely in its 25 years from an accounting tool to a ubiquitous application for anyone working with data, including business managers, statisticians … and engineers. But are spreadsheets really the right tool for managing engineering calculation information?

Companies create a wealth of engineering data, and it's critically important to their success. For any engineering project, the history of all the calculations made—including their inputs, assumptions, methods, and results—is among the most important records of the endeavor.

Too many companies fail to preserve this record. They lose invaluable intellectual property with every new project, resignation, or retirement. They have great difficulty reporting their work to clients and regulators. They risk lives and property. And they have no way of reusing engineering information in other areas of the enterprise.

Spreadsheets are a big part of the problem.

Spreadsheets have provided fast, accurate computation since the advent of VisiCalc and Lotus 1-2-3, the seminal application that put PCs on corporate desktops. Spreadsheets became ubiquitous in large part because of their programmability, and the success of Microsoft Office means that a spreadsheet is available on virtually every PC shipped today. So, does that mean spreadsheets give us the truth—the whole truth? Unfortunately, no.

First, spreadsheets show answers but omit context. You can see results of a critical engineering calculation, but you don't get the methods, assumptions, values, and logic that spawned those results. Instead of seeing calculations laid out in conventional math notation, you see machine-readable text buried in formulas.

While spreadsheet cell structure hints at the logic behind the cells, that logic isn't explicit. Embedded equations and hidden macros are often difficult to decrypt. And though today's spreadsheet software can trace relationships between cells, retracing the steps is likely to be agonizing.

Second, spreadsheets are inherently error-prone. Rick Butler, an auditor who writes and speaks widely on spreadsheets, asserts that controlled experiments show that 40 to 80 percent of spreadsheets contain errors at their inception.

It's frankly astounding that engineers use spreadsheets for complex calculations at all. By their very nature, engineering calculations cry out for validation, verification, documentation, and traceability. Spreadsheets don't offer these capabilities.

All of this boils down to the fact that spreadsheets, though they have many viable uses in engineering organizations, are unsuited to the task of modeling, analyzing, and documenting engineering designs. Engineers need documents that explain all one needs to know about the design process—including text, interactive math calculations, graphs, and actual drawings and models—in a single, sharable document. The other necessary piece is a system for viewing, searching, reporting, and publishing these documents—and their components as well.

Tools like that help separate great engineering organizations from average ones and make the difference between efficient product development and failure.

Note: Mathsoft develops Mathcad® for engineering calculations, and Designate™ for viewing and searching Mathcad worksheets. Reach Randles at chris.randles@mathsoft.com .