Documentation/OOo3 User Guides/Calc Guide/Using functions

OpenOffice.org 3 Calc Guide

Chapter 7 Using Formulas and Functions

< Previous Section

Next Section >

• Creating formulas
• Understanding functions
• Finding and fixing errors
• Examples of functions
• Using regular expressions in functions

< Previous Page

Next Page >

Calc includes over 350 functions to help you analyze and reference data. Many of these functions are for use with numbers, but many others are used with dates and times, or even text. A function may be as simple as adding two numbers together, or finding the average of a list of numbers. Alternatively, it may be as complex as calculating the standard deviation of a sample, or a hyperbolic tangent of a number. See a list of all functions in functions listed by category.

Typically, the name of a function is an abbreviated description of what the function does. For instance, the FV function gives the future value of an investment, while BIN2HEX converts a binary number to a hexadecimal number. By tradition, functions are entered entirely in upper case letters, although Calc will read them correctly if they are in lower or mixed case, too.

A few basic functions are somewhat similar to operators. Examples:

"+", this operator will add two numbers together for a result. SUM() on the other hand will add a contiguous range of numbers together.

"*", this operator will multiply to numbers together for a result. PRODUCT() does the same for multiplying, that SUM() does for adding.

Each function has a number of arguments used in the calculations. These arguments may or may not have their own name. Your task is to enter the arguments needed to run the function. In some cases, the arguments have pre-defined choices, and you may need to refer to the online help or Appendix B (Description of Functions) in this book to understand them. More often, however, an argument is a value that you enter manually, or one already entered in a cell or range of cells on the spreadsheet. In Calc, you can enter values from other cells by typing in their name or range, or by selecting cells with the mouse. Should the values in the cells change, the result of the function will be automatically updated.

For compatibility, functions and their arguments in Calc have almost identical names to their counterparts in Microsoft Excel. However, both Excel and Calc have functions that the other lacks. Occasionally, too, functions with the same names in Calc and Excel have different arguments, or slightly different names for the same argument—neither of which can be imported to the other. However, perhaps nine-tenths of functions can be imported between Calc and Excel without any problems.

Understanding the structure of functions

All functions have a similar structure. If you use the right tool for entering a function, you can escape learning this structure, but it is still worth knowing for troubleshooting.

To give a typical example, the structure of a function to find cells that match entered search criteria is:

= DCOUNT (Database;Database field;Search_criteria)

Since a function cannot exist on its' own, it must always be part of a formula. Consequently, even if the function represents the entire formula, there must be an "=" sign at the start of the formula. Regardless of where in the formula a function is, the Function will start with the function's name, DCOUNT in the example above. After the name of the function comes its arguments. All arguments are required, unless specifically listed as optional.

Arguments are added within the brackets (parentheses) and separated by semicolons. Many arguments will be a number. A Calc function can take up to thirty numbers as an argument. That may not sound like much at first. However, when you realize that the number can be not only a number or a single cell, but also an array or range of cells that contain several or even hundreds of cells, then the apparent limitation vanishes.

Depending on the nature of the function arguments may be entered as follow:

"text data"	The quotes establish that text or string data has been entered
9	In this case the number nine has been entered as a number
"9"	In this case the number nine is being entered as text
A1	The address for whatever is in Cell A1 is being entered

Nested functions

Functions can also be used as arguments within other functions.

=SUM(2;PRODUCT(5;7))

To get an idea of what nested functions can do, imagine that you are designing a self-directed learning module. During the module, students do three quizzes, and enter the results in cells A1, A2, and A3. In A4, you can create a nested formula that begins by averaging the results of the quizzes with the formula =AVERAGE(A1:A3). The formula then uses the IF function to give the student feedback that depends upon the average grade on the quizzes. The entire formula would read:

=IF(AVERAGE(A1:A3) >85; "Congratulations! You are ready to advance to the next module";
 "Failed. Please review the material again. If necessary, contact your instructor for help")

Depending on the average, the student would receive the message for either congratulations or failure.

If you are new to spreadsheets, the best way to think of functions is as a scripting language. We've used simple examples to explain more clearly, but, through nesting of functions, a Calc formula can quickly become complex.

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.