Difference between revisions of "Documentation/OOo3 User Guides/Calc Guide/Using functions"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Strategies for creating formulas)
(Strategies for creating formulas)
Line 119: Line 119:
the formula (in cell B9) is required.
the formula (in cell B9) is required.
There are several broad approaches when making a decision to create a formula. In deciding on an approach, consider how many other people will need to use the worksheets, the life of the worksheets, and the variations that could be encountered from use.
[[Image:CG3Ch7F20.png|thumb|none|500px|"Setting up a formula with arguments"]]
You can take several broad approaches when making a decision to create a formula. In deciding on an approach, consider how many other people will need to use the worksheets, the life of the worksheets, and the variations that could be encountered from use.
=== Enter a unique formula in each cell ===
=== Enter a unique formula in each cell ===

Revision as of 19:43, 25 December 2010

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.


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.


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.


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 CalcShrinkIcon.png 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

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 making a decision to create a formula. In deciding on an approach, consider how many other people will need to use the worksheets, the life of the worksheets, and the variations that could be encountered from use.

Enter a unique formula in each cell

The first and 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.

Break formulas into parts and combine the parts

The second strategy is similar, but instead you break down longer formulas into smaller parts and then combine the parts into the whole.

Editor's note: This needs some explanation, but it is NOT about the "formula editor" (Math).

Use the Basic editor to create functions

A third strategy is to use the Basic editor and create your own functions. 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 Chapter 12 of this book.

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools