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.
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.
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.
Bruce Byfield (nanday)
|Designing Electronics with Linux||May 22, 2013|
|Dynamic DNS—an Object Lesson in Problem Solving||May 21, 2013|
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
- RSS Feeds
- Dynamic DNS—an Object Lesson in Problem Solving
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Using Salt Stack and Vagrant for Drupal Development
- New Products
- Designing Electronics with Linux
- A Topic for Discussion - Open Source Feature-Richness?
- Drupal Is a Framework: Why Everyone Needs to Understand This
- Validate an E-Mail Address with PHP, the Right Way
- What's the tweeting protocol?
- Kernel Problem
2 hours 49 min ago
- BASH script to log IPs on public web server
7 hours 16 min ago
10 hours 51 min ago
- Reply to comment | Linux Journal
11 hours 24 min ago
- All the articles you talked
13 hours 47 min ago
- All the articles you talked
13 hours 51 min ago
- All the articles you talked
13 hours 52 min ago
18 hours 17 min ago
- Keeping track of IP address
20 hours 8 min ago
- Roll your own dynamic dns
1 day 1 hour ago
Enter to Win an Adafruit Pi Cobbler Breakout Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Pi Cobbler Breakout Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- 5-21-13, Prototyping Pi Plate Kit: Philip Kirby
- Next winner announced on 5-27-13!
Free Webinar: Hadoop
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers
Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.
Some of key questions to be discussed are:
- What is the “typical” Hadoop cluster and what should be installed on the different machine types?
- Why should you consider the typical workload patterns when making your hardware decisions?
- Are all microservers created equal for Hadoop deployments?
- How do I plan for expansion if I require more compute, memory, storage or networking?