We're asking spreadsheets to do too much

June 2, 2005
Spreadsheets have evolved over the last 25 years from an accounting tool to a ubiquitous application for anyone working with data, including business managers, statisticians, and engineers.

Chris Randles
CEO and President
Mathsoft Engineering & Education Inc.
Cambridge, Mass.

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.

Sponsored Recommendations

The entire spectrum of drive technology

June 5, 2024
Read exciting stories about all aspects of maxon drive technology in our magazine.


May 15, 2024
Production equipment is expensive and needs to be protected against input abnormalities such as voltage, current, frequency, and phase to stay online and in operation for the ...

Solenoid Valve Mechanics: Understanding Force Balance Equations

May 13, 2024
When evaluating a solenoid valve for a particular application, it is important to ensure that the valve can both remain in state and transition between its de-energized and fully...

Solenoid Valve Basics: What They Are, What They Do, and How They Work

May 13, 2024
A solenoid valve is an electromechanical device used to control the flow of a liquid or gas. It is comprised of two features: a solenoid and a valve. The solenoid is an electric...

Voice your opinion!

To join the conversation, and become an exclusive member of Machine Design, create an account today!