Can you trust your engineering calculations to spreadsheets?

excel image 1You don’t always get to choose the software your company buys for you. And that’s too bad, because engineers frequently end up with tools that weren’t built for the complex calculations they have to perform and communicate. While Excel spreadsheets might work well for the finance department, they can wreak serious havoc in research and development, resulting in expensive errors.

We recently talked to Systems Engineer and PTC Mathcad community member, Stuart Bruff, about just such a situation. Bruff, who works in optoelectronics for a defense contractor, says using Excel spreadsheets can hide mistakes and create a cascade of problems, each building on the one before.

Q: In a thread on the PTC Community Forum, you talked about a large international company * that chose a mathematical solution for a complex dynamic simulator that was more complicated than it needed to be—specifically they chose an Euler method when an RK4 would have worked better?

A: They chose their integrators based on Excel spreadsheets, which they solved using standard numerical integration. The simulated results were wrong. It ended up showing that an RK4 integrator performed worse than a standard Euler integrator. It should have set alarm bells off instantly by looking at that.

Q: Can you explain the error?

A: It was a typical cut and paste error. The way it was implemented made it difficult to focus on the solution rather than the mechanics of the problem. In Excel, all you see is a large table of either floating point numbers or lengthy, textual formulae, which makes it really difficult to understand what’s happening. So you don’t naturally see a problem like wrong values or an incorrect cell reference by just looking at the spreadsheet.

Q: When you showed the team their error, they fixed the math?.

A: They did. I easily demonstrated the correct solutions using a PTC Mathcad worksheet, where it was a simple job to take what they had done and implement it in PTC Mathcad. I proved that the RK4 performed better than the simple Euler on everything.

Experiences like this highlight to me how many decisions are made using Excel spreadsheets at a complexity where effectively they have not been reviewed properly. The complexity overwhelms people’s ability to understand what is really happening.

Q: What would have happened, or rather, what typically happens when these errors aren’t caught?

A: They propagate all the way through the system, and if you are lucky the math works so-so. But if you are unlucky, when you finally conduct tests and everyone has implemented the solution, you find you don’t have effective results. This then means you have to go back – and then you are wasting time, money, and missing deadlines.

Q: Why do you think people cling to using Excel spreadsheets for these types of calculations?

A: One of them is the effective predominance of the Microsoft office environment. It is on everyone’s desks and is a common tool; it’s there so you use it. I’ve seen Excel spreadsheets used for all sorts of problems that they shouldn’t be. It’s used for database applications and complex math analyses where it’s not a suitable tool in many instances.

Q: Excel performs calculations. What makes it so unsuitable?

A: The biggest problem with it is that it’s unreviewable. It is a big problem with spreadsheets with that level of complexity; effectively people become blinded by the detail and they can’t see that the solutions were actually wrong. It makes it difficult to see the problem when everything is hidden from you.

Ed. Would more readable calculations make a difference in your next complex project? For your next complex calculations, see the difference yourself and test out PTC Mathcad Express for free today.

*company mentioned is not Bruff’s current employer.

View this infographic on the dangers of spreadsheets.

Learn more about our latest release PTC Mathad Prime 3.1