A KPMG study found that, in a sample of 22 spreadsheets, 91 percent contained errors. Is this a fluke? Nope. A similar study by S.G. Powell discovered that, in a sample of 25 operational spreadsheets, 10 had an error -- with the consequent financial impact ranging from $216,806 to $110,543,305. That's not the kind of money any company can risk losing.

How engineers perform calculations to assess the performance of products during a number of phases of the product development process

How a tool called engineering calculation software has emerged in recent years to help engineers build their formulas

How there are a number of benefits associated with building formulas using engineering calculation software as opposed to other alternatives.

What exactly is engineering calculation software, and how does it reduce the risks associated with spreadsheet errors? Simply put, engineering calculation software speaks the language of the mathematical engineer. It's purpose built for engineers and specifically geared for design and product development applications. Engineers build formulas based on calculus and differential equations, not cells. In engineering calculation software, formulas aren't hidden away, but instead presented as if on a sheet of drawing paper. By putting the formulas front and center, the risk of error immediately starts to drop. In addition, the software is unit-aware -- don't try to mix your meters and inches -- which essentially adds another safety check against design errors.

Another key capability of engineering calculation software is its integration with CAD applications. Not only can the engineer build equations by using parameters, dimensions, or measurements from the 3D model as variables, but each time that 3D model changes, the values automatically get updated in the related equations. Once again, another risk is eliminated as the engineer doesn't have to manually check cells -- and potentially miss some or errantly apply others. All of that allows engineers to run formula-based optimization and design of experiment routines and send the results back to the CAD model.

Engineers inevitably run into design variations, but the bulk of their work is spent looking at very similar or slight variations on the same performance characteristics or measures. Engineering calculation software accounts for those minute variations and allows engineers to save and reuse formulas, saving valuable time. The overall organization then is able not only to maximize engineer work time, but also to begin developing and storing calculation best-practices.

The bottom line is that engineering calculation software, with its inherent math language, unit awareness, integration with CAD applications, and standardization/reuse of calculations, eliminates spreadsheet risks and provides key benefits to both the organization and the individual engineer. The engineers who perform calculations more frequently and more accurately make better decisions. This translates into avoiding costly errors and winning with better designs.

And performing calculations more frequently and accurately bolsters an engineer's ability to reduce errors that turn into design disruptions later. That means engineers can focus their time on actual work, versus trying to program spreadsheets.

Brent Edmonds is the senior director of Mathcad at PTC.

As far as my background goes, you can see it here: http://www.lifecycleinsights.com/about-author/. I blog and write about the technologies that engineering organizations use to design and develop products. My company providers services for engineering organizations (http://www.lifecycleinsights.com/services-eng/) as well as technology providers (http://www.lifecycleinsights.com/services-soft/).

I blog at a number of places, including ENGINEERING.com (http://www.engineering.com/DesignSoftware/DesignSoftwareArticles/articleType/AuthorView/authorID/134769/Chad-Jackson.aspx), where I provide some frank insights on the direction of these types of technologies. I also blog at the new NAFEMs blog (http://www.nafems.org/blog/). I also co-host a webshow called Tech4PD (http://www.engineering.com/Videos/Tech4PDShow.aspx).

I've included this detail because you seem interested in what I do professionally.

I've written about a blog series on the engineering notebook and how calculations fit in (http://www.engineering.com/DesignSoftware/DesignSoftwareArticles/ArticleID/4682/Calculations-and-the-Engineering-Notebook.aspx). That led me down the path to write the eBook referenced in this post. I developed it independently. PTC licensed the publication and is hosting it on their web site for access.

As far as my site, lifecycleinsights.com, you'll find that none of my publications, presentations, web shows and whatnot are hosted there. By no means am I trying to start a media company like Design News. Instead, companies license my content and host it elsewhere like PTC or ENGINEERING.com for their own purposes.

I hope that answers some of your questions. Let me know if you have others.

I would question the source if I did not know it to be true. However, if we limit the discussion to the primary spreadsheet program, MS-Excel, there are known documented issues with the functions built into Excel. Random is not random, stdev is not the true standard deviation, linear regression is not least squares fit..... The list of statistics errors in Excel is legion. The frustrating thing is that MS has known about these errors and has not fixed them. Even when they implement a fix in a new version it turns out to be broken in a new way.

Microsoft has clearly abandoned a great market in Engineering and Science and only seems to care if money adds up correctly. I suspect the monetary errors listed in the article were user generated. The statistic function errors in Excel are built in.

There is a skeptical side of most engineers that demands to verify everything and assume very little. Last time I checked - (unless the spreadsheet is locked) formulas are easily visible and we can see how values are being calculated. I prefer that scenario over feeding values into a software "black box", shaking it a bit and then accepting the answer.

Years ago, when slide rules were still found in desks and calculators roamed the earth, my mentors gave me this advice:

"If you find that you can't do your initial estimates with a basic scientific calculator, STOP. If you find yourself digging out your textbooks and writing equations, STOP. --You are probably about to make a huge mistake.

"Someone before you has probably seen this problem and has an acceptable plant equation or rule of thumb that you can use. If you reinvent that wheel, the chance that you will make a mistake is very high. You should use those text books to validate or invalidate an approach, not to create new ones, unless the old approaches are clearly inadequate somehow and you are really treading on new territory."

And in the years since, I have used those textbooks to create new approaches, but only in a handful of cases.

The point about spreadsheets is much the same: If someone has written and validated software, USE IT. The chances of making a mistake when modeling with a generic tool such as a spreadsheet or writing your own modeling software are VERY high.

As engineers we have an obligation to our employers, clients, and to the public to get the correct answers. If there is prior art that you can use to help you in this process, USE IT.

However, Lifecycle Interests does not seem to host the e-book on its own website. The book instead is hosted at the web site of this blog's author, PTC. The author's company does not host its own work?

One has to question the source for the conclusions of the blog (and by inference the book as well).

I don't necessarily disagree that spreadsheets can cause errors to propagate because they tend to hide the equations. It does not negate their incredible usefulness. Other commenters have already noted the need for testing and checking. ANY tool needs that. The software being pushed by the blog author needs it just as much. It may make the process simpler, and add some helping hand, but it also needs to be checked.

Charles, one could as easily say 91% of hammer-driven nails are bent. The thrust of that statement would be powder-gun or electric-gun driven nails go in straight.

91% of nails may be bent by hammer-wielders, but that doesn't mean they don't hold just as well as the gun driven ones.

Ninety-one percent of spreadsheets contain errors? The nice thing about handheld calculators (and sliderules before that) was that the engineer was more connected to the calculation. There's a subtle disconnect when doing the calculation in software on a spreadsheet, which makes it easier for a resulting number to gain a couple of zeroes and still not be noticed.

Tekochip yes I also with you but my problem is when we changed the data sometimes sum functions has changed without knowing me. Is there a any method of locking the formulas after checking the test data.

Thank you very much for the very informative article, actually I have some bad experience with spreadsheets, But my mind did not like to believe that it is with the spreadsheets fault always in my side. Now I know what has gone wrong.

Practically all electronic devices today contain metals that may
be coming from conflict-ravaged African countries. And political pressures will increasingly influence how these minerals are sourced and used in products.

Conformal coatings present themselves as space-saving alternatives to enclosures for protecting the circuit boards of automotive electronics. They can also provide electrical insulation.

Design for manufacturing (DFM) in mold production means that mold designers evaluate the manufacturability of their molds in the early stage of mold development by collecting all relevant information and applying it to their designs. They also have to consider many other factors, including flow balance, structural stress, and assembly tolerance, in order to ensure successful molding production.

Some adhesives provide strong structural bonds but take hours to fixture and attain handling strength. The technologies that offer the fastest cure do not bear loads or withstand stresses. A new class of adhesives aims to make both stick.

Focus on Fundamentals consists of 45-minute on-line classes that cover a host of technologies.
You learn without leaving the comfort of your desk. All classes are taught by subject-matter experts and all are archived.
So if you can't attend live, attend at your convenience.

To save this item to your list of favorite Design News content so you can find it later in your Profile page, click the "Save It" button next to the item.

If you found this interesting or useful, please use the links to the services below to share it with other readers. You will need a free account with each service to share an item via that service.