The Excel Function Help Tool If you forget the name of a function or the arguments it takes, you can have Excel help you. Simply click on the "fx" button (or, in some older versions of Excel, the “=” to the left of the formula window). Excel then asks you which function you need help with. You can select from just the ChangeUnits functions by selecting that addin category. Excel then gives a pop up window of the function and describes each parameter as you enter it. This feature is recommended since it eliminates the need to remember the order of the parameters and allows you to preview the answer.
The Excel Solver. The Excel Solver (installed as an addin named Solver Add-in from the original Excel software) can be used to find roots and minimize or maximize functions. The enables you to solve for an input value if you are given the output value desired without reprogramming a formula already set up using ChangeUnits.
Cell AutoCompletion. Excel often tries to automatically complete the typing of your input to cells assuming that you wish to repeat an input that you already have on your worksheet. This is useful when Excel’s guess is correct. When constructing unit-based formulas, however, Excel is often incorrect, and it is very easy not to notice the mistakes Excel makes for you. For example, if you have previously entered a unit like “m / s” and then enter just “m” below it, Excel will often change that to “m / s” even if you typed only “m” and return. The AutoComplete option can be turned off in Excel by deselecting (unchecking) the “Enable AutoComplete Cell Values” box under Tools|Options under the Edit tab (Windows) or under Excel|Preferences in the Edit pane (Mac).
Automatic Un-Capitalization. The default spell correction in Excel changes a word of three or more letters that begins with two capital letters and then small letters so that the second letter is not capitalized. This makes units such as MPa change to Mpa, which then have to be edited to get the correct spelling. A number of these common occurrences have been included in both spellings in the list of base units. For example, MPa and Mpa are both included as the same units. This Excel autocorrection can be turned off by deselecting (unchecking) the “Correct TWo INitial CApitals” box under Tools|Options under the Spelling tab and the “AutoCorrect Options …” button (Windows) or from the Tools|AutoCorrect menu (Mac).
Array Formulas. The ChangeUnits functions all work as specified above. However, in some cases, it is desirable to use them as Excel “Array Formulas” so that their output consists of two cells rather than a single cell. For example, for the Grav () function, it is sometimes convenient to output not just the value 9.806652 but also the underlying units “m/s^2”.
In fact, this is exactly what ChangeUnits does. The function Grav( ) is an array function which returns 9.806652 in the first cell and “m / s^2” in the second cell. If only one cell is selected for the output, only the first output, 9.806652, is outputted.
The functions ToMetric( ), FromMetric( ), Grav( ), and GasConst( ) are all array formulas, returning the value in the first cell and the units in the second cell. Illustrations of how to use array functions for Examples 3 and 7 above are given in ChangeUnits_Examples.xls at the bottom of the “Examples” worksheet, with the array formulas highlighted in orange.
For users unfamiliar with array formulas, their use can require getting used to. Here are the basics:
1. To input the function, highlight two adjoining cells.
2. Type the formula desired.
3. Hit “Ctrl-Shift-Enter”.
4. The formula you typed in will now be listed in both cells with curly brackets around it. The two output values will be displayed in the two cells.
5. Once you have an array function, you cannot delete only one cell’s value – you must delete both at once by highlighting both.
6. To update the formula, you must use “Ctrl-Shift-Enter” to complete the change. Hitting only “Enter” will give an error.
7. When editing (and sometimes inputting) array functions, it is sometimes necessary to make sure the cursor is located in the formula bar on the top of the Excel window, not in the cell itself, which is where it is when you select a cell and start typing. If you are having difficulty getting Excel to accept a correction to an array formula, make sure that the cursor is in the formula bar, not the cell.
You can get Microsoft help by searching “array formula”.