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 adds groups of contiguous ranges of numbers together.
*	This operator multiplies two 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 functions listed by category 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 — unlike in some spreadsheets — by selecting cells with the mouse. If the values in the cells change, then 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, 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 the majority of functions can be used in both Calc and Excel without any change.

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 its name, such as 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 parentheses and are separated by semicolons, with no space between the arguments and the semicolons.

OOo uses the semicolon as an argument list separator, unlike Excel which uses a comma. This is a common mistake made by users accustomed to entering Excel formulas.

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. These are called nested 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.

Notice that the nested formula for the average does not require its own equals sign. The one at the start of the equation is enough for both formulas.

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 the concept more clearly, but, through nesting of functions, a Calc formula can quickly become complex.

Calc 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.

Function List docked to right side of Calc 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 Wizard

The most commonly used input method is the Function Wizard. To open the Function Wizard, select Insert > Function, or click the fx 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.

Select a category of functions to shorten the list, then scroll down through the named functions and select the required one. When you select a function, its description appears on the right-hand side of the dialog. Double-click on the required function.

The wizard now displays an area to the right where you can enter data manually in text boxes or click the Shrink button to shrink the wizard so you can select the cells from the worksheet.

Function Wizard after shrinking.

To select cells, either click directly upon the cell or hold down the left mouse button and drag to select the needed area.

Functions page of Function Wizard.

When the area has been selected, click the Shrink button again to return to the wizard.

If multiple arguments are needed, select the next text box below the first and repeat the selection process for the next cell or range of cells. The wizard will accept up to 30 ranges or arguments in the Sum function.

Click OK to accept the function and add it to the cell and get the result.

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.

After you enter a function on the Input line, press the Enter key or click the Accept button on the Function toolbar to add the function to the cell and get its result.

The Function Toolbar.

1. Name Box showing list list of common functions
2. Function Wizard
3. Cancel
4. Accept
5. 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. Deselect Formulas and the result will display. However, you can still see the formula in the input line.

Strategies for creating formulas and functions

Formulas that do more than a simple calculation or summation of row or columns of values usually take a number of arguments. For example the classic equation of motion s = s0+ vt - ½at2 calculates the position of a body knowing its original position, its final velocity, its acceleration, and the time taken to move from the initial state to the final state.

For ease of presentation, it is good practice to set up a spreadsheet in a manner similar to that shown below. In this example, the individual variables are input into cells on the sheet and no editing of the formula (in cell B9) is required.

"Setting up a formula with arguments"

You can take several broad approaches when creating a formula. In deciding which approach to take, consider how many other people will need to use the worksheets, the life of the worksheets, and the variations that could be encountered in use of the formula.

If people other than yourself will use the spreadsheet, make sure that it is easy to see what input is required and where. Explanation of the purpose of the spreadsheet, basis of calculation, input required and output(s) generated are often placed on the first worksheet.

A spreadsheet that you build today, with many complicated formulas, may not be quite so obvious in its function and operation in 6 or 12 months time. Use comments and notes liberally to document your work.

You might be aware that you cannot use negative values or zero values for a particular argument, but if someone else inputs such a value, will your formula be robust or simply return a standard (and often not too helpful) Err: message? It is a good idea to trap errors using some form of logic statements or with conditional formatting.

Place a unique formula in each cell

The most basic strategy is to view whatever formulas are needed as simple and with a limited use life. The strategy is then to place a unique formula in each appropriate cell.This can be recommended only for very simple or "throw away" (single use) spreadsheets.

Break formulas into parts and combine the parts

The second strategy is similar to the first, but instead you break down longer formulas into smaller parts and then combine the parts into the whole. Many examples of this type exist in complex scientific and engineering calculations where interim results are used in a number of places in the worksheet. The result of calculating the flow velocity of water in a pipe may be used in estimating losses due to friction, whether the pipe is flowing full or partially empty, and in optimizing the diameter for the given flow regime.

In all cases you should adopt the basic principles of formula creation described previously.

Use the Basic editor to create functions

A third strategy is to use the Basic editor and create your own functions and macros. This approach would be used where the result would greatly simplify the use of the spreadsheet by the end user and keep the formulas simple with a better chance of avoiding errors. This approach also can make the maintenance easier by having corrections or updates kept in one central location. The use of macros is described in Calc Macros and is a specialized topic in itself. The danger of overusing macros and custom functions is that the principles upon which the spreadsheet is based become much more difficult to see by a user other than the original author (and sometimes even by the author!).