Article Archive Contact Features List 10

Media Pack Subscribe Privacy Policy

Construction Computing Online Training Map Recruitment

News

Review

From CAD User Mechanical Magazine  Vol 19 No 10 - SEPTEMBER/OCTOBER 2006

Using Excel spreadsheets to handle engineering calculations could be leading you into trouble, according to David Chadwick

I am always suspicious of statistics. I believe it is a healthy attitude when you see some of the spurious stuff appearing in the press these days. I also believe that if you were to add together all of the people in the UK who were not working for one reason or another, you would end up with half-a-dozen of us keeping the country afloat!
But these figures about the accuracy of Excel spreadsheets somehow strike a chord, especially when they are being used to produce complex engineering calculations – the sort of stuff that can determine the accuracy or viability of an engineering project, or the development of a design where accuracy is critical.

What statistics? Oh, just some interesting figures in a recent Mathsoft white paper that pointed out some disconcerting facts about Excel usage. Simple facts, such as the University of Hawaii study that found that 20% to 40% of all spreadsheets contain errors, and the Coopers and Lybrand report that stated that 90% of all spreadsheets with more than 150 lines contained errors – and more – backed up by a statement from Dr Alan Stevens, a specialist in mathematical modelling and simulation for Rolls Royce, who claimed that ‘talented engineers are using Excel and getting serious errors of which they’re simply not aware – and errors build up more rapidly than you might expect’!.

Mathsoft and PTC

I was handed the white paper following a meeting with Mathsoft, recently, where we discussed the company’s recent acquisition by PTC, in April this year. Mathcad, the the well-known engineering calculation package, has been added to PTC’s Product Development System as one of its main product lines, which also includes Pro/ENGINEER, Windchill and Arbortext.

Mathcad, the core programme, is supplemented by the Mathcad Calculation Server, which lets companies distribute fully interactive Mathcad worksheets on the Internet and intranets, and a couple of vertical libraries and extension packs covering mechanical, civil and electrical engineering, image processing, signal processing and data analysis. As part of PTC’s strategy of integrating its’ solutions within all of its products, Mathcad documents can be stored in PTC’s Windchill solution, with full Pro/ENGINEER associativity, consistent with the company’s policy of enabling its customers to store all intellectual property – and calculations are a company’s intellectual property every bit as much as a products’ design – in one location!

Despite its current position in PTC’s stable, Mathcad, though, is still being sold as a stand-alone package, available to engineers throughout the industry, from their normal suppliers.

Excel’s Contribution

I don’t think there is a single professional computer user who is not aware of Excel, and the tremendous benefits it has provided since its’ forerunners, Visicalc and Lotus 1-2-3 hit the market. As useful as the software is, however, it is not the most readable or accountable of products, especially when it is being used to create and manage complex engineering calculations.

The problem is that equations of any complexity can be embedded in an Excel cell – but as a single line equation – with no methods, assumptions, values and logic associated with it, except for the location of each of the equations’ factors. If you get the equation right, the answer will appear in the same cell. If you don’t, you may still get a result – but you won’t know that it is incorrect!

If, however, the equation is incorrectly laid out, Excel will tell you so, but with no explanation why – and it is left to the user to laboriously track through the logic and the worksheet to discover where the error lies, leaving him to work out where a parenthesis is missing, or where the logic is incorrect. And embedded equations and hidden macros are even harder to decrypt. Look at the two equations on the next page (figs 1 and 2) – both of which are solving the same problem – one from Excel and one from Mathcad! Which is easier to understand?

Mathcad Delivers

All of this is laid out in Matchsoft’s white paper, and is, of course, entirely selective to suit the company’s own purposes of promoting the use of Mathsoft whenever Excel comes across a job it can’t effectively handle.

So, what does Mathcad provide, instead? First of all, the mathematical equations are treated and displayed, as you see above, in exactly the same way that engineers would recognize – standard mathematical or algebraic notation. All inputs and errors are visible as they go in! The worksheets are easy to create, as well! Mathcad provides a series of quicksheets, comprising samples, trials and tutorials, which, if you are unsure how to go about setting up a particular equation, will even take you through the process!

If the calculation you have created works, it can be stored for future use. Companies tend to create libraries of tested and approved equations, so that they can guarantee the accuracy of their employees work – providing, of course, the correct data has been used.

Mathcad integrates readily with CAD packages. In the case of Pro/ENGINEER, this means that an engineer can select the Mathcad analysis tool from the toolbar, and attach an existing worksheet– or create one on the spot – to control the parameters and dimensions of the model. It’s a bi-directional integration, enabling the engineer to use the models dimensions to drive the calculation, or the calculation to modify the dimensions of the model.

Previously, the integration between Mathcad and CAD solutions was manual. Since the release of Pro/ENGINEER Wildfire 3.0 M030 in August 2006, this function has become automated and easily accessible from the Pro/ENGINEER user interface – a tremendous boon for optimising the performance, geometry, material and dimensions of a model – and a function of predictive engineering (or functional design, as others call it).

Predictive Engineering allows engineers to use engineering calculations up front to predict the behavior of the design before the CAD model is created. Having sketched out the problem, checking performance, dimensions, and other mathematical properties and come up with a solution, only then will the actual design be commenced. Engineers can even bring Pro/ENGINEER Mechanica into the equations, using analytical tools to optimise the design to the customers’ requirements.

Visualising the Equation

Mathcad’s visual capabilities are vastly superior to Excel’s giving engineers a number of ways of looking at their data – from 2D/3D plotting or graphing of equations, to experimental design gradings, matrices, hot spots – all very simply created within the worksheet, and providing engineers with an easy way to spot abnormalities in their data. Some users combine different display methods - text, graphics, schematics and live maths, for example, in the same worksheet.

Conclusion

Is intuitive, easy-to-use, and visible! It is driven by what is easiest, and quickest, and it’s presentation is just what the engineer knows – and expects! It allows them to build worksheets quickly – even cutting and pasting from known examples if necessary – to provide critical solutions quickly – and accurately. In view of the white paper figures – that last deserves to be repeated – and accurately!

www.ptc.com

Review

Click here for a Print Friendly Version

©2006 Business and Technical Communications Ltd. All rights reserved.
No part of this site may be reproduced without written permission of the owners.
www.CADUser.com