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 ( 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.
No comments:
Post a Comment