Because they work with geographic coordinates, GeogTools functions need to return more than a single number. Therefore, they are written as Excel “Array Formulas” so that their output consists of two or three cells rather than a single cell. For example, the LatLonToUTM() function is designed to return the easting, northing, and UTM zone values in three, horizontally adjacent cells. If only one cell is selected for the output, only the first output, the easting coordinate, is given as output.
For users unfamiliar with array formulas, they require getting used to. Here are the basics:
- To input the function, highlight two or three (as appropriate) horizontally adjoining cells.
- Type the formula desired or use the Insert|Function command to enter the formula.
- Hit “Ctrl-Shift-Enter”. Do no click the OK button in the formula entry dialog.
- The formula you typed in will now be listed in all selected cells with curly brackets around it. The output values will be displayed in the cells.
- Once you have an array function, you cannot delete only one cell’s value – you must delete both at once by highlighting both and selecting Edit|Clear|Contents.
- To update the formula, you must use “Ctrl-Shift-Enter” to complete the change. Hitting only “Enter” will give an error.
- 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, and that you use "Ctrl-Shift-Enter" when finished editing.
- You can get Microsoft help by searching “array formula”.