OpenOffice.org Calc functions, part 2: Working with formulas

A formula is a spreadsheet function entered in a cell, complete with its arguments. They're one of the two or three major applications that first spearheaded the acceptance of the personal computer in the 1980s, and the main tools of advanced spreadsheet use.

In part 1 of this article, I gave an overview of functions and what they do. Now, in part 2, I'll continue by explaining how you enter formulas (or formulae, if you prefer), and how you can check and review them afterwards.

Entering Formulas

You can enter function formulas in several ways.

The quickest way to enter a function 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 Function tool bar. The trouble with both these methods is that, because you are entering the names of functions are abbreviations, typos are too 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 inputting of a formula is most efficient for basic formulas or ones that you use all the time.

A more reliable method is the Function List. Available from Insert menu, the Function List is a floating window that automatically docks on the right side of the Calc editing window. The Function List includes a brief description of each function and its arguments, and selecting a function adds it to the current cell, together with placeholders for each of the function's arguments. 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.

However, the most commonly used input method is the Function Autopilot. The Function Autopilot opens from the Insert menu, from its button on the Function tool bar, or by pressing Ctrl+F2. Once open, the Function Autopilot 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.

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 in a spreadsheet anyway.

Another advantage of the Function Autopilot 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 window except the current input field so that you can see the spreadsheet beneath. When you are finished entering the argument's value, you can press the button again to return to the entire Function Autopilot window.

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 function 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 tool bar 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 check box beside it, and the result will display. However, you can still see the formula in the formula bar field.

Reviewing the Contents and Results of Formulas

Even with all the tools available in Calc to help you to enter formulas, making mistakes is easy. Many typists find inputting numbers difficult, and many users may make a mistake about the kind of entry that a function's argument needs. At times, too, you may want to find the cells used in a formula to change their values or to check the answer. For these reasons, Calc provides three tools for investigating formulas and the cells that they reference.

The most basic tool is error messages. Error messages display in a formula's cell or -- even more conveniently -- in the Function Autopilot instead of the result. An error message for a formula is usually a three-digit number from 501 to 527, or sometimes an unhelpful piece of text such as NAME?, REF, or VALUE. The error number appears in the cell, and a brief explanation of the error on the right side of the status bar. Most error messages indicate a problem with how the formula was input, although several indicate that you have run up against a limitation of either Calc or its current settings.

Error messages are not user-friendly, and may intimidate new users. All the same, they are valuable clues to correcting mistakes. You can find detailed explanations of them by opening OpenOffice.org help and searching for "Error codes in OpenOffice.org Calc." A few of the most common are:

• NAME? (525): No valid reference exists for the argument.
• REF (525): The column, row, or sheet for the referenced cell is missing.
• VALUE (519): The value for one of the arguments is not the type that the argument requires. The value may be entered incorrectly; for example, double-quotation marks may be missing around the value. At other times, a cell or range used may have the wrong format, such as text instead of numbers.
• 509: An operator such as an equals sign is missing from the formula.
• 510: An argument is missing from the formula.
• 502: The column, row, or sheet for the referenced cell is missing.

Another useful tool when reviewing a formula is the color coding for for input. When you select a formula that has already been run, the cells or ranges used for each argument in the formula are outlined in color. Calc uses eight colors for outlining referenced cells, starting with blue for the first cell, and continuing with red, magenta, green, dark blue, brown, purple and yellow before cycling through the sequence again.

However, in a long or complicated spreadsheet, color coding becomes less useful. In these cases, consider using Tools -> Detective's sub-menu. The Detective is a tool for checking which cells are used as arguments by a formula (precedents) and which other formulas it is nested in (dependents), and tracking errors. It can also be used for tracing errors, marking invalid data (that is, information in cells that is not in the proper format for a function's argument), or even for removing precents and dependents.

To use the Detective, selective a cell with a formula, then start the Detective. On the spreadsheet, you will see lines ending in circles to indicate precedents, and lines ending in arrows for dependents. If you like, think of the spreadsheet detective as Calc's equivalent of the lines that show connections between text frames in Writer. In both cases, the lines show the flow of information.

Conclusion

At this point, you should have a general sense of what functions do, and how to work with them. However, this is only an introduction to functions. There are additional tools for automating your use of functions and formulas, as well as the intricacies of function arguments, many of which require specialized knowledge to use effectively. In future articles, I plan to touch on both these subjects.

Bruce Byfield is a computer journalist who writes regularly for the Linux.com and Linux Journal web sites.