Engineering with Excel
I think a casual observer of the industry would be startled to learn that one of the most used design tools is Microsoft Excel. Considering how ubiquitous the Office suite is, however, it is not that surprising that for (relatively) simple calculations, repetitive design methods and result processing most engineers turn to Excel.
Despite how much it is used, very few Universities attempt anything more then a glancing blow at the massive feature set that Excel offers.
So here are the top functions that I think are useful, but rarely see used:
Indirect(cell reference as text, optional R1C1 format flag)
The indirect function allows you use an additional function to determine which cell you want to reference. It allows you to pick which cell you want to address based on the result of a calculation. When combined with functions for determining the relative location (i.e. column() and row()) Indirect can be very powerful.
IfError(expression, value if error)
The IfError function will be the only one of the new functions added for Office 2010 in this list. The function returns the result of the expression unless there is an error, where then it returns the alternative value specified. Although this can be done by testing for an error separately, and then repeating the calculation; this is arduous and can be a nightmare to correct and later re-factor.
Match(value to to look for, list of values to look in, flag for exact match)
Although the majority of engineering spreadsheets I have come across use the v-/h-/lookup functions, very few of them employ Match. Unlike the lookup equivalents Match returns the index of the value is in a list, instead of just the value itself. This function comes into its own when you need refer to whole areas of data, rather than just a single result.
Choose(option number, option expression one, option expression two, …)
Out of all of these ‘hidden’ functions, Choose is the most elusive. It provides a way of branching results based on an index; taking the option number and returning the result of the corresponding function. Typically most engineers live without encountering Choose as nested if statements provide a similar functionality; however Choose is a much neater solution, especially when dealing with user input.
So next time you’re writing a spreadsheet take a look at these functions; they may well make your life easier in the long run.