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(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(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(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(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!