Introduction
A formula is a spreadsheet function entered in a cell, complete with its arguments. You can enter formulas in several ways.
The quickest way to enter a formula is to start typing, either in the cell itself, or at the input line. If you are typing in the cell, as soon as you type an operator, such as an equal sign or less than sign (<), then the formula tools become active on the Formula bar.
The trouble with both these methods is that typos are easy to make. Also, unless you are entering a formula that you use all the time, you may not remember its structure. For this reason, manual input of a formula is best for basic formulas or ones that you use all the time.
New in 3.1
Calc now keeps the syntax of a formula displayed in a tooltip next to the cell as a handy memory aid as you type.
A more reliable method is to use the Function List. Available from the Insert menu, the Function List automatically docks as a pane on the right side of the Calc editing window. If you wish, you can Control+double-click on a blank space at the top of the pane to undock this pane and make it a floating window.
The Function List includes a brief description of each function and its arguments; highlight the function and look at the bottom of the pane to see the description. If necessary, hover the cursor over the division between the list and the description; when the cursor becomes a two-headed arrow, drag it upwards to increase the space for the description. Double-click on a function’s name to add it to the current cell, together with placeholders for each of the function’s arguments.
Using the Function List is almost as fast as manual entry, and has the advantage of not requiring that you memorize a formula that you want to use. In theory, it should also be less error-prone. In practice, though, some users may fumble when replacing the placeholders with values. Another handy feature is the ability to display the last formulas used.
Function List docked to right side of Calc window.
The most commonly used input method is the Function Wizard. To open the Function Wizard, select Insert > Function, or click its button on the Function tool bar, or press Ctrl+F2. Once open, the Function Wizard provides the same help features as the Function List, but adds fields in which you can see the result of a completed function, as well as the result of any larger formula of which it is part.
Functions page of Function Wizard.
You can also select the Structure tab to see a tree view of the parts of the formula. The main advantage over the Function List is that each argument is entered in its own field, making it easier to manage. The price of this reliability is slower input, but this is often a small price to pay, since precision is generally more important than speed when creating a spreadsheet.
Structure page of Function Wizard.
Another advantage of the Function Wizard is that you can reduce the window as you are adding each argument. At the end of each input field for an argument is the Shrink button
. The Shrink button temporarily removes all parts of the wizard except the current input field so that you can see the spreadsheet beneath. When you are finished entering the argument’s value, click the button again to return to the entire Function Wizard.
Function Wizard after shrinking.
Finally, as with many other features in OpenOffice.org, you can create a macro for any favorite function formula, and assign it to a tool bar, menu, or keystroke combination.
No matter how you enter a formula, take a moment to check its structure before using it. If it looks right, press the Enter key or select the Accept button from the Function toolbar to add it to the cell and get its result (the Accept button is the green check mark beside the Input line).
If you see the formula in the cell instead of the result, then Formulas are selected for display in Tools > Options > OpenOffice.org Calc > View > Display. Unselect the checkbox beside it, and the result will display. However, you can still see the formula in the formula bar field.
