We're asking spreadsheets to do too much

For engineering projects, the calculation history is among the most important record to keep. Engineering data can be an organization's most valuable asset. Unfortunately too many companies fail to preserve such records and in the process, lose valuable intellectual property with every new project, resignation, or retirement. We see the use of spreadsheets for engineering calculations as contributing to the problem.

First, spreadsheets display calculation results but omit context. Missing are the methods, assumptions, values, and logic that spawned those results. Instead of seeing calculations laid out in conventional math notation, spreadsheets bury machine-readable text in formulas. Embedded equations and hidden macros are often difficult to decrypt.

Second, spreadsheets are inherently error prone. Rick Butler, an auditor who writes and speaks widely on the subject of spreadsheets, asserts that controlled experiments show 40 to 80% of spreadsheets contain errors at their inception. Spreadsheet developers miss more than 80% of their own errors, and outside testers miss over 50% of design logic and 34% of application errors. Frankly, it's astounding that engineers use spreadsheets at all for complex calculations.

The fact is 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 contain everything 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 components.

An electronic calculation "worksheet" can effectively do all of this. Unlike spreadsheets, electronic calculation worksheets employ mathematical notation. They capture — in human-readable text — equations and their context, including original concepts, underlying assumptions, graphs, text, annotations, sketches, data, and results. Organizations can organize, track, control and share worksheets in a Web-based repository. Calculations are retrievable for reuse, validation, refinement, reporting, and publishing, all in their proper context.

"In a world where products are becoming more complex and brain drain is a pressing concern, it's paramount to create, capture, and intelligently reuse engineering information," said Ken Versprille, Ph.D., PLM research director, CPD Associates LLC.

Such capabilities let companies immediately answer important questions as:

• Did we use the current requirement for cargo weight or the old one in this analysis?
• Can we give our vendors access to that calculation?
• Does the delivered document match the analysis that was actually performed?
• Has anyone done this calculation before?
• Did we get the units right?

The ability to answer such questions separates great engineering organizations from average ones and can make the difference between efficient product development and failure.

Mathsoft (mathsoft.com) is a maker of engineering-math software.