Functions

What are functions

Using simple formulas for calculations, like for example the sum of a range of cells, can be tedious and complicated. In the example below we calculate the sum of the prices using a simple formula.

If in this example we had 20 items instead of just 3, the typing of the formula would take much more time and we probably would make mistakes. For calculations and other operations that are frequently used, Calc offers functions.

A function is a predefined calculation entered in a cell to help you analyze or manipulate data in a spreadsheet. All you have to do is add the arguments, and the calculation is automatically made for you. Functions help you create the formulas needed to get the results that you are looking for.

In the previous example we can use the SUM function. This function takes as an argument a range of cells and calculates the sum of all cells.

Fortunately, Calc has an entire library of functions you can take advantage of. You may be familiar with common functions like Sum, Average, Product, and Count, but there are hundreds of functions in Calc, even for things like formatting text, referencing cells, calculating financial rates, and analyzing statistics.

Function syntax

To use functions correctly, you need to understand the different parts of a function and how to enter the function arguments. Each function has a specific syntax, which must be followed in order for the function to work correctly. The basic syntax to create a formula with a function is:

  1. Insert an equals sign (=),
  2. Insert the function name (SUM, for example is the function name for addition),
  3. A function normally accepts one or more arguments. Arguments contain the data on which the function will calculate the result. In most cases, the arguments are references to cell areas or individual cells.

Arguments in functions

Arguments must be enclosed in parentheses. Multiple arguments within parentheses are separated with a semicolon (;).

Example 1

The formula = AVERAGE(B1:B8) will calculate the avevrage of the numbers in the cell range B1 to B8. The argument in thus case is the cell range B1:B8.

Example 2

The formula = SUM(A6:A10; B1:B3; D20) will calculate the sum of all cells in ranges A6:A10, B1:B3 and the individual cell D20. Therefore, in this case the SUM function accepts multiple arguments.

Inserting functions in Calc

You can insert functions in Calc either manually or using the Function Wizard.

Insert a function manually

  1. Select the cell where the result will appear.
  2. Type the equals sign (=), then enter the function name (SUM, for example).
  3. Type the cells reference for the argument or arguments. Alternatively you can select with the mouse the cells or cell range to automatically insert the arguments inside the parentheses.

Press Enter, and the result will appear.

The function Wizard

The Functions Wizard is a tool that helps us first to find the function we want through the library and secondly to introduce the arguments step-by-step and with the correct syntax.

1. Select the cell that will contain the formula and the result will appear.

2. Click the button on the Formula bar to open the Function Wizard.

3. Select a category of functions to shorten the list, then scroll down through the named functions and select the required one by double-clicking on it. When you select a function its description appears on the right-hand side of the dialog. Double click on the function name from the list or click Next.

4. The Wizard now displays an area to the right where you can enter arguments manually by typing them inside the text boxes. Alternatively you can click the Shrink button to shrink the wizard so you can select cells from the spreadsheet using the mouse. Press OK to exit the wizard.

Last modified: Saturday, 14 April 2018, 5:49 PM