Image Image Image Image Image Image Image Image Image Image

Being Brunel |

Scroll to top

Top

4 Comments

More Engineering with Excel

More Engineering with Excel

The one thing that unites all engineers across the globe is a love of Excel. In fact, it’s more than a love, it’s an obsession- the things I’ve seen some engineers achieve with Excel despite how entirely unequipped it was for the task is truly phenomenal. And whilst I might continue to despair at our industry’s constant abuse of spreadsheets, I can’t deny that they’re obviously useful.

So, despite my better judgement, here are another four functions that you can use to create bigger and better Excel abominations:

Aggregate

Aggregate(function, option, range, parameter)

Aggregate isn’t just a function; it’s a mega function. Simply put, it applies a function to a range of values. You specify a list of numbers and then pick from a list of operations (medians, nth smallest, percentiles, variations, counts and sums, etc.). Although none of these operations need aggregate, it really comes into its own with large data sets- with its tolerance for errors, and ability to ignore hidden rows that can be applied through the options.

VLookup

VLookup(value, table, column number to return, match type)

Learning the Excel lookup functions is something of a coming-of-age ritual for your average Excel user. VLookup looks up a value in the first column of a table and then returns the value along the row for the column number you’ve specified. The real trick, however, is noting that the last argument (match type) should pretty much always be False. This tells Excel not to try and guess what to do when if can’t find the value it’s looking for; annoyingly the default behaviour is to just assume the lookup column is sorted and locate the ‘nearest’. This leads to some pretty bewildering errors for the uninitiated.

Convert

Convert(value, from units, to units)

Engineers always work in consistent units so the convert function is totally useless to them. If, however, you do inexplicably happen to find that you’re using values in stupid units; Excel has the ability to convert a (somewhat random) selection of units. It really comes into it’s own if you use it to composite unit converts. For example, to move from kPa to psi multiply the value by (CONVERT(1000,”N”,”lbf”)/CONVERT(1,”m^2″,”in^2″)).

Cell

Cell(information to fetch, address of cell if not this one)

Cell fetches information about the current (or target) cell in Excel. Initially some of the options seem a little dull, I mean- you probably know what the address is, and it’s showing you the value. However the type and filename options can be powerful. The former will allow you to distinguish between a provided and a derived value, and the latter will (unexpectedly) let you not only determine the filename, but the worksheet name as well!


So, go forth and write amazing complex spreadsheets!

Submit a Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Comments

  1. I prefer to use Python+numpy+scipy. In my opinion spreadsheets are better suited for financial calculations.

    • Yup, although I have to say that, it’s often easier to work out the intention of a spreadsheet then the code of someone who isn’t too experienced with programming; or who haven’t invested in learning much of the theory of coding.

  2. One great disadvantage of Excel for engineering calculations is that the actual formulas are hidden in the cells. Unlike Mathcad, it is difficult to follow and check the calculation report. Formulas also look ugly unless you use names cells.

Trackbacks

  1. […] don’t want to hazard a guess how many times I’ve complained about the terrible misuse of Excel that I often witness at the hands of over-zealous engineers. However, last time I did this someone […]