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

MOVI-C Unleashed: Your One-Stop Shop for Automation Tasks

April 17, 2024
Discover the versatility of SEW-EURODRIVE's MOVI-C modular automation system, designed to streamline motion control challenges across diverse applications.

The Power of Automation Made Easy

April 17, 2024
Automation Made Easy is more than a slogan; it signifies a shift towards smarter, more efficient operations where technology takes on the heavy lifting.

Lubricants: Unlocking Peak Performance in your Gearmotor

April 17, 2024
Understanding the role of lubricants, how to select them, and the importance of maintenance can significantly impact your gearmotor's performance and lifespan.

From concept to consumption: Optimizing success in food and beverage

April 9, 2024
Identifying opportunities and solutions for plant floor optimization has never been easier. Download our visual guide to quickly and efficiently pinpoint areas for operational...

Voice your opinion!

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