Can spreadsheets work for engineering?

Outside of engineering, the spreadsheet has evolved nicely in its 25 years from an accounting tool to an application for anyone working with data, including business managers, bookkeepers, and statisticians. But spreadsheets do little to capture the wealth of engineering data critical to an organization’s success. For any engineering project, the history of all calculations performed is among the most important records of the endeavor.

If a bridge girder needs to have specific strength as defined by a certain number, we need to know how that figure was calculated. What were the assumptions, methods, and values that went into the result? Too many companies fail to preserve this record, and they lose invaluable intellectual property with every new project, resignation, or retirement. They have great difficulty reporting their work to clients and regulators. And they have no way of reusing engineering information in other areas of the enterprise, such as sales, marketing, and customer service.

Spreadsheets don’t help in this important area. They show answers, but omit context. Instead of seeing calculations laid out in conventional math notation, the user will see machine-readable text buried in formulas using cryptic hidden macros and convoluted cell structure.

Moreover, 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% of spreadsheets contain errors at their inception. Spreadsheet developers miss more than 80% of their own errors, and outside testers miss more than 50% of design logic and 34% of application errors.

A veteran engineer at Rolls-Royce also is sounding the alarm. Dr. Alan Stevens, who possesses a Ph.D. in theoretical physics, has documented several instances where Microsoft Excel provides incorrect results. For example, in resolving the implicit ambiguity in:

-x2

(-x)2Positive

-(x2) Negative

Excel chooses the positive interpretation, unlike in all properly constituted calculation software tools, which choose the negative. The positive interpretation has the unfortunate consequence that -x2+ 1 is different from 1 – x2.

“Talented engineers are using Excel and getting serious errors of which they’re simply not aware,” Stevens claims. “And errors build up more rapidly than you might expect.”

An electronic calculation “worksheet” is a better solution for effectively documenting design and engineering processes and ensuring accuracy. Unlike spreadsheets, worksheets employ real mathematical notation and capture, in human -readable text, illustrations and graphs—assumptions, sources, methods, and critical data behind every calculation.

Organizations can build on the value of these worksheets by organizing, tracking, controlling, and sharing them in a searchable Web-based repository.

With a solution incorporating these capabilities, organizations can obtain concrete and immediate answers to commonly asked questions, such as:

• Did we use the current requirement for cargo weight or the old requirement in this analysis?

• Does the delivered document match the analysis that was actually performed?

• Which design decisions depend on the yield strength of ASTM 316L steel, and where did we get that number?

• Has anyone done this calculation before?

• Did we get the units right?

Ability to answer questions like these separates great engineering organizations from average ones and makes the difference between efficient product development and failure. It can also save engineers’ reputations.

Do you have experience and expertise with the topics mentioned in this content? You should consider contributing to our WTWH Media editorial team and getting the recognition you and your company deserve. Click here to start this process.