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.

How to Build Better Robotics with Integrated Actuators

July 17, 2024
Reese Abouelnasr, a Mechatronics Engineer with Harmonic Drive, answers a few questions about the latest developments in actuators and the design or engineering challenges these...

Crisis averted: How our AI-powered services helped prevent a factory fire

July 10, 2024
Discover how Schneider Electric's services helped a food and beverage manufacturer avoid a factory fire with AI-powered analytics.

Pumps Push the Boundaries of Low Temperature Technology

June 14, 2024
As an integral part of cryotechnology, KNF pumps facilitate scientific advances in cryostats, allowing them to push temperature boundaries and approach absolute zero.

The entire spectrum of drive technology

June 5, 2024
Read exciting stories about all aspects of maxon drive technology in our magazine.