Spreadsheet software — an unappreciated engineering tool

July 26, 2007
Like many engineers, I'm often busy and need to get answers fast.

— John Doyle

My program of choice to flesh-out calculations is Microsoft Excel. Windows and the Office Suite are ubiquitous, so Excel is always available and everyone can use and read it.

That said, with so many software utilities available, it would be easy to just slob-out and let "black-box solvers" take over. But those of us wanting to keep our engineering muscles in tone are better off thinking through problems by encoding spreadsheets and using trusted engineering-theory books.

In fact, working with spreadsheets forces users to completely understand the mechanics of a problem. This puts users in a better position to devise innovative solutions. And taking a fellow engineer through a few calculations gets to the nub of a problem more convincingly than any amount of complex data or analysis.

Excel lets users parameterize 3D models, sort databases, and postprocesses finite-element results. And it has other features that users sometimes aren't aware of. They include:

Goal Seek, basically a NewtonRaphson numerical algorithm. Imagine having a spreadsheet calculation for the second moment of area of a given shape with a particular width. Back-solving the calculation solves for the width that gives a particular value of the second moment of area. This problem is hard to solve algebraically with a pen and paper, but Goal Seek sorts results in seconds.

Formula Auditing lets users trace cell formulas to find dependant and precedent cells. Colored boxes and arrows make it simple to check formulas. Research shows that most spreadsheets contain at least one error, so it's wise to check and validate your solutions.

VBA Macros. The Excel VBA (Visual Basic for Applications) programming language lets users create macros or small procedures to perform complex tasks in applications such as Excel, Word, Power Point, and Access.

Add-ins developed using VBA make the base program better yet. For example, XlXtrFun.xll is a collection of functions that interpolate, extrapolate and curve-fit data. Another handy add-in, ChangeUnits, converts units directly in Excel. Both add-ins are free downloads from the Internet at http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm and http://www.changeunits.com

On the downside, a lot of users complain that Excel makes underlying equations hard to view. The Show Formulas feature, for instance, displays X=AA415^2/(BT$6+S$3)^3, an equation far from transparent. And Microsoft's so-called Equation Editor might be good for certain tasks, but it can't interact with cell formulas.

Fortunately, a free download from http://www.excelcalcs.com dynamically displays cell formulas as equations. The XLC add-in makes it easy to check cell formulas directly against mathematical expressions so users can make sure they didn't screw up.

Excel, of course, comes from Microsoft Corp., One Microsoft Way, Redmond, WA 98052, microsoft.com.

John Doyle runs MoreVision, a mechanical engineering consultancy, www.morevision.co.uk. Doyle developed the XLC add-in tool.

The XLC add-in tool for Excel software dynamically displays cell formulas as equations.

Sponsored Recommendations

NEW Low Profile, Ultra Compact Power Supplies

March 13, 2024
Learn more HERE about Altech's Power supplies!

Altech's Liquid Tight Strain Relifs Catalog

March 13, 2024
With experienced Product Engineers and Customer Service personnel, Altech provides solutions to your most pressing application challenges. All with one thought in mind - to ensure...

Industrial Straight-Through Cable Gland

March 13, 2024
Learn more about Altech's cable glands and all they have to offer for your needs!

All-In-One DC-UPS Power Solutions

March 13, 2024
Introducing the All-In-One DC-UPS, a versatile solution combining multiple functionalities in a single device. Serving as a power supply, battery charger, battery care module,...

Voice your opinion!

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