WORKING WITH DIFFERENT

FORMULAE AND FUNCTIONS

Working with different Formulae and Functions

Introduction
In previous chapters, we have been entering one of two basic types of data into each cell: numbers and
text. However, we will not always know what the contents should be. Often the contents of one cell
depends on the contents of other cells. To handle this situation, we use a third type of data: the formula.
Formulas are equations using numbers and variables to get a result. In a spreadsheet, the variables are
cell locations that hold the data needed for the equation to be completed.
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.

Setting up a spreadsheet
If you are setting up more than a simple one-worksheet system in Calc, it is worth planning
ahead a little. Avoid the following traps:
Typing fixed values into formulas
Not including notes and comments describing what the system does, including what input is
required and where the formulas come from (if not created from scratch)
Not incorporating a system of checking to verify that the formulas do what is intended

The trap of fixed values

Many users set up long and complex formulas with fixed values typed directly into the formula.
For example, conversion from one currency to another requires knowledge of the current
conversion rate. If you input a formula in cell C1 of =0.75*B1 (for example to calculate the value in
Euros of the USD dollar amount in cell B1), you will have to edit the formula when the exchange
rate changes from 0.75 to some other value. It is much easier to set up an input cell with the
exchange rate and reference that cell in any formula needing the exchange rate. What-if type
calculations are also simplified: what if the exchange rate varies from 0.75 to 0.70 or 0.80? No
formula editing is needed and it is clear what rate is used in the calculations. Breaking complex
formulas down into more manageable parts, described below, also helps to minimize errors and
aid troubleshooting.

Lack of documentation
Lack of documentation is a very common failing. Many users prepare a simple worksheet which
then develops into something much more complicated over time. Without documentation, the
original purpose and methodology is often unclear and difficult to decipher. In this case it is
usually easier to start again from the beginning, wasting the work done previously. If you insert
comments in cells, and use labels and headings, a spreadsheet can later be modified by you or
others and much time and effort will be saved.

Error-checking formulas
Adding up columns of data or selections of cells from a worksheet often results in errors due to
omitting cells, wrongly specifying a range, or double-counting cells. It is useful to institute checks in
your spreadsheets. For example, set up a spreadsheet to calculate columns of figures, and use
SUM to calculate the individual column totals. You can check the result by including (in a non-
printing column) a set of row totals and adding these together. The two figures—row total and
column total—must agree. If they do not, you have an error somewhere.

Chapter 7 Using Formulas and Functions 192

Working with different Formulae and Functions

 Figure 144: Error checking of formulas

You can even set up a formula to calculate the difference between the two totals and report
an error in case a non-zero result is returned (see Figure 144).

Creating formulas
You can enter formulas in two ways, either by using the Function Wizard, or by typing directly into
the cell or into the input line. A formula must begin with an = symbol, so when typing in directly,
you need to start a formula with one of the following symbols: =, + or –. Calc automatically adds
the = symbol for the formula, when starting with the + or _ character. Starting with anything else
causes the formula to be treated as if it were text.

Operators in formulas
Each cell on the worksheet can be used as a data holder or a place for data calculations.
Entering data is accomplished simply by typing in the cell and moving to the next cell or pressing
Enter. With formulas, the equals sign indicates that the cell will be used for a calculation. A
mathematical calculation like 15 + 46 can be accomplished as shown in Figure 145.
While the calculation on the left was accomplished in only one cell, the real power is shown on the
right where the data is placed in cells and the calculation is performed using references back to
the cells. In this case, cells B3 and B4 were the data holders, with B5 the cell where the calculation
was performed. Notice that the formula was shown as =B3+B4. The plus sign indicates that the
contents of cells B3 and B4 are to be added together and then have the result in the cell holding
the formula. All formulas build upon this concept. Other ways of using formulas are shown in Table
4.
These cell references allow formulas to use data from anywhere in the worksheet being worked
on or from any other worksheet in the workbook that is opened. If the data needed was in different
worksheets, they would be referenced by referring to the name of the worksheet, for example
=SUM(Sheet2.B12+Sheet3.A11).

Chapter 7 Using Formulas and Functions 193

Working with different Formulae and Functions

 To enter the = symbol for a purpose other than creating a formula as described in this
chapter, type an apostrophe or single quotation mark before the =. For example, in
Note the entry '= means different things to different people, Calc treats everything after the
single quotation mark—including the = sign—as text.

Simple Calculation in 1 Cell Calculation by Reference

Figure 145: A simple calculation

Table 4: Common ways to use formulas

Formula Description
=A1+10 Displays the contents of cell A1 plus 10.
=A1*16% Displays 16% of the contents of A1.
=A1*A2 Displays the result of multiplying the contents of A1 and A2.
=ROUND(A1,1) Displays the contents of cell A1 rounded to one decimal place.
=EFFECTIVE(5%,12) Calculates the effective interest for 5% annual nominal interest with
12 payments a year.
=B8-SUM(B10:B14) Calculates B8 minus the sum of the cells B10 to B14.
=SUM(B8,SUM(B10:B14)) Calculates the sum of cells B10 to B14 and adds the value to B8.
=SUM(B1:B1048576) Sums all numbers in column B.

Chapter 7 Using Formulas and Functions 194

Working with different Formulae and Functions

 Formula Description
=AVERAGE(BloodSugar) Displays the average of a named range defined under the name
BloodSugar.
=IF(C31>140, "HIGH", "OK") Displays the results of a conditional analysis of data from two
sources. If the contents of C31 is greater than 140, then HIGH is
displayed, otherwise OK is displayed.

Users of Lotus 1-2-3, Quattro Pro and other spreadsheet software may be familiar
with formulas that begin with +, -, =, (, @, ., $, or #. A mathematical formula would
Note look like +D2+C2 or +2*3. Functions begin with the @ symbol such as
@SUM(D2..D7), @COS(@DEGTORAD(30)) and
@IRR(GUESS,CASHFLOWS). Ranges are identified such as A1..D3.

Functions can be identified in Table 4 by a word, for example ROUND, followed by

parentheses enclosing references or numbers.
It is also possible to establish ranges for inclusion by naming them using Insert > Names, for
example BloodSugar representing a range such as B3:B10. Logical functions can also be
performed as represented by the IF statement which results in a conditional response based
upon the data in the identified cell, for example
=IF(A2>=0,"Positive","Negative")
A value of 3 in cell A2 would return the result Positive, a value of –9 the result Negative.

Operator types
You can use the following operator types in LibreOffice Calc: arithmetic, comparative, text,
and reference.

Arithmetic operators
The addition, subtraction, multiplication and division operators return numerical results. The
Negation and Percent operators identify a characteristic of the number found in the cell, for
example -37. The example for Exponentiation illustrates how to enter a number that is being
3
multiplied by itself a certain number of times, for example 2 = 2*2*2.
Table 5: Arithmetical operators

Operator Name Example

+ (Plus) Addition =1+1
– (Minus) Subtraction =2–1
– (Minus) Negation –5
* (asterisk) Multiplication =2*2
/ (Slash) Division =10/5
% (Percent) Percent 15%
^ (Caret) Exponentiation 2^3

Chapter 7 Using Formulas and Functions 195

Working with different Formulae and Functions

Comparative operators
Comparative operators are found in formulas that use the IF function and return either a true
or false answer; for example, =IF(B6>G12, 127, 0) which, loosely translated, means if the
contents of cell B6 are greater than the contents of cell G12, then return the number 127,
otherwise return the number 0.
A direct answer of TRUE or FALSE can be obtained by entering a formula such as =B6>B12. If
the numbers found in the referenced cells are accurately represented, the answer TRUE is
returned, otherwise FALSE is returned.

Table 6: Comparative operators

Operator Name Example

= Equal A1=B1
> Greater than A1>B1
< Less than A1<B1
>= Greater than or equal to A1>=B1
<= Less than or equal to A1<=B1
<> Inequality A1<>B1

If cell A1 contains the numerical value 4 and cell B1 the numerical value 5, the above
examples would yield results of FALSE, FALSE, TRUE, FALSE, TRUE, and TRUE.

Text operators
It is common for users to place text in spreadsheets. To provide for variability in what and how this
type of data is displayed, text can be joined together in pieces coming from different places on the
spreadsheet. Figure 146 shows an example.

Figure 146: Text concatenation

In this example, specific pieces of the text were found in three different cells. To join these
segments together, the formula also adds required spaces and punctuation enclosed within
quotation marks, resulting in a formula of =B2 & " " & C2 & ", " & D2. The result is the
concatenation into a date formatted in a particular sequence.
Calc has a CONCATENATE function which performs the same operation.

Chapter 7 Using Formulas and Functions 196

Working with different Formulae and Functions

Taking this example further, if the result cell is defined as a name, then text concatenation is
performed using this defined name. This process is demonstrated in Figures 147, 148, and 149
where the cell with the date is named “WizardDay” and subsequently used in a formula in another
cell.

Figure 147: Defining a name for a range of cells

Chapter 7 Using Formulas and Functions 197

Working with different Formulae and Functions

 Figure 148: Naming a cell or range of cells for inclusion in a formula
The defined name WizardDay in D2.

Text entered into A4, the formula into A6.

The result displayed in A6.

Figure 149: Using Names in a formula

Reference operators
An individual cell is identified by the column identifier (letter) located along the top of the columns
and a row identifier (number) found along the left-hand side of the spreadsheet. On spreadsheets
read from left to right, the reference for the upper left cell is A1.
Thus in its simplest form a reference refers to a single cell, but references can also refer to a
rectangle or cuboid range or a reference in a list of references. To build such references you
need reference operators.

Chapter 7 Using Formulas and Functions 198

Working with different Formulae and Functions

Range operator
The range operator is written as a colon. An expression using the range operator has the
following syntax:
reference upper left : reference lower right
The range operator builds a reference to the smallest range including both the cells referenced
with the left reference and the cells referenced with the right reference.

Figure 150: Reference Operator for a range

In the upper left corner of Figure 150 the reference A1:D12 is shown, corresponding to the
cells included in the drag operation with the mouse to highlight the range.
Examples
A2:B4 Reference to a rectangle range with 6 cells, 2 column width × 3 row
height. When you click on the reference in the formula in the
input line, a border indicates the rectangle.
(A2:B4):C9 Reference to a rectangle range with cell A2 top left and cell C9
bottom right. So the range contains 24 cells, 3 column width ×
8 row height. This method of addressing extends the initial
range from A2:B4 to A2:C9.
Sheet1.A3:Sheet3.D4 Reference to a cuboid range with 24 cells, 4 column width × 2 row
height × 3 sheets depth.

When you enter B4:A2 or A4:B2 directly, then Calc will turn it to A2:B4. So the left top cell of the
range is left of the colon and the bottom right cell is right of the colon. But if you name the cell
B4 for example with _start and A2 with _end, you can use _start:_end without any error.
Calc can not reference a whole column of unspecified length using A:A or a whole row using
1:1 which you might be familiar with in other spreadsheet programs.

Reference concatenation operator

The concatenation operator is written as a tilde. An expression using the concatenation
operator has the following syntax:
reference left ~ reference right
The result of such an expression is a reference list, which is an ordered list of references.
Some functions can take a reference list as an argument, SUM, MAX or INDEX for example.

Chapter 7 Using Formulas and Functions 199

Working with different Formulae and Functions

The reference concatenation is sometimes called 'union'. But it is not the union of the two sets
'reference left' and 'reference right' as normally understood in set theory. COUNT(A1:C3~B2:D2)
returns 12 (=9+3), but it has only 10 cells when considered as the union of the two sets of cells.
Notice that SUM(A1:C3,B2:D2) is different from SUM(A1:C3~B2:D2) although they give the
same result. The first is a function call with 2 parameters, each of them is reference to a range.
The second is a function call with 1 parameter, which is a reference list.

Intersection operator
The intersection operator is written as an exclamation mark. An expression using the intersection
operator has the following syntax:
reference left ! reference right
If the references refer to single ranges, the result is a reference to a single range, containing
all cells, which are both in the left reference and in the right reference.
If the references are reference lists, then each list item from the left is intersected with each one
from the right and these results are concatenated to a reference list. The order is to first intersect
the first item from the left with all items from the right, then intersect the second item from the left
with all items from the right, and so on.
Examples
A2:B4 ! B3:D6
This results in a reference to the range B3:B4, because these cells are inside A2:B4 and
inside B3:D4.
(A2:B4~B1:C2) ! (B2:C6~C1:D3)
First the intersections A2:B4!B2:C6, A2:B4!C1:D3, B1:C2!B2:C6 and B1:C2!C1:D3 are
calculated. This results in B2:B4, empty, B2:C2, and C1:C2. Then these results are
concatenated, dropping empty parts. So the final result is the reference list B2:B4 ~ B2:C2
~ C1:C2.
You can use the intersection operator to refer a cell in a cross tabulation in an understandable
way. If you have columns labeled 'Temperature' and 'Precipitation' and the rows labeled 'January',
'February', 'March', and so on, then the following expression
'February' ! 'Temperature'
will reference to the cell containing the temperature in February.
The intersection operator (!) has a higher precedence than the concatenation operator (~), but
do not rely on precedence.

Tip Always put in parentheses the part that is to be calculated first.

Relative and absolute references

References are the way that we refer to the location of a particular cell in Calc and can be either
relative (to the current cell) or absolute (a fixed amount).

Relative referencing
An example of a relative reference will illustrate the difference between a relative reference
and absolute reference using the spreadsheet from Figure 151.
1) Type the numbers 4 and 11 into cells C3 and C4 respectively of that spreadsheet.

Chapter 7 Using Formulas and Functions 200

Working with different Formulae and Functions

 Copy the formula in cell B5 (=B3+B4) to cell C5. You can do this by using a simple copy
and paste or click and drag B5 to C5 as shown below. The formula in B5 calculates the
sum of values in the two cells B3 and B4.
Click in cell C5. The formula bar shows =C3+C4 rather than =B3+B4 and the value in C5
is 15, the sum of 4 and 11 which are the values in C3 and C4.
In cell B5 the references to cells B3 and B4 are relative references. This means that Calc
interprets the formula in B5, applies it to the cells in the B column, and puts the result in the cell
holding the formula. When you copied the formula to another cell, the same procedure was used to
calculate the value to put in that cell. This time the formula in cell C5 referred to cells C3 and C4.

Figure 151: Relative references

You can think of a relative address as a pair of offsets to the current cell. Cell B1 is 1 column to
the left of Cell C5 and 4 rows above. The address could be written as R[-1]C[-4]. In fact earlier
spreadsheets allowed this notation method to be used in formulas.
Whenever you copy this formula from cell B5 to another cell the result will always be the sum of
the two numbers taken from the two cells one and two rows above the cell containing the formula.
Relative addressing is the default method of referring to addresses in Calc.

Absolute referencing
You may want to multiply a column of numbers by a fixed amount. A column of figures might
show amounts in US Dollars. To convert these amounts to Euros it is necessary to multiply each
dollar amount by the exchange rate. $US10.00 would be multiplied by 0.75 to convert to Euros, in
this case Eur7.50. The following example shows how to input an exchange rate and use that rate
to convert amounts in a column form USD to Euros.
Input the exchange rate Eur:USD (0.75) in cell D1. Enter amounts (in USD) into cells
D2, D3 and D4, for example 10, 20, and 30.
In cell E2 type the formula =D2*D1. The result is 7.5, correctly shown.
Copy the formula in cell E2 to cell E3. The result is 200, clearly wrong! Calc has copied
the formula using relative addressing: the formula in E3 is =D3*D2 and not what we want,
which is =D3*D1.
In cell E2 edit the formula to be =D2*$D$1. Copy it to cells E3 and E4. The results are
now 15 and 22.5 which are correct.
The $ signs before the D and the 1 convert the reference to cell D1 from relative to absolute
or fixed. If the formula is copied to another cell the second part will always show $D$1. The

Chapter 7 Using Formulas and Functions 201

Working with different Formulae and Functions

interpretation of this formula is “take the value in the cell one column to the left in the same
row and multiply it by the value in cell D1”.

Entering the conversion formula into E2, correct result, then copying it to E3.

E3 result is clearly wrong, change the formula in E2 to read absolute reference.

Applying the correct formula from E2 to E3 to get the correct answer.

Figure 152: Absolute references

Cell references can be shown in four ways.

Reference Explanation
D1 Relative, from cell E3 it is the cell one column to the left and two rows above
$D$1 Absolute, from cell E3 it is the cell D1
$D1 Partially absolute, from cell E3 it is the cell in column D and two rows above
D$1 Partially absolute, from cell E3 it is the cell one column to the left and in row 1

To change references in formulas, highlight the cell and press Shift-F4 to cycle
Tip through the four types of references. This method is of limited value in more
complicated formulas; it is usually quicker to edit the formula by hand.

Knowledge of the use of relative and absolute references is essential if you want to copy and paste
formulas and to link spreadsheets.

Chapter 7 Using Formulas and Functions 202

Working with different Formulae and Functions

Order of calculation
Order of calculation refers to the sequence in which numerical operations are performed. Division
and multiplication are performed before addition or subtraction. There is a common tendency to
expect calculations to be made from left to right as the equation would be read in English. Calc
evaluates the entire formula, then based upon programming precedence breaks the formula
down executing multiplication and division operations before other operations. Therefore, when
creating formulas you should test your formula to make sure that the correct result is being
obtained. Following is an example of the order of calculation in operation.

Table 7: Order of Calculation

Left To Right Calculation Ordered Calculation

1+3*2+3 = 11 =1+3*2+3 result 10
1+3 = 4, then 4 X 2 = 8, then 8+3 = 11 3*2 = 6, then 1+6+3 = 10
Another possible intention could be: The program resolves the multiplication of
1+3*2+3 = 20 3 X 2 before dealing with the numbers
being added.
1+3 = 4, then 2+3 = 5, then 4 X 5=20

If you intend for the result to be either of the two possible solutions on the left, the way to
achieve these results would be to order the formula as:
((1+3) * 2)+3 = 11 (1+3) * (2+3) = 20

Use parentheses to group operations in the order you intend; for example,
Note =B4+G12*C4/M12 might become =((B4+G12)*C4)/M12.

Calculations linking sheets

Another powerful feature of Calc is the ability to link data through several worksheets. The naming
of worksheets can be helpful to identify where specific data may be found. A name such as
Payroll or Boise Sales is much more meaningful than Sheet1. The function named SHEET()
returns the sheet number (position) in the collection of worksheets. There may be several
worksheets in each book and they may be numbered from the left: Sheet1, Sheet2, and so forth.
If you drag the worksheets around to different locations among the tabs, the function returns the
number referring to the current position of this worksheet. In a new instance of Calc, the default is
a single worksheet.
For example, if the formula =SHEET() is put into A1 on Sheet 1 it returns the value 1. If you drag
Sheet 1 to be positioned between sheets 2 and 3 then the value changes to 2, it is now the
second sheet in the order.
An example of calculations obtaining data from other work can be seen in a business setting
where a business combines revenues and costs of each of its branch operations into a single
combined worksheet.

Chapter 7 Using Formulas and Functions 203

Working with different Formulae and Functions

 Sheet containing data
for Branch 1.

Sheet containing data

for Branch 2.

Chapter 7 Using Formulas and Functions 204

Working with different Formulae and Functions

 Sheet containing data
for Branch 3.

Sheet containing combined

data for all branches.

Figure 153: Combining data from several sheets into a single sheet

The spreadsheets have been set up with identical structures. The easiest way to do this is to
open a new spreadsheet, set up the first Branch spreadsheet, input data, format cells, and
prepare the formulas for the various sums of rows and columns.
On the worksheet tab, right-click and select Rename Sheet. Type Branch1. Right-click on
the tab again and select Move/Copy Sheet.
In the Move/Copy Sheet dialog, select the Copy option (automatically selected if there
is only one sheet in the spreadsheet) and select -move to end position- in the Insert
before window. Change the entry in New name to Branch2. Click OK. Repeat to
produce the Branch3 and Combined worksheets.

Chapter 7 Using Formulas and Functions 205

Working with different Formulae and Functions

 Figure 154: Copying a worksheet
Enter the data for Branch 2 and Branch 3 into the respective sheets. Each sheet stands
alone and reports the results for the individual branches.
In the Combined worksheet, click on cell K7. Type =, click on the tab Branch1, click on
cell K7, press +, repeat for sheets Branch2 and Branch3 and press Enter. You now have
a formula in cell K7 which adds the revenue from Greenery Sales for the 3 Branches.

Figure 155: Combined worksheet showing linking between branch sheets

Chapter 7 Using Formulas and Functions 206

Working with different Formulae and Functions

 Copy the formula, highlight the range K7:N17, click Edit > Paste Special, uncheck the
Paste all and Formats boxes in the Selection area of the dialog box and click OK. You
will see the following message:

Figure 156: Linking sheets: pasting a formula to a

cell range
Click Yes. You have now copied the formulas into each cell while maintaining the format
you set up in the original worksheet. Of course, in this example you would have to tidy the
worksheet up by removing the zeros in the non-formatted rows.

Figure 157: Linking Sheets: Copy Paste Special from K7:N17

LibreOffice default is to paste all the attributes of the original cell(s) - formats, notes,
Note objects, text strings and numbers.

The Function Wizard can also be used to accomplish the linking. Use of this Wizard is described
in detail in the section on Functions.

Understanding functions
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.
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

Chapter 7 Using Formulas and Functions 207

Working with different Formulae and Functions

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 adds 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 predefined 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 —unlike the case in some spreadsheets—by selecting cells with the mouse. If the values in the
cells change, then the result of the function is 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. A
comparison list may be found on the LibreOffice wiki, linked from the Documentation/Publications
page.

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 commas, with no space
between the arguments and the commas.
Many arguments are numbers. 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 follows:
"text data" The quotes indicate text or string data is being entered.
9 The number nine is being entered as a number.
"9" The number nine is being entered as text
A1 The address for whatever is in Cell A1 is being entered

Chapter 7 Using Formulas and Functions 208

Working with different Formulae and Functions

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 equal 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 tool tip next to the cell as a
Note handy memory aid as you type.

A more reliable method is to use the Function List (Figure 158).

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.
Clicking on the bar where the 5 dots and arrows are shown (shown by the ellipse in Figure 158) will
hide the list on the right hand side of the screen. Clicking this area again will show the list, making
it easy to keep the list available for easy reference.
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 feature is the ability to display the last formulas used.

Chapter 7 Using Formulas and Functions 209

Working with different Formulae and Functions

Figure 158: Function List docked to right side of Calc window

Function Wizard
The most commonly used input method is the Function Wizard (Figure 160). To open the
Function Wizard, choose Insert > Function, or click the fx button on the Formula 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 by double-clicking on it. When you select a function its description appears
on the right-hand side of the dialog.
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 cells from the worksheet.

Figure 159: Function Wizard after shrinking

Chapter 7 Using Formulas and Functions 210

Working with different Formulae and Functions

Figure 160: Functions page of Function Wizard.
To select cells, either click directly upon the cell or hold down the left mouse button and drag
to select the required area.
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. Repeat this process as often as required. 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.

If you select a function by double-clicking it in the list, and then change your mind
Caution and select a different one by double-clicking again, then the second choice formula
is added into the first choice formula in the Formula text box. You must clear
the formula box and then double-click the function to add it to the box.
This additive facility allows you to create complex formulas by building them up
in the Formula box.

You can also select the Structure tab (Figure 161) 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.

Pressing the dialog Help button after selecting certain functions with either a single-
Caution or double-click will cause LibreOffice to crash. This early bug may be corrected for
later releases in the Version 4.1. series. Affected functions are:
AVERAGEIF; AVERAGEIFS; SUMIFS; COUNTIFS; IFERROR; IFNA;
XOR; NUMBERVALUE; SKEWP

Chapter 7 Using Formulas and Functions 211

Working with different Formulae and Functions

Figure 161: Structure page of Function Wizard
Functions can be entered into the Input line. After you enter a function on the Input line, press the
Enter key or click the Accept button on the Formula toolbar to add the function to the cell and get
its result.

Name Box showing list of common functions

2 Function Wizard 4 Accept
3 Cancel 5 Input Line
Figure 162: The Function toolbar

If you see the formula in the cell instead of the result, then Formulas are selected for display in
Tools > Options > LibreOffice Calc > View > Display. Deselect Formulas, and the result will
display. However, you can still see the formula in the Input line.

Chapter 7 Using Formulas and Functions 212

Working with different Formulae and Functions

Finding and fixing errors
It is common to find situations where errors are displayed. Even with all the tools available in Calc
to help you to enter formulas, making mistakes is easy. Many people find inputting numbers
difficult and many may make a mistake about the kind of entry that a function’s argument needs. In
addition to correcting errors, you may want to find the cells used in a formula to change their
values or to check the answer.
Calc provides three tools for investigating formulas and the cells that they reference:
error messages, color coding, and the Detective.

Error messages
The most basic tool is error messages. Error messages display in a formula’s cell or in
the Function Wizard 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. However, they are valuable clues
to correcting mistakes. You can find detailed explanations of them in the help, by searching for Error
codes in LibreOffice Calc. A few of the most common are shown in the following table.
#NAME? Instead of displaying Err:525. No valid reference exists for the argument.
#REF Instead of displaying Err:524. The column, row, or sheet for the referenced
cell is missing.
#VALUE Instead of displaying Err: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.
#DIV0! Instead of displaying Err:532. Division by zero
#NUM! A calculation results in an overflow of the defined value range.
509 An operator such as an equals sign is missing from the formula.
510 A variable is missing from the formula.

Chapter 7 Using Formulas and Functions 214

Working with different Formulae and Functions

Examples of common errors

#DIV/0! Division by zero

This error is the result of dividing a number by either the number zero (0) or a blank cell. There is
an easy way to avoid this type of problem. When you have a zero or blank cell displayed, use a
conditional function. Figure 164 depicts division of column B by column C yielding 2 errors
arising from a zero and a blank cell showing in column C.

Figure 164: Examples of #DIV/0!, Division by zero error

It is very common to find an error such as this arising from a situation where data was not reported
or reported incorrectly. When such an occurrence is possible, an IF function can be used to
display the data correctly. The formula =IF(C3>0, B3/C3, "No Report") can be entered. The
formula is then copied over the remainder of Column D. The meaning of this formula roughly
would be: “If C3 is greater than 0, then compute B3 divided by C3, otherwise enter ‘No Report’”.
It is also possible for the last parameter to use double quotes for a blank (no value) to be entered,
or a different formula with a standardized number being substituted for the lower number. An
example is shown in Figure 165.

Figure 165: Division by zero solution

Chapter 7 Using Formulas and Functions 215

Working with different Formulae and Functions

#VALUE No result and #REF Incorrect references
The #Value error is also very common.

Figure 166: Incorrect entry causing #VALUE error

A common occurrence of this error arises when a cell contains an incorrect value type. In the
example of Figure 23, text “None” has been entered in C8, where our formula in column D is
expecting a number.
The #REF error is caused by a missing reference. In the example below, the formula references
a sheet which has been deleted.

Color coding for input

Another useful tool when reviewing a formula is the color coding for input. When you select a
formula that has already been entered, 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.

Chapter 7 Using Formulas and Functions 216

Working with different Formulae and Functions

The Detective
In a long or complicated spreadsheet, color coding becomes less useful. In these cases,
consider using the submenu under Tools > Detective. 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 precedents and dependents.
To use the Detective, select 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. The lines show the flow of information.
Use the Detective to assist in following the precedents referred to in a formula in a cell. By
tracing these precedents, you frequently can find the source of the errors. Place the cursor in the
cell in question and then choose Tools > Detective > Trace Precedents from the menu bar or
press Shift+F7. Figure 167 shows a simple example of tracing precedents.
This allows us to check the source cells (which may be a range) for any errors which have caused us
to query the calculation result. If a source is a range, then that range is highlighted in blue.
In other instances we may have to trace an error. For this we use the Trace Error function,
found under Tools > Detective > Trace Error, to find the cells that caused the error.

a) Cell containing formula selected.

Figure 167: Tracing precedents using the Detective

Chapter 7 Using Formulas and Functions 217

Working with different Formulae and Functions

 b) Initiate trace by clicking Trace Precedents

Source cells indicated with blue dots, with arrow pointing to the calculation

cell (continued): Tracing precedents using the Detective

Examples of functions
For novices, functions are one of the most intimidating features of LibreOffice's Calc. New users
quickly learn that functions are an important feature of spreadsheets, but there are hundreds,
and many require input that assumes specialized knowledge. Fortunately, Calc includes dozens
of functions that anyone can use.

Chapter 7 Using Formulas and Functions 218

Working with different Formulae and Functions

 Mathematical Functions
(COUNT,COUNTBLANK,COUNTIF,COUNTIFS,SUM,SUMIF,SUMIFS)

 Statistical analysis functions (AVERAGE, AVERAGEIF, MAX,

MIN,MOD)

 Date and Time functions (DATE, TODAY,TIME)

 Logical functions (AND,FALSE,IF,NOT,OR, TRUE)

 Information functions (ISBLANK, ISNUMBER)

 Text functions (CONCATENATE, EXACT)

Working with different Formulae and Functions

Functions available in Calc
Calc provides all of the commonly used functions found in modern spreadsheet applications.
Since many of Calc’s functions require very specific and carefully calculated input arguments, the
descriptions in this appendix should not be considered complete references for each function.
Refer to the application Help or the LibreOffice wiki for details and examples of all functions. On
the wiki, start with http://help.libreoffice.org/Calc/Functions_by_Category
Over 300 standard functions are available in Calc. More can be added through extensions to
Calc (see Chapter 14). The following tables list Calc’s functions organized into eleven categories.

Functions whose names end with _ADD are provided for compatibility with Microsoft
Excel functions. They return the same results as the corresponding functions in Excel
Note (without the suffix), which though they may be correct, are not based on international
standards. Calc automatically changes the function to _ADD for relevant functions in
imported Excel spreadsheets.

Terminology: numbers and arguments

Some of the descriptions in this appendix define limitations on the number of values or arguments
that can be passed to the function. Specifically, functions that refer to the following arguments may
lead to confusion:
Number_1, number_2, ... number_30
Number 1 to 30
a list of up to 30 numbers
There is a significant difference between a list of number s (or integers) and the number of
arguments a function will accept. For, example the SUM function will only accept a maximum of
30 arguments. This limit does NOT mean that you can only sum 30 numbers, but that you can
only pass 30 separate arguments to the function.
Arguments are values separated by commas, and can include ranges which often refer to
multiple values. Therefore one argument can refer to several values, and a function that limits
input to 30 arguments may in fact accept more then 30 separate numerical values.
This appendix attempts to clarify this situation by using the term arguments, rather than any of
the other phrases.
In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no
parameter follows. For example, in a function with four parameters, where the last two
parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but
you cannot leave out parameter 3 alone.

In the tables of functions in this Appendix, several bugs are listed; if you wish to check
Note on the progress of fixing those bugs, you can visit http://bugs.libreoffice.org/ and
enter the bug number.

Appendix B Description of Functions 386

Working with different Formulae and Functions

Mathematical functions
Table 33: Mathematical functions

Syntax Description
ABS(Number) Returns the absolute value of the given Number.
ACOS(Number) Returns the inverse cosine of the given Number in radians.
ACOSH(Number) Returns the inverse hyperbolic cosine of the given Number in
radians.

ACOT(Number) Returns the inverse cotangent of the given Number in radians.

ACOTH(Number) Returns the inverse hyperbolic cotangent of the given Number
in radians.
ASIN(Number) Returns the inverse sine of the given Number in radians.
ASINH(Number) Returns the inverse hyperbolic sine of the given Number in
radians.

ATAN(Number) Returns the inverse tangent of the given Number in radians.

ATAN2(number_x, number_y) Returns the inverse tangent of the specified x and y coordinates
in radians. number_x is the value for the x coordinate.
number_y is the value for the y coordinate.
ATANH(Number) Returns the inverse hyperbolic tangent of the given Number.
(Angle is returned in radians.)
BITAND(Number, Number) This is the bitwise “AND” of two positive integers whose values
are less than 2˄48. Both parameters are required.
Bug 71810, concerning parameter names in BITAND, BITOR,
and BITXOR.
BITLSHIFT(Number, Shift) The bitwise left shift of an integer value. Both parameters are
required. Number is an integer less than 2˄48. Shift is the
number of bits to move by.
BITOR(Number, Number) This is the bitwise “OR” of two positive integers whose values
are less than 2˄48. Both parameters are required.
BITRSHIFT(Number, Shift) The bitwise right shift of an integer value. Both parameters are
required. Number is an integer less than 2˄48. Shift is the
number of bits to move by.
BITXOR(number, number) This is the bitwise “exclusive OR” of two positive integers whose
values are less than 2˄48. Both parameters are required.
CEILING(Number, Significance, Rounds up the given Number to the nearest multiple of the
Mode) value of Significance. Mode is an optional value. If the mode
value is given and not equal to zero, and if number and
significance are negative, then rounding is done based on the
absolute value of number. Omit this value for Excel
compatibility.
COMBIN(count_1, count_2) Returns the number of combinations for elements without
repetition. count_1 is the total number of elements. count_2 is
the number to be combined from the elements. This is the
same as the nCr function on a calculator.

Appendix B Description of Functions 387

Working with different Formulae and Functions

 Syntax Description
COMBINA(count_1, count_2) Returns the number of combinations for a given number of
objects (repetition included). count_1 is the total number of
elements. count_2 is the number to choose from the elements.
CONVERT(value, text, text) Converts a value from one unit of measurement to another.
value is the quantity to be converted. The first text is the official
abbreviation for the measurement in question (for example, "mi"
for miles). The second text parameter gives the unit to which it
is to be converted. Both text arguments must be within quotes
and are case sensitive. The conversion is done according to a
table in the configuration (main.xcd).
Bug 69539: This function does not work.
COS(Number) Returns the cosine of the Number (the angle in radians).
COSH(Number) Returns the hyperbolic cosine of the Number (the angle in
radians).
COT(Number) Returns the cotangent of the Number (the angle in radians).
COTH(Number) Returns the hyperbolic cotangent of the Number (the angle in
radians).
COUNTBLANK(range) Returns the number of empty cells. range is the cell range in
which the empty cells are counted.
COUNTIF(range, criteria) Returns the number of cells that meet the criteria within a cell
range. range is the range to which the criteria are to be applied.
criteria indicates the criteria in the form of a number, a regular
expression, or a character string by which the cells are counted.
COUNTIFS(range 1, criteria 1, Returns the number of cells that meet multiple criteria in multiple
range 2, criteria 2, …,) cell ranges. range 1 (required), range 2, …, are the ranges to
which the criteria are to be applied. criteria 1 (required), criteria
2, …, indicate the criteria in the form of a number, a regular
expression, or a character string by which the cells are
evaluated. All ranges must have the same dimension and size.
CSC(Angle) Returns the cosecant of an angle given in radians (1/SIN(X)).
CSCH(Angle) Returns the hyperbolic cosecant of a hyperbolic angle
(1/SINH(X)).
DEGREES(Number) Converts the given Number in radians to degrees.
EUROCONVERT(value, Converts from one pre-Euro currency to another. value is the
from_currency, to_currency, value to be converted. The from_currency is the ISO 4217
full_precision, code of the currency from which value is to be converted. The
triangulation_precision) to_currency is the ISO 4217 code of the currency to which
value is to be converted. The entries are not case sensitive.
The above parameters are required. The optional
full_precision parameter, if omitted, is 0 or FALSE rounds the
result to the decimals of the to_currency. If full_precision is
TRUE, the result is not rounded. triangulation_precision is
optional. If triangulation_precision is given and >=3, the
intermediate result of a triangular conversion (currency1,
EURO, currency2) is rounded to that precision. If
triangulation_precision is omitted, the intermediate result is
not rounded. Also if to_currency is "EUR",
triangulation_precision is used as if triangulation was needed

Appendix B Description of Functions 388

Working with different Formulae and Functions

 Syntax Description
and conversion from Euro to Euro was applied. Conversion
rates and currency codes can be found here:
http://ec.europa.eu/economy_finance/euro/adoption/conversion/
index_en.htm
The Cyprus pound has been omitted from this list but is “CYP”.
Bug 71850: These are NOT case sensitive as stated in the
Function Wizard.
EVEN(Number) Rounds the given Number up to the nearest even integer, and a
negative number down to the next even number.
EXP(Number) Returns e raised to the power of the given Number.
FACT(Number) Returns the factorial of the given Number.
FLOOR(Number, Significance, Rounds the given Number down to the nearest multiple of
Mode) Significance. Significance is the value to whose multiple the
number is to be rounded down. Mode is an optional value. If it is
indicated and non-zero and if the number and significance are
negative, rounding is done based on the absolute value of the
number. Note: Many application user interfaces have a FLOOR
function with only two parameters, and somewhat different
semantics than given here (e.g., they operate as if there was a
non-zero mode value). These FLOOR functions are inconsistent
with the standard mathematical definition of FLOOR.
GCD(Integer 1, Integer 2, Returns the greatest common divisor of one or more positive
…, Integer 30)) integers. Integers x is a list of up to 30 integers, at least one
of which must be greater than zero, whose greatest common
divisor is to be calculated. This gives a result based on
international standards.
GCD_ADD(Number(s), Returns the greatest common divisor of a list of numbers.
Number(s)1, …, Number(s)30) Number(s) X is a list of up to 30 numbers, additional to
Number(s) separated by commas. This gives the same results
as MS Excel.
INT(Number) Rounds the given Number down to the nearest integer.
LCM(Integer 1, Integer 2, …, Returns the least common multiple of one or more integers.
Integer 30) Integer 1, Integer 2, …, Integer 30 are integers whose lowest
common multiple is to be calculated.
LCM_ADD(Number(s), Number(s) X is a list of up to 30 numbers, additional to
Number(s)1, …, Number(s)30) Number(s), separated by commas. The result is the lowest
common multiple of a list of numbers.
LN(Number) Returns the natural logarithm, based on the constant e, of the
given Number.
LOG(Number, Base) Returns the logarithm of the given Number (value >0) to the
specified base. Base is the base for the logarithm calculation. If
omitted, 10 is assumed.
LOG10(Number) Returns the base-10 logarithm of a Number >0.
MOD(Dividend, Divisor) Returns the remainder after a number is divided by a divisor.
Dividend is the number to be divided. Divisor is the number by
which the dividend is divided.

Appendix B Description of Functions 389

Working with different Formulae and Functions

 Syntax Description
MROUND(Number, Multiple) Returns Number rounded to the nearest multiple of Multiple.
MULTINOMIAL (Number(s), Returns the factorial of the sum of the arguments divided by the
Number(s)1, …, Number(s)30) product of the factorials of the arguments. Number(s) X is a list
of up to 30 numbers, additional to Number(s), separated by
commas.
ODD(Number) Rounds Number up if positive and down if negative, to the
nearest odd integer.
PI() Returns the value of PI to fourteen decimal places.
POWER(Base, Exponent) Returns the result of a number raised to a power. Base is the
number that is to be raised to the given power. Exponent is the
exponent by which the base is to be raised.
PRODUCT(Number 1, Number Multiplies all the numbers given as arguments and returns the
2, …, Number 30) product. Number 1 to Number 30 are up to 30 arguments
whose product is to be calculated, separated by commas.
QUOTIENT(Numerator, Returns the integer result of a division operation. Numerator is
Denominator) the number that will be divided. Denominator is the number the
numerator will be divided by.
RADIANS(Number) Converts the given Number in degrees to radians.
RAND() Returns a random number between 0 and 1. This number will
recalculate every time data is entered, Ctrl+Shift+F9 or F9 is
pressed.
RANDBETWEEN (Bottom, Top) Returns an integer random number between Bottom and Top
(inclusive). This number will recalculate when the Ctrl+Shift+F9
key combination is pressed (not F9 alone).
ROUND(number, count) Rounds the given number to count (optional) decimal places. If
the count parameter is omitted or zero, number rounds to the
nearest integer. If count is negative, the function rounds to the
nearest 10, 100, 1000 and so on.
ROUNDDOWN(number, count) Rounds the given number down, to count (optional) decimal
places. If the count parameter is omitted or zero, number
rounds down to the nearest integer. If count is negative, the
function rounds down to the nearest 10, 100, 1000 and so on.
Number rounds toward zero.
ROUNDUP(number, count) Rounds the given number up to count (optional) decimal
places. If the count parameter is omitted or zero, number
rounds up to the nearest integer. If count is negative, the
function rounds up to the nearest 10, 100, 1000 and so on.
Number rounds away from zero.
SEC(Angle) Returns the secant of an Angle given in radians.
SEC(x)=1/COS(x).
SECH(Angle) Returns the hyperbolic secant of an Angle given in radians.
SECH(x)=1/COSH(x).

Appendix B Description of Functions 390

Working with different Formulae and Functions

 Syntax Description
SERIESSUM(X, N, M, Returns the sum of a powers series.
Coefficients) SERIESSUM(X, N, M, Coefficients) = coefficient_1*x^n +
coefficient_2*x^(n+m) + coefficient_3*x^(n+2m) +...+
coefficient_i*x^(n+(i-1)m).
X is the number as an independent variable. N is the starting
power. M is the increment. Coefficients is a series of
coefficients. For each coefficient the series sum is extended by
one section. You can only enter coefficients using a cell range.
SIGN(Number) Returns the sign of the given Number. The function returns the
result 1 for a positive sign, –1 for a negative sign, and 0 for zero.

SIN(number) Returns the sine of the given number (angle in radians).

SINH(number) Returns the hyperbolic sine of the given number (angle in
radians).
SQRT(number) Returns the positive square root of the given number. The
value of the number must be positive.
SQRTPI(Number) Returns the square root of the product of the given Number and
PI.
SUBTOTAL(Function, range) Calculates subtotals. If a range already contains subtotals,
these are not used for further calculations. Function is a value
that stands for another function such as Average, Count,
Min, Sum, Var. range is the range whose cells are included.
SUM(number 1, number 2, Adds all the numbers in a range of cells. Number 1, number
…, number 30) 2, …, number 30 are up to 30 arguments whose sum is to be
calculated. You can also enter a range using cell references.
SUMIF(range, criteria, Adds the cells specified by the given criteria. The search
sum_range) supports regular expressions. range is the range to which the
criteria are to be applied. criteria is the cell in which the search
criterion is shown, or the search criterion itself. sum_range
(optional) is the range from which values are summed; if it has
not been entered, the values found in the range are summed.
If supplied, sum_range must be the same size and shape as
range.
SUMIFS(sum_range, range Totals the values of cells in a range that meet multiple criteria
1, criteria 1, range 2, criteria in multiple ranges. sum_range (required) is the cell range from
2, …,) which the values are to be totaled. range 1 (required) is the
cell range to be evaluated by criteria 1 (required), range 2 by
criteria 2 and so on. All ranges must have the same size and
shape.
SUMSQ(number 1, number 2, Calculates the sum of the squares of numbers (totaling up of the
…, number 30) squares of the arguments) number 1, number 2, …, number
30 are up to 30 arguments, the sum of whose squares is to be
calculated.
TAN(number) Returns the tangent of the given number (angle in radians).
TANH(number) Returns the hyperbolic tangent of the given number (angle in
radians).

Appendix B Description of Functions 391

Working with different Formulae and Functions

 Syntax Description
TRUNC(number, count) Truncates a number by removing decimal places. number is the
number whose decimal places are to be trimmed. count
(optional) is the number of decimal places which are retained. If
count is missing or zero, it effectively truncates to a decimal
integer. If count is negative, it truncates to the left of the
decimal point.

Financial analysis functions

A note about dates

Date values used as parameters for Calc’s financial functions must comply with ISO8601 and be
entered surrounded by double quotes. For example, a date representing August 6, 2004, must
be entered “2004-08-06”, single digits are padded with leading zeroes. If you do not enter the
date values as required by the function, you will not get the correct results. Date formats are
locale specific and will allow other formats to be used. Among others, the en_US locale allows
“2004/08/06” and “08/06/2004” for example. Check the Help for the acceptable formatting.

A note about interest rates

You can enter interest rates in either of two ways:
As a decimal. To enter an interest rate as a decimal, divide it by 100 before entering it into a
function. For example, to compute a loan with a 3.25% interest rate, enter .0325 into the
function.
As a percentage. To enter an interest rate as a percentage, type in the interest rate
followed by the % key. For example, to compute a loan with a 3.25% interest rate,
enter 3.25% into the function.
If you enter it as 3.25, the function will treat it as a 325% interest rate.
Accounting systems vary in the number of days in a month or a year used in calculations.
The following table gives the integers used for the basis parameter used in some of the
financial analysis functions.

Table 34: Basis calculation types

Basis Calculation
0 or missing US method (NASD), 12 months of 30 days each.
1 Exact number of days in months, exact number of days in year.
2 Exact number of days in month, year has 360 days.
3 Exact number of days in month, year has 365 days.
4 European method, 12 months of 30 days each.

Appendix B Description of Functions 392

Working with different Formulae and Functions

Table 35: Financial analysis functions

Syntax Description
ACCRINT(Issue, First interest, Calculates the accrued interest of a security in the case of
Settlement, Rate, Par, Frequency, periodic payments. Issue is the issue date of the security.
Basis) First interest is the first interest date of the security.
Settlement is the maturity date. Rate is the annual nominal
rate of interest (coupon interest rate). Par is the par value of
the security. Frequency is the number of interest payments
per year (1, 2 or 4). Basis indicates how the year is to be
calculated.
ACCRINTM(Issue, Settlement, Calculates the accrued interest of a security in the case of
Rate, Par, Basis) one-off payment at the settlement date. Issue is the issue
date of the security. Settlement is the maturity date. Rate is
the annual nominal rate of interest (coupon interest rate). Par
is the par value of the security. Basis indicates how the year
is to be calculated.
AMORDEGRC(Cost, Date Calculates the amount of depreciation for a settlement
purchased, First period, period as degressive amortization. Unlike AMORLINC, a
Salvage, Period, Rate, Basis) depreciation coefficient that is independent of the
depreciable life is used here. Cost is the acquisition cost.
Date purchased is the date of acquisition. First period is
the end date of the first settlement period. Salvage is the
salvage value of the capital asset at the end of the
depreciable life. Period is the settlement period to be
considered. Rate is the rate of depreciation. Basis indicates
how the year is to be calculated.
AMORLINC(Cost, Date Calculates the amount of depreciation for a settlement
purchased, First period, period as linear amortization. If the capital asset is
Salvage, Period, Rate, Basis) purchased during the settlement period, the proportional
amount of depreciation is considered. Cost is the acquisition
cost. Date purchased is the date of acquisition. First
period is the end date of the first settlement period. Salvage
is the salvage value of the capital asset at the end of the
depreciable life. Period is the settlement period to be
considered. Rate is the rate of depreciation. Basis indicates
how the year is to be calculated.
COUPDAYBS(Settlement, Returns the number of days from the first day of interest
Maturity, Frequency, Basis) payment on a security until the settlement date. Settlement
is the date of purchase of the security. Maturity is the date
on which the security matures (expires). Frequency is the
number of interest payments per year (1, 2 or 4). Basis
indicates how the year is to be calculated.
COUPDAYS(Settlement, Maturity, Returns the number of days in the current interest period in
Frequency, Basis) which the settlement date falls. Settlement is the date of
purchase of the security. Maturity is the date on which the
security matures (expires). Frequency is the number of
interest payments per year (1, 2 or 4). Basis indicates how
the year is to be calculated.

Appendix B Description of Functions 393

Working with different Formulae and Functions

 Syntax Description
COUPDAYSNC(Settlement, Returns the number of days from the settlement date until the
Maturity, Frequency, Basis) next interest date. Settlement is the date of purchase of the
security. Maturity is the date on which the security matures
(expires). Frequency is the number of interest payments per
year (1, 2 or 4). Basis indicates how the year is to be
calculated.
COUPNCD(Settlement, Maturity, Returns the date of the first interest date after the settlement
Frequency, Basis) date, and formats the result as a date. Settlement is the date
of purchase of the security. Maturity is the date on which the
security matures (expires). Frequency is the number of
interest payments per year (1, 2 or 4). Basis indicates how
the year is to be calculated.
COUPNUM(Settlement, Maturity, Returns the number of coupons (interest payments) between
Frequency, Basis) the settlement date and the maturity date. Settlement is the
date of purchase of the security. Maturity is the date on
which the security matures (expires). Frequency is the
number of interest payments per year (1, 2 or 4). Basis
indicates how the year is to be calculated.
COUPPCD(Settlement, Maturity, Returns the date of the interest date prior to the settlement
Frequency, Basis) date, and formats the result as a date. Settlement is the date
of purchase of the security. Maturity is the date on which the
security matures (expires). Frequency is the number of
interest payments per year (1, 2 or 4). Basis indicates how
the year is to be calculated.
CUMIPMT(Rate, NPER, pv, S, E, Calculates the cumulative interest payments (the total
Type) interest) for an investment based on a constant interest rate.
Rate is the periodic interest rate. NPER is the payment
period with the total number of periods. NPER can also be a
non-integer value. The Rate and NPER must refer to the
same unit, and thus both must be calculated annually or
monthly. pv is the current value in the sequence of
payments. S is the first period. E is the last period. Type is
the due date of the payment at the beginning (1) or end (0) of
each period.
CUMIPMT_ADD(Rate, Nper, Pv, Calculates the accumulated interest for a period. Rate is the
Start period, End period, Type) interest rate for each period. Nper is the total number of
payment periods. The Rate and Nper must refer to the same
unit, and thus both must be calculated annually or monthly.
Pv is the current value. Start period the first payment period
for the calculation. End period the last payment period for
the calculation. Type is the due date of the payment at the
beginning (1) or end (0) of each period.
CUMPRINC(Rate, NPER, PV, S, Returns the cumulative interest paid for an investment period
E, Type) with a constant interest rate. Rate is the periodic interest
rate. NPER is the payment period with the total number of
periods. NPER can also be a non-integer value. The Rate
and NPER must refer to the same unit, and thus both must be
calculated annually or monthly. PV is the current value in the
sequence of payments. S is the first period. E is the last
period. Type is the due date of the payment at the beginning
(1) or end (0) of each period.

Appendix B Description of Functions 394

Working with different Formulae and Functions

 Syntax Description
CUMPRINC_ADD(Rate, Nper, Pv, Calculates the cumulative redemption of a loan in a period.
Start period, End period, Type) Rate is the interest rate for each period. Nper is the total
number of payment periods. The Rate and Nper must refer
to the same unit, and thus both must be calculated annually
or monthly. Pv is the current value. Start period is the first
payment period for the calculation. End period is the last
payment period for the calculation. Type is the due date of
the payment at the beginning (1) or end (0) of each period.
DB(Cost, Salvage, Life, Period, Returns the depreciation of an asset for a specified period
month) using the fixed-declining balance method. Cost is the initial
cost of an asset. Salvage is the value of an asset at the end
of the depreciation. Life defines the period over which an
asset is depreciated. Period is the length of each period. The
life must be entered in the same date unit as the depreciation
period. month (optional) denotes the number of months for
the first year of depreciation.
DDB(Cost, Salvage, Life, Period, Returns the depreciation of an asset for a specified period
Factor) using the arithmetic-declining method. Note that the book
value will never reach zero under this calculation type. Cost
fixes the initial cost of an asset. Salvage fixes the value of an
asset at the end of its life. Life is the number of periods
defining how long the asset is to be used. Period defines the
length of the period. The period must be entered in the same
time unit as the life. Factor (optional) is the factor by which
depreciation decreases. If no value is entered, a value of 2 is
assumed, making this double declining.
DISC(Settlement, Maturity, Price, Calculates the allowance (discount) of a security as a
Redemption, Basis) percentage. Settlement is the date of purchase of the
security. Maturity is the date on which the security matures
(expires). Price is the price of the security per 100 currency
units of par value. Redemption is the redemption value of
the security per 100 currency units of par value. Basis
indicates how the year is to be calculated.
DOLLARDE(Fractional dollar, Converts a quotation that has been given as a decimal
Fraction) fraction into a decimal number. Fractional dollar is a number
given as a decimal fraction. (In this number, the decimal
value is the numerator of the fraction.) Fraction is a whole
number that is used as the denominator of the decimal
fraction.
DOLLARFR(Decimal dollar, Converts a quotation that has been given as a decimal
Fraction) number into a mixed decimal fraction. The decimal of the
result is the numerator of the fraction that would have
Fraction as the denominator. Decimal dollar is a decimal
number. Fraction is a whole number that is used as the
denominator of the decimal fraction.
DURATION(RATE, pv, FV) Calculates the number of periods required by an investment
to attain the desired value. RATE (a constant) is the interest
rate to be calculated for the entire duration. Entering the
interest rate divided by the periods per year, can calculate
the interest after each period. pv is the present value. FV
is the desired future value of the investment.

Appendix B Description of Functions 395

Working with different Formulae and Functions

 Syntax Description
DURATION_ADD (Settlement, Calculates the duration of a fixed interest security in years.
Maturity, Coupon, Yield, Settlement is the date of purchase of the security. Maturity
Frequency, Basis) is the date on which the security matures (expires). Coupon
is the annual coupon interest rate (nominal rate of interest).
Yield is the annual yield of the security. Frequency is the
number of interest payments per year (1, 2 or 4). Basis
indicates how the year is to be calculated.
EFFECT_ADD(Nominal rate, Calculates the effective annual rate of interest on the basis of
Npery) the nominal interest rate and the number of interest
payments per annum. Nominal interest refers to the amount
of interest due at the end of a calculation period. Nominal
rate is the annual nominal rate of interest. Npery is the
number of interest payments per year.
EFFECTIVE(NOM, P) Calculates the effective annual rate of interest on the basis of
the nominal interest rate and the number of interest
payments per annum. Nominal interest refers to the amount
of interest due at the end of a calculation period. NOM is the
nominal interest. P is the number of interest payment periods
per year.
FV(Rate, NPER, PMT, PV, Type) Returns the future value of an investment based on periodic,
constant payments and a constant interest rate. Rate is the
periodic interest rate. NPER is the total number of periods.
PMT is the annuity paid regularly per period. PV (optional) is
the present cash value of an investment. Type (optional)
defines whether the payment is due at the beginning (1) or
the end (0) of a period.
FVSCHEDULE(Principal, Calculates the accumulated value of the starting capital for a
Schedule) series of periodically varying interest rates. Principal is the
starting capital. Schedule is a series of interest rates.
Schedule has to be entered with cell references, or with a
list.
INTRATE(Settlement, Maturity, Calculates the annual interest rate that results when a
Investment, Redemption, Basis) security (or other item) is purchased at an investment value
and sold at a redemption value with no interest being paid.
Settlement is the date of purchase of the security. Maturity
is the date on which the security is sold. Investment is the
purchase price. Redemption is the selling price. Basis
indicates how the year is to be calculated.
IPMT(Rate, Period, NPER, pv, FV, Calculates the periodic amortization for an investment with
Type) regular payments and a constant interest rate. Rate is the
periodic interest rate. Period is the period for which the
compound interest is calculated. NPER is the total number of
periods during which annuity is paid. Period=NPER, if
compound interest for the last period is calculated. pv is the
present cash value in sequence of payments. FV (optional)
is the desired value (future value) at the end of the periods.
Type (optional) defines whether the payment is due at the
beginning (1) or the end (0) of a period.

Appendix B Description of Functions 396

Working with different Formulae and Functions

 Syntax Description
IRR(Values, Guess) Calculates the internal rate of return for an investment. The
values represent cash flow values at regular intervals; at
least one value must be negative (payments), and at least
one value must be positive (income). Values is an array or
cell range containing the values. Guess (optional) is the
estimated value. If you can provide only a few values, you
should provide an initial guess to enable the iteration.
ISPMT(rate, Period, total_periods, Calculates the level of interest for unchanged amortization
invest) installments. rate sets the periodic interest rate. Period is the
number of installments for calculation of interest.
total_periods is the total number of installment periods.
invest is the amount of the investment.
MDURATION(Settlement, Maturity, Calculates the modified Macauley duration for a security with
Coupon, Yield, Frequency, Basis) an assumed par value of 100 currency units. Settlement is
the date of purchase of the security. Maturity is the date on
which the security matures (expires). Coupon is the annual
nominal rate of interest (coupon interest rate) Yield is the
annual yield of the security. Frequency is the number of
interest payments per year (1, 2 or 4). Basis indicates how
the year is to be calculated.
MIRR(Values, investment, Calculates the modified internal rate of return of a series of
reinvest_rate) investments. Values corresponds to the array or the cell
reference for cells whose content corresponds to the
payments. investment is the rate of interest of the
investments (the negative values of the array) reinvest_rate
is the rate of interest of the reinvestment (the positive values
of the array).
NOMINAL(effect_rate, npery) Calculates the yearly nominal interest rate, given the
effective rate and the number of compounding periods per
year. effect_rate is the effective interest rate. npery is the
number of periodic interest payments per year. Returns a
percentage.
NOMINAL_ADD(Effective_rate, Calculates the yearly nominal rate of interest, given the
Npery) effective rate and the number of compounding periods per
year. Effective_rate is the effective annual rate of interest.
Npery is the number of interest payments per year. Returns
a number.
NPER(Rate, PMT, PV, FV, Type) Returns the number of periods for an investment based on
periodic, constant payments and a constant interest rate.
Rate is the periodic interest rate. PMT is the constant annuity
paid in each period. PV is the present value (cash value) in
a sequence of payments. FV (optional) is the future value,
which is reached at the end of the last period. If FV is
omitted it is assumed to be zero. Type (optional) defines
whether the payment is due at the beginning (1) or the end
(0) of a period.
NPV(Rate, value 1, value 2, Returns the net present value of an investment based on a
…, value 30) series of periodic cash flows and a discount rate. Rate is
the discount rate for a period. value 1, value 2, ..., value
30 are values representing deposits or withdrawals.

Appendix B Description of Functions 397

Working with different Formulae and Functions

 Syntax Description
ODDFPRICE(Settlement, Maturity, Calculates the price per 100 currency units par value of a
Issue, First coupon, Rate, Yield, security, having an odd (short or long) first period. Settlement
Redemption, Frequency, Basis) is the date of purchase of the security. Maturity is the date on
which the security matures (expires). Issue is the date of issue
of the security. First coupon is the first interest date of the
security. Rate is the annual rate of interest. Yield is the annual
yield of the security. Redemption is the redemption value per
100 currency units of par value. Frequency is the number of
interest payments per year (1, 2 or 4). Basis indicates how the
year is to be calculated.
ODDFYIELD(Settlement, Maturity, Calculates the yield of a security that has an odd (short
Issue, First coupon, Rate, Price, or long) first period.
Redemption, Frequency, Basis) Settlement is the date of purchase of the security. Maturity
is the date on which the security matures (expires). Issue is
the date of issue of the security. First coupon is the first
interest date of the security. Rate is the annual rate of
interest. Price is the price of the security. Redemption is
the redemption value per 100 currency units of par value.
Frequency is the number of interest payments per year (1,
2 or 4). Basis is chosen from a list of options and indicates
how the year is to be calculated.
ODDLPRICE(Settlement, Maturity, Calculates the price per 100 currency units par value of a
Last interest, Rate, Yield, security, that has an odd (short or long) last period.
Redemption, Frequency, Basis) Settlement is the date of purchase of the security.
Maturity is the date on which the security matures
(expires). Last interest is the last interest date of the
security. Rate is the annual rate of interest. Yield is the
annual yield of the security. Redemption is the redemption
value per 100 currency units of par value. Frequency is
the number of interest payments per year (1, 2 or 4). Basis
indicates how the year is to be calculated.
ODDLYIELD(Settlement, Maturity, Calculates the yield of a security that has an odd (short or
Last interest, Rate, Price, long) last period. Settlement is the date of purchase of the
Redemption, Frequency, Basis) security. Maturity is the date on which the security matures
(expires). Last interest is the last interest date of the
security. Rate is the annual rate of interest. Price is the
price of the security. Redemption is the redemption value
per 100 currency units of par value. Frequency is the
number of interest payments per year (1, 2 or 4). Basis
indicates how the year is to be calculated.
OPT_BARRIER(spot, vol, r, rf, T, A function following the Black Scholes formula. It calculates the
strike, barrier_low, barrier_up, pricing of a barrier option. spot (required) is the price/value of
rebate, put/call, knock in/out, the asset. vol (required) is the annual volatility of the asset.
barrier_type, greek) rebate (required) is the amount of money paid at maturity if the
barrier was hit. put/call (required) is a string to define if the
option is a (p)ut/ or a (c)all. knock (required) (i)n/(o)ut is a
string to define if the option is of type knock-(i)n or knock-(o)ut.
barrier_type (required) is a string to define whether the barrier
is observed (c)ontinuosly or only at the (e)nd/maturity. greek is
an optional parameter, which if left out causes the function to
return the option price. If included, the function returns price
sensitivities (Greeks) to one of the input parameters, such as
“vega” for sensitivity to volatility.

Appendix B Description of Functions 398

Working with different Formulae and Functions

 Syntax Description
OPT_PROB_HIT(spot, vol, drift, T, Returns the probability that an asset hits a barrier assuming it
barrier_low, barrier_up) follows dS =μdt +vol dW
S
spot is the price/value S of the underlying asset. vol is the
annual volatility of the underlying asset. drift is the μ value of
the formula. T is the time to maturity. barrier_low is the lower
barrier and set to zero if there is no lower barrier. barrier_up
is the upper barrier and is set to zero if there is no upper
barrier. All parameters are required.
OPT_PROB_INMONEY(spot, vol, Returns the probability that an asset will at maturity end up
drift, T, barrier_low, barrier_up, between two barrier levels, assuming it follows
put/call, strike) dS =μdt +vol dW . (If the last two optional parameters,
S
put/call and strike, are specified, the probability of S_T in
[strike, upper barrier] for a call, and S_T in [lower barrier,
strike] for a put will be returned). S_T is the spot at maturity
and ignores the possibility of knock-out before maturity.
spot (required) is the price/value of the asset. vol (required)
is the annual volatility of the asset. drift (required) is the
parameter μ from the formula above. T is the time to maturity
in years. barrier_low (required) is the lower barrier and set
to zero if there is no lower barrier. barrier_up (required) is
the upper barrier and is set to zero if there is no upper
barrier. put/call (optional) is the (p)ut/(c)all indicator. strike
(optional) is the strike level.
OPT_TOUCH(spot, vol, r, rf, T, Returns the pricing of a touch/no-touch option.
barrier_low, barrier_up, spot (required) is the price/value of the asset. vol (required)
foreign/domestic, knock in/out,
is the annual volatility of the asset. r (required) is the interest
barrier_type, greek) rate continuously compounded. rf (required) is the foreign
interest rate continuously compounded. T (required) is the
time to maturity entered in years. strike (required) is the
strike level of the option. barrier_low (required) is the lower
barrier and set to zero if there is no lower barrier. barrier_up
(required) is the upper barrier and is set to zero if there is no
upper barrier. foreign/domestic (required) is a string to
define if the option pays one unit of (d)omestic currency
(cash or nothing) or (f)oreign currency (asset or nothing).
knock (required) (i)n/(o)ut is a string to define if the option is
of type knock-(i)n (touch) or knock-(o)ut (no touch).
barrier_type (required) is a string to define whether the
barrier is observed (c)ontinuously or only at the
(e)nd/maturity. greek is an optional parameter, which if left
out causes the function to return the option price. If included,
the function returns price sensitivities (Greeks) to one of the
input parameters, such as “theta” for time sensitivity.

Appendix B Description of Functions 399

Working with different Formulae and Functions

 Syntax Description
PMT(Rate, NPER, PV, FV, Type) Returns the periodic payment for an annuity with constant
interest rates. Rate is the periodic interest rate. NPER is the
number of periods in which annuity is paid. PV is the present
value (cash value) in a sequence of payments. FV (optional)
is the desired value (future value) to be reached at the end of
the periodic payments. Type (optional) defines whether the
payment is due at the beginning (1) or the end (0) of a
period.
PPMT(Rate, Period, NPER, PV, Returns for a given period the payment on the principal for an
FV, Type) investment that is based on periodic and constant payments
and a constant interest rate. Rate is the periodic interest rate.
Period is the amortization period. NPER is the total number
of periods during which annuity is paid. PV is the present
value in the sequence of payments. FV (optional) is the
desired (future) value. Type (optional) defines whether the
payment is due at the beginning (1) or the end (0) of a
period.
PRICE(Settlement, Maturity, Rate, Calculates the price per 100 currency units of par value of an
Yield, Redemption, Frequency, interest-bearing security. Settlement is the date of purchase
Basis) of the security. Maturity is the date on which the security
matures (expires). Rate is the annual nominal rate of interest
(coupon interest rate). Yield is the annual yield of the
security. Redemption is the redemption value per 100
currency units of par value. Frequency is the number of
interest payments per year (1, 2 or 4). Basis indicates how
the year is to be calculated.
PRICEDISC(Settlement, Maturity, Calculates the price per 100 currency units of par value of a
Discount, Redemption, Basis) discounted security. Settlement is the date of purchase of
the security. Maturity is the date on which the security
matures (expires). Discount is the discount of a security as a
percentage. Redemption is the redemption value per 100
currency units of par value. Basis indicates how the year is
to be calculated.
PRICEMAT(Settlement, Maturity, Calculates the price per 100 currency units of par value of a
Issue, Rate, Yield, Basis) security, that pays interest on the maturity date. Settlement
is the date of purchase of the security. Maturity is the date
on which the security matures (expires). Issue is the date of
issue of the security. Rate is the interest rate of the security
on the issue date. Yield is the annual yield of the security.
Basis indicates how the year is to be calculated.
PV(Rate, NPER, PMT, FV, Type) Returns the present value of an investment resulting from a
series of regular payments. Rate defines the interest rate per
period. NPER is the total number of payment periods. PMT is
the regular payment made per period. FV (optional) defines
the future value remaining after the final installment has been
made. Type (optional) defines whether the payment is due at
the beginning (1) or the end (0) of a period.

Appendix B Description of Functions 400

Working with different Formulae and Functions

 Syntax Description
RATE(NPER, PMT, PV, FV, Type, Returns the constant interest rate per period of an annuity.
Guess) NPER is the total number of periods, during which payments
are made (payment period). PMT is the constant payment
(annuity) paid during each period. PV is the cash value in the
sequence of payments. FV (optional) is the future value,
which is reached at the end of the periodic payments. Type
(optional) defines whether the payment is due at the
beginning (1) or the end (0) of a period. Guess (optional)
determines the estimated value of the interest with iterative
calculation.
RECEIVED(Settlement, Maturity, Calculates the amount paid out at maturity for a fully invested
Investment, Discount, Basis) security. Settlement is the date of purchase of the security.
Maturity is the date on which the security matures.
Investment is the purchase sum. Discount is the
percentage discount on acquisition of the security. Basis
indicates how the year is to be calculated.
RRI(P, pv, FV) Calculates the interest rate resulting from the profit (return) of
an investment. P is the number of periods used for
calculating the interest rate. pv is the present value (must be
>0). FV is the final value of the security.
SLN(Cost, Salvage, Life) Returns the straight-line depreciation of an asset for one
period. The amount of the depreciation is constant during the
depreciation period. Cost is the initial cost of an asset.
Salvage is the value of an asset at the end of the
depreciation. Life is the number of periods in the useful life of
the asset.
SYD(Cost, Salvage, Life, Period) Returns the arithmetically declining value of an asset
(depreciation) for a specified period. It uses the Sum-of-
Years'-Digits method. Cost is the initial cost of an asset.
Salvage is the value of an asset after depreciation. Life is
the period fixing the time span over which an asset is
depreciated. Period defines the period for which the
depreciation is to be calculated. Must use the same units as
life.
TBILLEQ(Settlement, Maturity, Calculates the bond equivalent yield for a treasury bill.
Discount) Settlement is the date of purchase of the security. Maturity
is the date on which the security matures (expires). The
settlement and maturity date must be within a year of each
other. Discount is the percentage discount on acquisition of
the security. Calculated using the 360 days in a year basis
(basis 2).
TBILLPRICE(Settlement, Maturity, Calculates the price per 100 currency units face value of a
Discount) treasury bill. Settlement is the date of purchase of the
security. Maturity is the date on which the security matures
(expires). The settlement and maturity date must be within
a year of each other. Discount is the percentage discount
upon acquisition of the security.

Appendix B Description of Functions 401

Working with different Formulae and Functions

 Syntax Description
TBILLYIELD(Settlement, Maturity, Calculates the yield of a treasury bill. Settlement is the date
Price) of purchase of the security. Maturity is the date on which the
security matures (expires). The settlement and maturity date
must be within a year of each other. Price is the price
(purchase price) of the treasury bill per 100 currency units of
par value.
VDB(Cost, Salvage, Life, S, end, Returns the depreciation of an asset for a specified or partial
Factor, Type) period using a variable declining balance method. Cost is the
initial value of an asset. Salvage is the value of an asset at
the end of the depreciation. Life is the depreciation duration
of the asset. S is the start period of the depreciation, entered
in the same date unit as Life. end is the last period of the
depreciation, entered in the same date unit as Life. Factor
(optional) is the depreciation factor. If factor is omitted, a
factor of two is assumed (the double-declining balance
method). Type is an optional parameter. Type = 1 means a
switch to linear depreciation. In Type = 0, no switch is made.
XIRR(Values, dDates, Guess) Calculates the internal rate of return for a list of payments
which take place on different dates. The calculation is based
on a 365 days per year basis, ignoring leap years. If the
payments take place at regular intervals, use the IRR
function. Values and Dates are a series of payments and the
series of associated date values entered as cell references.
Values shall include at least one negative value and one
positive value. Guess (optional) is a guess for the internal
rate of return. If omitted, the value 10% is assumed.
XNPV(Rate, Values, Dates) Calculates the capital value (net present value) for a list of
payments which take place on different dates. The
calculation is based on a 365 days per year basis, ignoring
leap years. If the payments take place at regular intervals,
use the NPV function. Rate is the internal rate of return for
the payments. Values and Dates are a series of payments
and the series of associated date values entered as cell
references. The first value-date pair indicates the start of the
payments, other dates can be in any order. Values shall
include at least one negative value and one positive value.
YIELD(Settlement, Maturity, Rate, Calculates the yield of a security that pays periodic interest.
Price, Redemption, Frequency, Settlement is the date of purchase of the security. Maturity
Basis) is the date on which the security matures (expires). Rate is
the annual rate of interest. Price is the price (purchase price)
of the security per 100 currency units of par value.
Redemption is the redemption value per 100 currency units
of par value. Frequency is the number of interest payments
per year (1, 2 or 4). Basis indicates how the year is to be
calculated.
YIELDDISC(Settlement, Maturity, Calculates the annual yield of a non-interest-bearing security.
Price, Redemption, Basis) Settlement is the date of purchase of the security. Maturity
is the date on which the security matures (expires). Price is
the price (purchase price) of the security per 100 currency
units of par value. Redemption is the redemption value per
100 currency units of par value. Basis indicates how the
year is to be calculated.

Appendix B Description of Functions 402

Working with different Formulae and Functions

 Syntax Description
YIELDMAT(Settlement, Maturity, Calculates the annual yield of a security, the interest of which
Issue, Rate, Price, Basis) is paid on the date of maturity. Settlement is the date of
purchase of the security. Maturity is the date on which the
security matures (expires). Issue is the date of issue of the
security. Rate is the interest rate of the security on the issue
date. Price is the price (purchase price) of the security per
100 currency units of par value. Basis indicates how the year
is to be calculated.

Statistical analysis functions

Calc includes over 70 statistical functions which enable the evaluation of data from simple
arithmetic calculations, such as averaging, to advanced distribution and probability
computations. Several other statistics-based functions are available through the Add-ins which
are noted at the end of this appendix.

Table 36: Statistical analysis functions

Syntax Description
AVEDEV(number 1, number 2, …, Returns the average of the absolute deviations of data points
number 30) from their mean. Displays the diffusion in a data set. number
1, number 2, …, number 30 are values or ranges that
represent a sample. Each number can also be replaced by a
reference.
AVERAGE(number 1, number 2, Returns the average of the arguments. number 1, number 2,
…, number 30) …, number 30 are numerical values or ranges. Text is
ignored.
AVERAGEA(value 1, value 2, …, Returns the average of the arguments. The value of text is
value 30) taken to be 0. value 1, value 2, …, value 30 are values or
ranges.
AVERAGEIF(range, criteria, Averages the arguments that meet the conditions. If the
average_range) optional average_range is omitted, range, which is required,
is the range of cells that will be averaged. criteria is a
required value which determines which cells in range are
averaged. If the optional average_range is used, it averages
the values of cells of a range that is constructed using the top
left cell of range and applying the dimensions, shape and
size, of average_range. If no cell in range matches the
criteria value, an Error is returned. If no numbers are in the
range to be averaged, an Error is returned.
AVERAGEIFS(average_range, Averages the values of the cells in a range that meet multiple
range 1, criteria 1, range 2, criteria criteria in multiple ranges. average_range, range 1 and criteria
2, …, range 30, criteria 30) 1 are required values. Averages the values of cells in
average_range that meet the criteria 1 in range 1 and the
criteria 2 in range 2, and so on. All ranges must have the same
dimension and size, else an Error is returned. A logical AND is
applied between each array result of each selection; a cell of
average_range is evaluated only if the same position in each
array is the result of a criteria match. If no numbers are in the
result set to be averaged, an Error is returned.

Appendix B Description of Functions 403

Working with different Formulae and Functions

 Syntax Description
B(trials, SP, T_1, T_2) Returns the probability of a sample with binomial distribution.
trials is the number of independent trials. SP is the probability
of success on each trial. T_1 defines the lower limit for the
number of trials. T_2 (optional) defines the upper limit for the
number of trials.
BETADIST(number, alpha, beta, Returns the value of the probability density function or the
Start, End, Cumulative) cumulative distribution function for the beta distribution.
number is the value between Start and End at which to
evaluate the function. alpha is a parameter to the distribution.
beta is a parameter to the distribution. Start (optional) is the
lower bound for number. End (optional) is the upper bound
for number. Cumulative (optional) can be 0 or False to
calculate the probability density function. It can be any other
value or True or omitted to calculate the cumulative
distribution function.
BETAINV(number; alpha, beta, Returns the inverse of the cumulative beta probability density
Start, End) function. number is the value between Start and End at
which to evaluate the function. alpha is a parameter to the
distribution. beta is a parameter to the distribution. Start
(optional) is the lower bound for number. End (optional) is
the upper bound for number.
BINOMDIST(X, trials, SP, C) Returns the individual term binomial distribution probability. X
is the number of successes in a set of trials. trials is the
number of independent trials. SP is the probability of success
on each trial. C = 0 calculates the probability of a single event
and C = 1 calculates the cumulative probability.
CHIDIST(Number, Returns the probability value that a hypothesis will be
degrees_freedom) confirmed from the indicated chi square. The probability
determined by CHIDIST can also be determined by CHITEST.
Number is the chi-square value of the random sample used
to determine the error probability. degrees_freedom is the
degrees of freedom of the experiment.
This function is defined by the ODF as LEGACY.CHIDIST.
Use CHISQDIST for possible greater accuracy.
CHIINV(number, Returns the inverse of the one-tailed probability of the chi-
degrees_freedom) squared distribution. number is the value of the error
probability. degrees_freedom is the degrees of freedom of
the experiment.
This function is defined by the ODF as LEGACY.CHIINV. Use
CHISQINV for possible greater accuracy.
CHISQDIST(Number, Degrees of Returns the value of the probability density function or the
Freedom, Cumulative) cumulative distribution function for the chi-square distribution.
Number is the value at which you want to evaluate the
distribution. Degrees of Freedom is the number of degrees of
freedom. Cumulative (optional) is a logical value that determines
the form of the function. If cumulative is TRUE, CHISQDIST
returns the cumulative distribution function; if FALSE, it returns
the probability density function. If omitted, it is assumed TRUE.

Appendix B Description of Functions 404

Working with different Formulae and Functions

 Syntax Description
CHISQINV(Probability, Degrees of Returns the inverse of CHISQDIST(x, Degrees of Freedom,
Freedom) TRUE()).
Probability is the probability value for which the inverse of the
chi square distribution is to be calculated. Degrees of Freedom
is the number of degrees of freedom.
CHITEST(Data_B, data_E) Returns the chi-square distribution from a random distribution
of two test series based on the chi-square test for
independence. The probability determined by CHITEST can
also be determined with CHIDIST, in which case the chi
square of the random sample must then be passed as a
parameter instead of the data row. Data_B is the array of the
observations. data_E is the range of the expected values.
This function is defined by the ODF as LEGACY.CHITEST.
CONFIDENCE(alpha, STDEV, Returns the (1-alpha) confidence interval for a normal
size) distribution. alpha is the level of the confidence interval.
STDEV is the standard deviation for the total population. size
is the size of the total population.
CORREL(Data_1, Data_2) Returns the correlation coefficient between two data sets.
Data_1 is the first data set. Data_2 is the second data set.
Both arrays shall be the same size and shape. Any empty
element or non-numeric value in an element will cause the
corresponding element to be ignored.
COUNT(value 1, value 2, Counts how many numbers are in the list of arguments. Text
…, value 30) entries are ignored. value 1, value 2, …, value 30 are
values or ranges which are to be counted.
COUNTA(value 1, value 2, Counts how many values are in the list of arguments. Text
…, value 30) entries are also counted, even when they contain an empty
string of length 0. If an argument is an array or reference,
empty cells within the array or reference are ignored. value
1, value 2, …, value 30 are up to 30 arguments representing
the values to be counted.
COVAR(Data_1, Data_2) Returns the covariance of the product of paired deviations.
Data_1 is the first data set. Data_2 is the second data set.
Any empty element or non-numeric value in an element will
cause the corresponding element to be ignored.
CRITBINOM(trials, SP, alpha) Returns the smallest value for which the cumulative binomial
distribution is less than or equal to a criterion value. trials is
the total number of trials. SP is the probability of success for
one trial. alpha is the threshold probability to be reached or
exceeded.
DEVSQ(number 1, number 2, Returns the sum of squares of deviations based on a sample
…, number 30) mean. number 1, number 2, …, number 30 are numerical
values or ranges representing a sample.
EXPONDIST(Number; lambda, C) Returns the value of the probability density function or the
cumulative distribution function for the exponential
distribution. Number is the value of the function. lambda is
the parameter value. C is a logical value that determines the
form of the function. C = 0 calculates the density function, and
C = 1 calculates the distribution function.

Appendix B Description of Functions 405

Working with different Formulae and Functions

 Syntax Description
FDIST(Number, Calculates the values of an F probability distribution. Number
degrees_freedom_1, is the value for which the F distribution is to be calculated.
degrees_freedom_2) degrees_freedom_1 is the degrees of freedom in the
numerator in the F distribution. degrees_freedom_2 is the
degrees of freedom in the denominator in the F distribution.
In the ODF specification this is named LEGACY.FDIST and a
new FDIST has been defined which has yet to be
implemented in Calc.
FINV(number, Returns the inverse of the F probability distribution. number
degrees_freedom_1, is the probability value for which the inverse F distribution is to
degrees_freedom_2) be calculated. degrees_freedom_1 is the number of degrees
of freedom in the numerator of the F distribution.
degrees_freedom_2 is the number of degrees of freedom in
the denominator of the F distribution. In the ODF specification
this is named LEGACY.FINV and a new FINV has been
defined which has yet to be implemented in Calc.
FISHER(Number) Returns the Fisher transformation for the given Number.
FISHER is a synonym for ATANH.
FISHERINV(Number) Returns the inverse of the Fisher transformation for the given
Number. FISHERINV is a synonym for TANH.
FORECAST(value, data_Y, Extrapolates future values based on existing x and y values.
data_X) value is the x value, for which the y value of the linear
regression is to be returned. data_Y is the array or range of
known Y-values. data_X is the array or range of known X-
values. Does not work for exponential functions. Both arrays
must be the same size and shape. A non-numeric value in an
element causes the corresponding element to be ignored.
FTEST(data_1, data_2) Returns the result of an F test. data_1 is the first record array.
data_2 is the second record array.
GAMMA(Number) Returns the value of the Gamma function. Number is the
value for which the Gamma function is to be calculated.
GAMMADIST(Number, alpha, Returns the value of the probability density function or the
beta, Cumulative) cumulative distribution function for the Gamma distribution.
Number is the value for which the Gamma distribution is to
be calculated. alpha is the parameter Alpha of the Gamma
distribution. beta is the parameter Beta of the Gamma
distribution. Cumulative = 0 calculates the density function,
and Cumulative = 1 calculates the distribution.
GAMMAINV(Number, alpha, beta) Returns the inverse of the GAMMADIST(Number, alpha, beta,
TRUE()). This function allows you to search for variables with
different distribution.
Number is the probability value for which the inverse Gamma
distribution is to be calculated. alpha is the parameter Alpha
of the Gamma distribution. beta is the parameter Beta of the
Gamma distribution.
GAMMALN(Number) Returns the natural logarithm of the Gamma function for the
given Number.
GAUSS(Number) Returns 0.5 less than the standard normal cumulative
distribution for the given Number.

Appendix B Description of Functions 406

Working with different Formulae and Functions

 Syntax Description
GEOMEAN(number 1, number 2, Returns the geometric mean of a sample. number 1, number
…, number 30) 2, …, number 30 are numerical arguments or ranges that
represent the sample.
HARMEAN(number 1, number 2, Returns the harmonic mean of a data set. The harmonic
…, number 30) mean is the reciprocal of the arithmetic mean of reciprocals.
number 1, number 2, …, number 30 are values or ranges
for which you want to calculate the harmonic mean.
HYPGEOMDIST(X, n_sample, Returns the hypergeometric distribution. X is the number of
successes, n_population) successes achieved in the random sample. n_sample is the
size of the random sample. successes is the number of
successes in the total population. n_population is the size of
the total population. This function does not fully comply with
the ODF v1.2 specification, having no logical Cumulative
parameter.
INTERCEPT(data_Y, data_X) Calculates the y-value at which a line will intersect the y-axis
by using known x-values and y-values. data_Y is the array of
Y-values. data_X is the array of X-values
Numbers or names, arrays or references containing numbers
must be used here.
KURT(number 1, number 2, …, Returns the kurtosis of a data set (at least 4 values required).
number 30) number 1, number 2, …, number 30 are numerical
arguments or ranges representing a random sample of
distribution.
Kurtosis characterizes the relative peakedness or flatness of
a distribution compared with the normal distribution. Positive
kurtosis indicates a relatively peaked distribution (compared
to the normal distribution), while negative kurtosis indicates a
relatively flat distribution.
LARGE(data, Rank_c) Returns the Rank_c-th largest value in a data set. data is the
cell range of data. Rank_c is the ranking of the value (2nd
largest, 3rd largest, etc.) written as an integer.
LOGINV(number; mean, STDEV) Returns the inverse of the lognormal distribution for the given
number, a probability value. mean is the arithmetic mean of
the standard logarithmic distribution. STDEV is the standard
deviation of the standard logarithmic distribution.
LOGNORMDIST(Number, mean, Returns the value of the probability density function or the
STDEV, Cumulative) cumulative distribution function for the lognormal distribution
with the mean and standard deviation given. Number, a
probability value. mean is the mean value of the standard
logarithmic distribution. STDEV is the standard deviation of
the standard logarithmic distribution. Cumulative (optional)
= 0 calculates the density function, Cumulative = 1
calculates the distribution.
MAX(number 1, number 2, Returns the maximum value in a list of arguments. number
…, number 30) 1, number_2, …, number 30 are numerical values or
ranges. None-numbers are ignored.

Appendix B Description of Functions 407

Working with different Formulae and Functions

 Syntax Description
MAXA(value 1, value 2, …, value Returns the maximum value in a list of arguments. Unlike
30) MAX, text and logical values can be entered. Text is
evaluated as 0, logical True is treated as 1 and logical False
as 0. value 1, value 2, …, value 30 are values or ranges.
MEDIAN(number 1, number 2, …, Returns the median of a set of numbers. number 1, number
number 30) 2, …, number 30 are values or ranges, which represent a
sample. Each number can also be replaced by a reference.
MEDIAN logically ranks the numbers (lowest to highest). If
given an odd number of values, MEDIAN returns the middle
value. If given an even number of values, MEDIAN returns the
arithmetic average of the two middle values.
MIN(number 1, number 2, …, Returns the minimum value in a list of arguments. number 1,
number 30) number 2, …, number 30 are numerical values or ranges.
MINA(value 1, value 2, …, Returns the minimum value in a list of arguments. Text and
value 30) logical values are evaluated. Text is evaluated as 0, logical
True is treated as 1 and logical False as 0. value 1, value
2, …, value 30 are values or ranges.
MODE(number 1, number 2, Returns the most common value in a data set. number 1,
…, number 30) number 2, …, number 30 are numerical values or ranges. If
several values have the same frequency, it returns the
smallest value. An error occurs if a value does not appear
more than once.
NEGBINOMDIST(X, R, SP) Returns the negative binomial distribution. X is the value
returned for unsuccessful tests. R is the value returned for
successful tests. SP is the probability of the success of an
attempt.
NEGBINOMDIST returns the probability that there will be x
failures before the r-th success, when the constant probability
of a success is sp.
NORMDIST(Number, Mean, Returns the value of the probability density function or the
STDEV, C) cumulative distribution function for the normal distribution with
the mean and standard deviation given.
Number is the value for which the normal distribution is to be
calculated. Mean is the mean value of the normal distribution.
STDEV is the standard deviation of the normal distribution. C
= 0 or FALSE it calculates the probability density function, and
C = 1, TRUE or omitted, it calculates the cumulative
distribution function.
NORMINV(number, mean, Returns the inverse of the normal distribution for the given
STDEV) probability value, number, in the distribution. mean is the
mean value in the normal distribution. STDEV is the standard
deviation of the normal distribution.
NORMSDIST(Number) Returns the standard normal cumulative distribution for the
given Number. This function is defined as
LEGACY.NORMSDIST in the ODF v1.2 specification. This
is exactly NORMDIST(x,0,1,TRUE()).

Appendix B Description of Functions 408

Working with different Formulae and Functions

 Syntax Description
NORMSINV(number) Returns the inverse of the standard normal distribution for the
given probability value, number. number must be 0 < number
< 1. This function is defined as LEGACY.NORMSINV in the
ODF v1.2 specification.
PEARSON(Data_1, Data_2) Returns the Pearson correlation coefficient, r, of two data
sets. Data_1 is the array of the first data set. Data_2 is the
array of the second data set. For an empty element or an
element of type Text or Boolean in Data_1 the element at the
corresponding position of Data_2 is ignored, and vice versa.
Both arrays must be the same size and shape.
PERCENTILE(data, Alpha) Returns the alpha-percentile of data values in an array. data
is the array of data. Alpha is the percentile value between 0
and 1. If Alpha is not a multiple of 1/(n - 1), PERCENTILE
interpolates to determine the value between two data points.
PERCENTRANK(data, value) Returns the percentage rank (percentile) of the given value in
a sample. data is the array of data in the sample.
PERMUT(Count_1, Count_2) Returns the number of permutations for a given number of
objects without repetition. Count_1 is the total number of
objects. Count_2 is the number of objects in each
permutation.
PERMUTATIONA(Count_1, Returns the number of permutations for a given number of
Count_2) objects (repetition allowed, meaning an object can combine
with itself). Count_1 is the total number of objects. Count_2
is the number of objects in each permutation.
PHI(number) Returns the values of the distribution function for a standard
normal distribution for the given number. PHI(number) is a
synonym for NORMDIST(number,0,1,FALSE()).
POISSON(Number, mean, Returns the probability, or the cumulative distribution function
Cumulative) for the Poisson distribution of Number. mean is the middle
value of the Poisson distribution. Cumulative = 0 calculates
the probability density function, and Cumulative = 1
calculates the distribution.
PROB(data, probability, Start, Returns the probability that values in a range are between
End) two limits. data is the array or range of data in the sample.
probability is the array or range of the corresponding
probabilities. Start is the start value of the interval whose
probabilities are to be summed. End (optional) is the end
value of the interval whose probabilities are to be summed. If
this parameter is missing, then End = Start value is assumed.
QUARTILE(data, Type) Returns the quartile of a data set. data is the array of data in
the sample. Type is the number of the quartile to return. (0 =
Min, 1 = 25%, 2 = 50% (Median), 3 = 75% and 4 = Max).
Based on the statistical rank of the data points in data,
QUARTILE returns the percentile value indicated by Type.
The percentile is calculated as Type divided by 4. The
same algorithm used in PERCENTILE is used here to
interpolate between two data points.

Appendix B Description of Functions 409

Working with different Formulae and Functions

 Syntax Description
RANK(value, Data, Type) Returns the rank of the given value in a sample. Data is the
array or range of data in the sample. Type (optional) is the
ranking order, if omitted or 0 data is ranked in ascending
order, if not 0 data is ranked in descending order.
RSQ(data_Y, data_X) Returns the square of the Pearson product moment
correlation coefficient based on the given values. data_Y is
an array of data points. data_X is an array of data points. The
arguments shall be either numbers or names, arrays, or
references that contain numbers.
If an array or reference argument contains Text, Logical
values, or empty cells, those values are ignored; however,
cells with the value zero are included. Both arrays must have
the same size and shape.
SKEW(number 1, number 2, …, Returns the skewness of a distribution. number 1, number 2,
number 30) …, number 30 are numerical values or ranges. There must
be a minimum of three numbers.
SKEWP(number 1, number 2, Calculates the skewness of a distribution using the
…, number 30) population of a random variable. number 1, number 2, …,
number 30 are numerical values or ranges. There must be a
minimum of three numbers.
SLOPE(data_Y, data_X) Returns the slope of the linear regression line. data_Y is the
array or matrix of Y data. data_X is the array or matrix of X
data. Both arrays must have the same size and shape. For
an empty element or an element of type Text or Boolean in y
the element at the corresponding position of x is ignored, and
vice versa.
SMALL(data, Rank_c) Returns the Rank_c-th smallest value in a data set. data is
the cell range of data. Rank_c is the rank of the value (2nd
smallest, 3rd smallest, etc.) written as an integer.
STANDARDIZE(Number, mean, Converts a random variable to a normalized value. Number is
STDEV) the value to be standardized. mean is the arithmetic mean of
the distribution. STDEV is the standard deviation of the
distribution.

STDEV(number 1, number 2, Computes the sample standard deviation of a set of numbers.

…, number 30) number 1, number 2, …, number 30 are numerical
values or ranges representing a sample based on an entire
population.
STDEVA(value 1, value 2, Calculates the standard deviation using a sample set
…, value 30) of values, including values of type Text and Logical.
value 1, value 2, …, value 30 are values or ranges
representing a sample derived from an entire population.
Text has the value 0.
STDEVP(number 1, number 2, Calculates the standard deviation using the population of a
…, number 30) random variable, including values of type Text and Logical.
number 1, number 2, …, number 30 are numerical
values or ranges representing a sample based on an entire
population.

Appendix B Description of Functions 410

Working with different Formulae and Functions

 Syntax Description
STDEVPA(value 1, value 2, …, Calculates the standard deviation based on the entire
value 30) population. value 1, value 2, …, value 30 are values or
ranges representing a sample derived from an entire
population. Text has the value 0. Logical FALSE is 0 and
logical TRUE is 1.
STEYX(data_Y, data_X) Returns the standard error of the predicted y value for each x
in the regression. data_Y is the array or matrix of Y data.
data_X is the array or matrix of X data. Both arrays must
have the same size and shape and contain at least three
numbers.
TDIST(Number, Returns the t-distribution for the given Number.
degrees_freedom, mode) degrees_freedom is the number of degrees of freedom for
the t-distribution. mode = 1 returns the one-tailed test, mode
= 2 returns the two-tailed test. This function is named
LEGACY.TDIST in the ODF v1.2 specification.
TINV(number, degrees_freedom) Returns the inverse of the t-distribution, for the given number
associated with the two-tailed t-distribution.
degrees_freedom is the number of degrees of freedom for
the t-distribution.
TRIMMEAN(data, Alpha) Returns the mean of a data set, ignoring a proportion of high
and low values. data (required) is the array of data in the
sample. Alpha (required) is the fractional number of data
points to exclude from the calculation. For example, if Alpha
= 0.2, 4 points are trimmed from a data set of 20 points (20 x
0.2): 2 from the top and 2 from the bottom of the set.
TTEST(data_1, data_2, mode, Returns the probability associated with a Student’s t-Test.
Type) data_1 is the dependent array or range of data for the first
record. data_2 is the dependent array or range of data for the
second record. mode = 1 calculates the one-tailed
distribution, mode = 2 the two- tailed distribution. Type of t-
test to perform: paired (1), equal variance (homoscedastic)
(2), or unequal variance (heteroscedastic) (3).
VAR(number 1, number 2, Calculates the variance based on a sample. number 1,
…, number 30) number 2, …, number 30 are numerical values or
ranges representing a sample based on an entire
population. Requires at least two numbers.
VARA(value 1, value 2, …, Estimates a variance based on a sample. value 1, value 2,
value 30) …, value 30 are values or ranges representing a sample
derived from an entire population. Text is evaluated as 0.
Logical TRUE is evaluated as 1 and FALSE as 0.
VARP(number 1, number 2, Calculates a variance based on the entire population.
…, number 30) number 1, number 2, …, number 30 are numerical values
or ranges representing an entire population.
VARPA(value 1, value 2, …, Calculates the variance based on the entire population. The
value 30) value of text is 0. value 1, value 2, …, value 30 are values or
ranges representing an entire population. Text is evaluated as
0. Logical TRUE is evaluated as 1 and FALSE as 0.

Appendix B Description of Functions 411

Working with different Formulae and Functions

 Syntax Description
WEIBULL(Number, Alpha, beta, Returns the values of the Weibull distribution at the given
C) Number. Alpha is the alpha parameter of the Weibull
distribution. beta is the beta parameter of the Weibull
distribution. C indicates the type of function: C= 0 the
probability density function is calculated, C=1 the cumulative
distribution function is calculated.
ZTEST(data, mu, sigma) Returns the two-tailed P value of a z test with standard
distribution. data is the array of the data. mu is the value to
be tested. sigma (optional) is the standard deviation of the
total population. If this argument is missing, the standard
deviation of the sample is processed.

Date and time functions

Use these functions for inserting, editing, and manipulating dates and times. LibreOffice handles
and computes a date/time value as a number. When you assign the number format “Number” to a
date or time value, it is displayed as a number. For example, 01/01/2000 12:00 PM, converts to
36526.5. This is just a matter of formatting; the actual value is always stored and manipulated as a
number. To see the date or time displayed in a standard format, change the number format (date
or time) accordingly.
To set the default date format used by Calc, go to Tools > Options > LibreOffice Calc
> Calculate.

When entering dates, slashes or dashes used as date separators may be interpreted
Caution as arithmetic operators. To keep dates from being interpreted as parts of formulas,
and thus returning erroneous results, always place them in quotation marks, for
example, "12/08/52". See also A note about dates on page 392.

Table 37: Data and time functions

Syntax Description
DATE(year, month, day) Converts a date written as year, month, day to an internal
serial number and displays it in the cell’s formatting. year is
an integer between 1583 and 9956 or 0 and 99. month is an
integer between 1 and 12. day is an integer between 1 and
31.
DATEDIF(Start date, End date, Returns the difference in years, months, or days of two date
Interval) numbers, Start date and End date. Interval is entered as “y”,
“m” or “d”, to return the value in years, months or days or as
“ym”, “md” or “yd” for months ignoring the years value; days
ignoring the months and years values; or days ignoring the
months and years values. Start date and End date must be
entered using double quotes.
DATEVALUE(text) Returns the date serial number for text in double quotes using
the current locale. text is a valid date expression.
DAY(Number) Returns the day, as an integer, of the given date value.
Number is the date serial number (a negative date/time value
can be entered) or a date value entered in double quotes.

Appendix B Description of Functions 412

Working with different Formulae and Functions

 Syntax Description
DAYS(Date_2, Date_1) Calculates the difference, in days, between two date values.
Date_1 is the start date. Date_2 is the end date. If Date_2 is
an earlier date than Date_1, the result is a negative number.
Dates can be entered as numbers or text.
DAYS360(Date_1, Date_2, Type) Returns the difference between two dates based on the 360
day year used in interest calculations. If Date_2 is earlier than
Date_1, the function will return a negative number. Type
(optional) determines the type of difference calculation: the
US method (0) or the European method (≠0). Dates can be
entered as numbers or text.
DAYSINMONTH(Date) Calculates the number of days in the month of the given Date.
Date can be entered as a number or text.
DAYSINYEAR(Date) Calculates the number of days in the year of the given Date.
Date can be entered as a number or text.
EASTERSUNDAY(year) Returns the date of Easter Sunday for the entered year. year
is an integer between 1583 and 9956 or 0 and 99 (19xx or
20xx depending on the option set)..
EDATE(Start date, Months) Returns the serial number of the date a number of Months
away from the given Start date. Only months are considered;
days are not used for calculation. Months is the number of
months before (negative) or after (positive) the start date.
Start date may be entered as text or a number.
EOMONTH(Start date, Months) Returns the serial number date of the last day of a month
which falls Months away from the given Start date. Months
is the number of months before (negative) or after (positive)
the start date. Start date may be entered as text or a number.
HOUR(Number) Returns the hour, as an integer, for the given time value.
Number is a time value and can be either text or a number.
ISLEAPYEAR(Date) Determines whether a given Date falls within a leap year.
Returns either 1 (TRUE) or 0 (FALSE). Date must be a full
date for text, a reference to a date value or a serial number.
MINUTE(Number) Returns the minute, as an integer, for the given time value.
Number is a time value.
MONTH(Number) Returns the month, as an integer, for the given date value.
Number is a time value.
MONTHS(Start date, End date, Calculates the difference, in months, between two date
Type) values. Start date is the start (earlier) date. End date is the
end date. Type determines the type of calculation and is one
of two possible values; 1 returns the difference between the
calendar month values in the two dates, disregarding the
day values; 0 returns the number of months that separate
the dates taking into account the day values of the two
dates. If End date is an earlier date than Start date, the
result is a negative number.

Appendix B Description of Functions 413

Working with different Formulae and Functions

 Syntax Description
NETWORKDAYS(Start date, End Returns the number of workdays between Start date and
date, Holidays) End date. Holidays can be deducted. Start date is the date
from which the calculation is carried out. End date is the date
up to which the calculation is carried out. If the start or end
date is a workday, the day is included in the calculation.
Holidays (optional) is a list of holidays. Enter a cell range in
which the holidays are listed individually. Saturdays and
Sundays are considered non-workdays.
NOW() Returns the computer system date and time. The value is
updated when your document recalculates. NOW() is a
function without arguments.
SECOND(Number) Returns the second, as an integer, for the given time value.
Number is a time value.
TIME(hour, minute, second) Returns the time value from values for hours, minutes and
seconds. This function can be used to convert a time based
on these three elements to a decimal time value. hour,
minute and second must all be integers.
TIMEVALUE(text) Returns the time serial number value from text enclosed by
quotes in a time entry format. The value of the decimal
number returned is the result of the date system used under
LibreOffice to calculate date entries.
TODAY() Returns the current computer system date. The value is
updated when your document recalculates. TODAY() is a
function without arguments.
WEEKDAY(Number, Type) Returns the day of the week for the given Number (date
value). The day is returned as an integer based on the type.
Type determines the type of calculation: Type = 1 (assumed if
Type is omitted), the weekdays are counted (1-7) starting
from Sunday (Monday = 2); Type = 2, the weekdays are
counted (1-7) starting from Monday (Monday = 1); Type = 3,
the weekdays are counted (0-6) starting from Monday
(Monday = 0).
WEEKNUM(Number, mode) Calculates the number of the calendar week of the year for
the given date Number. mode sets the start of the week and
the calculation type: 1 = Sunday, any other value = Monday.
WEEKNUM_ADD(Date, Return Calculates the calendar week of the year for a Date. Date is
type) the date within the calendar week. Return type sets the start
of the week and the calculation type: 1 = Sunday, 2 =
Monday. This function returns the same results as the
WEEKNUM function in Excel.
WEEKS(Start date, End date, Calculates the difference in weeks between two dates, Start
Type) date and End date. Type is one of two possible values, 0
(number of whole weeks in the interval) or 1 (returns the
number of different weeks in which the two dates appear).
This function uses the ISO weeknumber.
WEEKSINYEAR(Date) Calculates the number of weeks in a year for a given Date. A
week that spans two years is added to the year in which most
days of that week occur (so any week containing four or
more days in the calendar year of Date is counted).

Appendix B Description of Functions 414

Working with different Formulae and Functions

 Syntax Description
WORKDAY(Start date, Days, Returns a date serial number which is a specified number of
Holidays) work days (Days) before or after an input date, Start date.
Holidays (optional) is a list of holidays. Enter a cell range in
which the holidays are listed individually. Work days exclude
Saturdays and Sundays. This function does not fully
implement the ODFv1.2 specification which allows you to set
the non-work days.
YEAR(Number) Returns the calendar year as an integer according to the
internal calculation rules. Number is the date value in date
serial number format or as a text date, for which the year is to
be returned.
YEARFRAC(Start date, End date, Extracts the number of years (including fractional part)
Basis) between two date values, Start date and End date. Basis is
a value either omitted or between 0 and 4, chosen from a list
of options and indicates how the year is to be calculated (see
Help files). If omitted it is evaluated as 0.
YEARS(Start date, End date, Calculates the difference in years between two dates: the
Type) Start date and the End date. Type calculates the type of
difference. Possible values are 0 (interval) and 1 (in calendar
years).

Logical functions
Use the logical functions to test values and produce results based on the result of the test. These
functions are conditional and provide the ability to write longer formulas based on input or output.

Table 38: Logical functions

Syntax Description
AND(Logical value 1, Logical Returns TRUE if all arguments are TRUE. If any element is
value 2, …, Logical value 30) FALSE, this function returns the FALSE value. Logical value 1,
Logical value 2, …, Logical value 30 are conditions to be
checked. All conditions can be either TRUE or FALSE. If a range is
entered as a parameter, only logical values in the range are
evaluated. The result is TRUE if the logical value in all cells within
the cell range is TRUE.
Bug 70632: Concerning range statement given in Help.
FALSE() Set the logical value to FALSE. The FALSE() function does not
require any arguments.
IF(Test; Then_value, Specifies a logical test to be performed. Test is any value or
Otherwise_value) expression that can be TRUE or FALSE. Then_value (optional) is
the value that is returned if the logical test is TRUE.
Otherwise_value (optional) is the value that is returned if the
logical test is FALSE.
IFERROR(value, alternative Evaluates value; if it is not an error it returns the result for value,
value) or else it returns the alternative value. If value evaluates to a
logical value, then either 1 (for TRUE), or 0 (for FALSE) is
returned.

Appendix B Description of Functions 415

Working with different Formulae and Functions

 Syntax Description
IFNA(value, alternative Evaluates value; if it is not a #N/A error it returns the result for
value) value, or else it returns the alternative value. If value evaluates
to a logical value, then either 1 (for TRUE), or 0 (for FALSE) is
returned.
NOT(Logical value) Reverses the logical value. Logical value is the TRUE or FALSE
value to be reversed.
OR(Logical value 1, Logical Returns TRUE if at least one argument is TRUE. Returns the value
value 2, …, Logical value 30) FALSE if all the arguments have the logical value FALSE. Logical
value 1, Logical value 2, …, Logical value 30 are conditions to
be checked. All conditions can be either TRUE or FALSE.
TRUE() Sets the logical value to TRUE. The TRUE() function does not
require any arguments.
XOR(Logical value 1, Logical Computes the logical XOR of the parameters. If an even number
value 2, …, Logical value 30) of parameters is TRUE it returns FALSE, if an odd number of
parameters is TRUE it returns TRUE.

Information functions
These functions provide information (or feedback) regarding the results of a test for a
specific condition, or a test for the type of data or content a cell contains.

Table 39: Informational functions

Syntax Description
CELL(info_type, Reference) Returns information on a cell such as its address, formatting or
contents of a cell based on the value of the info_type argument.
info_type specifies the type of information to be returned and
comes from a predefined list of arguments. See Help files for
complete listing. info_type is not case sensitive, but it must be
enclosed within quotes. Reference is the address of the cell to
be examined. If Reference is a range, the cell reference moves
to the top left of the range. If Reference is missing, Calc uses
the position of the cell in which this formula is located.
CURRENT() Calculates the current value of a formula at the actual position.
FORMULA(Reference) Displays in the current location, the formula contained in a cell
cell at Reference position. If no formula at Reference can be
found, or if the presented argument is not a reference, returns
the error value #N/A.
INFO(Text) Returns information about the working environment. Text is a
string constant entered in double quotes taken from a list of
arguments. See the Help files for the listing.
ISBLANK(value) Returns TRUE if the referenced cell is blank, else returns
FALSE. If value is of type Number, Text, or Logical, return
FALSE. If value is a reference to a cell, examine the cell; if it is
blank (has no value), return TRUE, but if it has a value, return
FALSE. A cell with the empty string is not considered blank.

Appendix B Description of Functions 416

Working with different Formulae and Functions

 Syntax Description
ISERR(value) Returns TRUE if the value refers to any error value except
#N/A. You can use this function to control error values in certain
cells. If an error occurs, the function returns a logical or
numerical value. value is any value or expression in which a
test is performed to determine whether an error value not equal
to #N/A is present.
ISERROR(value) The ISERROR tests if the cells contain general error values.
ISERROR recognizes the #N/A error value. If an error occurs,
the function returns a logical or numerical value. value is any
value where a test is performed to determine whether it is an
error value.
ISEVEN(value) Returns TRUE if the given value is an even integer, or FALSE if
the value is odd. If the value is not an integer, the function
evaluates only the integer part of the value.
ISEVEN_ADD(Number) Tests for even numbers. Returns TRUE (1) if the integer part of
Number returns a whole number when divided by 2.
ISFORMULA(reference) Returns TRUE if a cell is a formula cell. If an error occurs, the
function returns a logical or numerical value. reference
indicates the reference to a cell in which the test will be
performed.
ISLOGICAL(value) Returns TRUE if the cell contains a logical number format. The
function is used in order to check for both TRUE and FALSE
values in certain cells. If an error occurs, the function returns a
logical or numerical value. value is the cell reference to be
tested for logical number format.
ISNA(value) Returns TRUE if value contains the #N/A (value not available)
error value. If an error occurs, the function returns a logical or
numerical value. value is the cell, value or expression to be
tested.
ISNONTEXT(value) Return TRUE if the parameter does not have type Text, else
return FALSE. If an error occurs, the function returns a logical
or numerical value. value is any value or expression where a
test is performed to determine whether it is a text or numbers or
a Boolean value. Empty cells are considered non-text and will
return TRUE.
ISNUMBER(value) Returns TRUE if value evaluates to a number. If an error
occurs, the function returns a logical or numerical value. value
is any expression to be tested to determine whether it is a
number or text. TRUE (1) and FALSE (0) are evaluated as
numbers.
ISODD(value) Returns TRUE if value evaluates as an odd integer, else
FALSE. value is truncated to an integer before evaluation.
TRUE (1) and FALSE (0) are evaluated as numbers. Text
returns an error. Zero is evaluated FALSE.
ISODD_ADD(Number) Returns 1 if Number does not return a whole number when
divided by 2, else 0. Number is the number to be tested.
Does not return logical type TRUE/FALSE like ISODD;
returns number.

Appendix B Description of Functions 417

Working with different Formulae and Functions

 Syntax Description
ISREF(value) Returns TRUE if value is of type reference (including a
reference list), else return FALSE. If an error occurs, the
function returns a logical or numerical value. It does not
evaluate the content of the reference.
ISTEXT(value) Returns TRUE if value is of type text, else FALSE. If an error
occurs, the function returns a logical or numerical value. Value
is a value, number, Boolean value, or error value to be tested. If
value is a reference, the content of the reference is evaluated.
N(value) Return the number of value. If value is a reference the
reference content is evaluated. If value is a logical value, 1 is
returned for TRUE, else 0. If value is an error it is returned.
Text returns a 0.
NA() Returns the error value #N/A.
TYPE(value) Evaluates value and returns a number indicating its type. If an
error occurs, the function returns the error. The numerical value
from which the data type is determined is; 1 = number, 2 =
text, 4 = Boolean value, 8 = formula, 16 = error value. If value
references an empty cell, an error is returned. The results of a
formula in a reference are not evaluated.

Database functions
This section deals with functions used with data organized as one row of data for one record.
The Database category should not be confused with the Base database component in
LibreOffice. A Calc database is simply a range of cells that comprises a block of related data
where each row contains a separate record. There is no connection between a database in
LibreOffice and the Database category in LibreOffice Calc.
The database functions use the following common arguments:
Database is a range of cells which define the database.
Database field specifies the column which the function operates on after the search criteria of the
first parameter is applied and the data rows are selected. It is not related to the search criteria
itself. The number 0 specifies the whole data range. To reference a column by using the
column header name, place quotation marks around the header name.
Search criteria is a cell range containing the search criteria.. Empty cells in the search
criteria range will be ignored.

All of the search criteria arguments for the database functions support regular
expressions. For example, “all.*” can be entered to find the first location of “all”
Note followed by any characters. To search for text that is also a regular expression, precede
every character with a \ character. You can switch the automatic evaluation of regular
expressions on and off in Tools > Options > LibreOffice Calc > Calculate.

Appendix B Description of Functions 418

Working with different Formulae and Functions

Table 40: Database average

Syntax Description
DAVERAGE(Database, Returns the average of the values in a given database field from
Database field, Search the records (rows) in a database that match the search criteria.
criteria) Database field cannot be 0 or empty.
DCOUNT(Database, Counts the number of records (rows) in a database that match the
Database field, Search search criteria and contain numerical values. Database field can
criteria) be empty or 0.
DCOUNTA(Database, Counts the number of rows (records) in a database that match the
Database field, Search specified search criteria and contain numeric or alphanumeric
criteria) values. Database field can be empty or 0.
DGET(Database, Database Returns the field value from a record in a database, which
field, Search criteria) matches the search criteria. The search criteria must return a
single value. In case of an error, the function returns either
#VALUE! for no record or field values found, or Err502 for more
than one cell in the search criteria.
DMAX(Database, Database Returns the maximum value of a field in a database (all records)
field, Search criteria) that matches the specified Search criteria. The search supports
regular expressions.
DMIN(Database, Database Returns the minimum value of a field in a database that matches
field, Search criteria) the specified Search criteria. The search supports regular
expressions.
DPRODUCT(Database, Multiplies all cells of a data range where the cell contents match
Database field, Search the Search criteria. The search supports regular expressions.
criteria)
DSTDEV(Database, Finds the sample standard deviation in a given field from the
Database field, Search records (rows) in a database that match a search criteria.
criteria)
DSTDEVP(Database, Finds the population standard deviation in a given field from the
Database field, Search records (rows) in a database that match a search criteria.
criteria)
DSUM(Database, Database Finds the sum of values in a given field from the records (rows) in
field, Search criteria) a database that match a search criteria. The search supports
regular expressions.
DVAR(Database, Database Finds the sample variance in a given field from the records (rows)
field, Search criteria) in a database that match a search criteria.
DVARP(Database, Database Finds the population variance in a given field from the records
field, Search criteria) (rows) in a database that match a search criteria.

Appendix B Description of Functions 419

Working with different Formulae and Functions

Array functions
When using the Function Wizard for Array functions, those returning an array result have the
Array check-box automatically selected.

Table 41: Array functions

Syntax Description
FREQUENCY(data, classes) Categorizes values into intervals and counts the number of values
in each interval. Returns the results as a vertical array containing
one more result than the number of classes. data is the data that
should be categorized and counted according to the given
intervals. classes is the array containing the upper boundaries
determining the intervals the values in data should be grouped by.
GROWTH(data_Y, data_X, Calculates predicted exponential growth by using existing data.
new_data_X, Function_type) data_Y is the Y data array. data_X (optional) is the X data array.
new_data_X (optional) is the X data array, for which the values
are to be calculated. If new_data_X is omitted it is assumed to be
the same size as data_X. If both arrays are omitted, they are
assumed to be the array {1,2,3,...} that is the same size as the Y
data array. Function_type is optional. If Function_type = 1 or
omitted, functions in the form y = b*m^x are calculated, else y =
m^x functions are calculated.
LINEST(data_Y, data_X, Returns the parameters of the (simple or multiple) linear
Linear_type, stats) regression equation for the given data and, optionally, statistics on
this regression. The equation for the line is y = mx + c, or y =
m1x1 + m2x2 + … + c for multiple ranges of x-values, where the
dependent y-values are a function of the independent x-values.
The m-values are coefficients corresponding to each x-value, and
c is a constant value. data_Y is a single row or column range
specifying the y coordinates in a set of data points. data_X
(optional) is a corresponding single row or column range
specifying the x coordinates. If data_X is omitted it defaults to
{1,2,3,..., n}. If there is more than one set of variables data_X may
be a range with corresponding multiple rows or columns.
Linear_type (optional): if FALSE the straight line found is forced
to pass through the origin (the constant c is zero; y = mx). If
omitted, Linear_type defaults to TRUE (the line is not forced
through the origin). stats (optional): If stats = 0, only the
regression coefficient is calculated. Otherwise, other statistics will
be returned, see the Help file for full information.
LOGEST(data_Y, data_X, Calculates the adjustment of the entered data as an exponential
Function_type, stats) regression curve (y=b*m^x). data_Y is the Y Data array. data_X
(optional) is the X data array. Function_type (optional): If
Function_type = 0, functions in the form y = m^x are calculated.
Otherwise, y = b*m^x functions are calculated. stats (optional). If
stats = 0, only the regression coefficient is calculated; if stats = 1
other statistics will be returned, see the Help file for full
information.
MDETERM(array) Returns the determinant of a square array. This function returns a
value in the current cell; it is not necessary to define a range for
the results. array is an array in which the determinants are
defined. The Array check-box is not automatically selected.

Appendix B Description of Functions 420

Working with different Formulae and Functions

 Syntax Description
MINVERSE(array) Returns the inverse array. array is a square array that is to be
inverted.
MMULT(array, array) Calculates the array product of two arrays. The number of
columns for array 1 must equal the number of rows for array 2.
array at first place is the first array used in the array product.
array at second place is the second array with the same number
of rows as the first array has columns.
Bug 71128: Same name for two variables.
MUNIT(Dimensions) Returns the unitary square array of a certain size. The unitary
array is a square array where the main diagonal (top left to bottom
right) elements are set to 1 and all other array elements are set to
0. Dimensions refers to the column and row size of the array.
SUMPRODUCT(Array 1, Multiplies corresponding elements in the given arrays, and returns
Array 2, …, Array 30) the sum of those products. Array 1, Array 2, …, Array 30 are
arrays whose corresponding elements are to be multiplied. At
least one array must be part of the argument list. If only one array
is given, the array elements are summed. Arrays must have the
same size and shape. Non numeric elements are treated as 0.
The Array check-box is not automatically selected.
SUMX2MY2(array_x, Returns the sum of the difference of the squares of corresponding
array_y) values in two arrays. array_x is the first array whose elements are
to be squared and added. array_y is the second array whose
elements are to be squared and subtracted. Arrays must have the
same size and shape. The Array check-box is not automatically
selected.
SUMX2PY2(array_x, array_y) Returns the sum of the sum of the squares of the individual values
in each array. array_x is the first array whose arguments are to be
squared and summed. array_y is the second array, whose
arguments are to be squared and summed and then summed with
the result from the first array. Arrays must have the same size
and shape. The Array check-box is not automatically selected.
SUMXMY2(array_x, array_y) Adds the squares of the difference between corresponding
values in two arrays. array_x is the first array from whose
elements the corresponding elements of array_y are to be
subtracted. The results of each subtraction are summed and the
results squared. Arrays must have the same size and shape. The
Array check-box is not automatically selected.
TRANSPOSE(array) Transposes the rows and columns of an array. array is the array in
the spreadsheet that is to be transposed.
TREND(data_Y, data_X, Returns values along a linear trend. data_Y is the Y data array.
new_data_X, Linear_type) data_X (optional) is the X data array. new_data_X (optional) is
the array of the X data, which are used for recalculating values. If
new_data_X is omitted it is assumed to be the same size as
data_X. If both arrays are omitted, they are assumed to be the
array {1,2,3,...} that is the same size as the Y data array.
Linear_type is optional. If Linear_type = 1 or omitted, functions
in the form y = mx + c are calculated, else y = mx functions are
calculated.

Appendix B Description of Functions 421

Working with different Formulae and Functions

Spreadsheet functions
Use spreadsheet functions to search and address cell ranges and provide feedback regarding
the contents of a cell or range of cells. You can use functions such as HYPERLINK() and DDE()
to connect to other documents or data sources.

Table 42: Spreadsheet functions

Syntax Description
ADDRESS(row, column, ABS, A1, Returns a cell address (reference) as text, according to the
sheet) specified row and column numbers. Optionally, whether the
address is interpreted as an absolute address (for
example, $A$1) or as a relative address (as A1) or in a
mixed form (A$1 or $A1) can be determined. The name of
the sheet can also be specified. row (required) is the row
number for the cell reference. column (required) is the
column number for the cell reference (the number, not the
letter). ABS (optional) determines the type of reference
and is a value between 1 and 4. See the Help files for
explanation of list numbers. Optional A1 if set to 0 uses the
R1C1 notation, else it uses the A1 notation. Optional sheet
is the name of the sheet entered in double quotes. If using
R1C1 notation, ADDRESS returns address strings using
the exclamation mark '!' as the sheet name separator. The
function still uses the dot '.' sheet name separator with A1
notation.
When opening documents from ODF 1.0/1.1 format, the
ADDRESS functions that show a sheet name as the fourth
parameter will shift that sheet name to become the fifth
parameter. A new fourth parameter with the value 1 will be
inserted.
When saving a document in ODF 1.0/1.1 format, if the
ADDRESS function has a fourth parameter, that parameter
will be removed. A spreadsheet should not be saved in the
old ODF 1.0/1.1 format if A1 is set to 0.
AREAS(reference) Returns the number of individual ranges that belong to a
multiple range. A range can consist of contiguous cells or a
single cell. reference is a reference list of the ranges. The
function expects a single argument. Multiple ranges can be
entered using the tilde (~) (Union) operator or a semicolon
(;) as the divider, but the semicolon gets automatically
converted to the tilde operator after the function is entered
into the spreadsheet. If you state multiple ranges and you
use the semicolon separator, you must enclose them in
additional parentheses. The tilde is the union range
operator. See Chapter 7 for range operators.
Multiple ranges can be entered into the reference input
box in two ways. Firstly they can be be typed directly into
the argument's input box, noting the parentheses
constraint mentioned above for the semicolon. Secondly,
by clicking the Shrink button to the right of the input box
and then clicking and dragging in the sheet to select cell
ranges. Add the range operator between selections. Note
the parentheses constraint above for use of the semicolon.
Bug 71225 concerning problems inputting data.

Appendix B Description of Functions 422

Working with different Formulae and Functions

 Syntax Description
CHOOSE(Index, value1, …, Returns a value from a list of up to 30 values. Index is a
value30) reference or number between 1 and 30 indicating which
value is to be taken from the list. value1, …, value30 is the
list of values entered as any number type, reference, or
formula expression. Only the selected value from the list is
evaluated, any other formulas in the list are not checked for
validity.
COLUMN(reference) Returns the column number of a reference. If the
reference is a single cell, the column number of the cell is
returned; if the parameter is a cell range containing more
than one column, the corresponding column numbers are
returned in a single-row array, if the formula is entered as
an array formula. If the cell range is not entered as an
array formula, only the column number of the first cell
within the range is determined. If no reference is entered,
the column number of the cell in which the formula is
entered is returned as Calc automatically sets the
reference to the current cell.
COLUMNS(array) Returns the number of columns in the given reference.
array is the reference to a cell range whose total number
of columns is to be found. The argument can also be a
single cell.
DDE(server, File, range, mode) Dynamic Data Exchange. Returns the result of a DDE
request. If the contents of the linked range or section
changes, the returned value will also change. The
spreadsheet can be reloaded, or Edit > Links selected, to
see the updated links. Cross-platform links, for example
from an LibreOffice installation running on a Windows
machine to a document created on a Linux machine, are
not supported. server is the name of a server application.
LibreOffice applications have the server name “Soffice”.
File is the complete file name, including path. range is the
area containing the data to be evaluated. mode is an
optional parameter that controls the method by which the
DDE server converts its data into numbers. See the Help
files for information on choices.
An earlier bug that caused this function to crash LibreOffice
has been fixed in v4.1.4 and later releases.
ERRORTYPE(reference) Evaluates the cell value at reference location. If the cell
contains an error then a logical or numerical value is
returned else it returns #N/A. The numerical value is the
error number (see Help for full listing). For a cell
containing the #N/A error, a value of 32767 is returned.

Appendix B Description of Functions 423

Working with different Formulae and Functions

 Syntax Description
GETPIVOTDATA(Data Field, Pivot The GETPIVOTDATA function returns a calculated result
Table, Field Name/Item1, Field value from a pivot table. The value is addressed using field
Name/Item2, …, Field and item names, so it remains valid if the layout of the pivot
Name/Item30) table changes.
Two different syntax definitions can be used: the syntax
This is the syntax used in the shown on the left and
Function Wizard. GETPIVOTDATA(Pivot Table, Constraints)
For syntax 1, Data Field is a string that selects one of the
pivot table's data fields. The string can be the name of the
source column, or the data field name as shown in the
table (like "Sum – Sales"). Pivot Table is a reference to a
cell or cell range that is positioned within a pivot table or
contains a pivot table. If the cell range contains several
pivot tables, the table that was created last is used. If no
Field Name /ItemX pairs are given, the grand total is
returned. Otherwise, each pair adds a constraint that the
result must satisfy. Field Name is the name of a field from
the pivot table. ItemX is the name of an item from that field.
A maximum of 30 Field Name/ItemX pairs can be entered.
The second syntax is assumed if exactly two parameters
are given, Pivot Table has the same meaning as in the
first syntax. Constraints is a space-separated list. Entries
can be quoted (single quotes). The whole string must be
enclosed in quotes (double quotes), unless you reference
the string from another cell. See the Help file for detailed
information.
In some versions of LibreOffice, the second syntax
variation returns a #REF error. See Bug 71234.
HLOOKUP(search_criteria, array, Searches for a value given in search_criteria in the first
Index, sorted) row of the given array, and returns the value from the row
given in Index for the column in which the search item was
found. If sorted is 0 or FALSE the first row of array need
not be sorted, else the first row of array must be sorted in
alpha-numerical and logical order. The search supports
regular expressions.
HYPERLINK(URL, CellText) When the text in a cell that contains the HYPERLINK
function is Ctrl-clicked (the cursor becomes a pointing hand
when correctly positioned), the hyperlink opens. URL
specifies the link target. The optional CellText argument is
the text displayed in the cell. If either argument is a text
string, it must be entered in double quotes. If the CellText
parameter is not specified, the URL text is displayed.

Appendix B Description of Functions 424

Working with different Formulae and Functions

 Syntax Description
INDEX(reference, row, column, Given a reference, returns the value at the given row and
range) column intersection (starting numbering at 1, relative to
the top left of the reference) of the given area range. If
range is not given, it is assumed to be 1 (the first and
possibly only area).
If row is omitted or empty or 0, an entire column of the
given area range in reference is returned. If column is
omitted or empty or 0, an entire row of the given area
range in reference is returned. If both, row and column,
are omitted or empty or 0, the entire given area range is
returned.
If reference is a one-dimensional column vector, column
is optional or can be omitted. If reference is a one-
dimensional row vector, row is optional, which effectively
makes row act as the column offset into the vector, or can
be omitted.
If row or column have a value greater than the dimension
of the corresponding given area range, an Error is
returned.
The Array checkbox must be selected in this function
unless row and column are both included.
Bug 71325: Returns #VALUE error when optional
arguments are omitted.
INDIRECT(ref, A1) Returns a reference given a string representation of a
reference as ref. This function can also be used to return
the area of a corresponding string. ref is a reference to a
cell or an area (in text form) from which to return the
contents. Unless ref refers to a cell containing a reference,
ref must be entered in double quotes. A1 (optional) - if set
to 0, the R1C1 notation is used. If this parameter is absent
or set to another value than 0, the A1 notation is used.
LOOKUP(Search criterion, Search Returns the contents of a cell either from a one-row or one-
vector, result_vector) column range or from an array. Optionally, the assigned
value (of the same index) is returned in a different column
and row. As opposed to VLOOKUP and HLOOKUP, search
and result vectors may be at different positions; they do not
have to be adjacent. Additionally, the search vector for the
LOOKUP must be sorted ascending, otherwise the search will
not return any usable results. The search supports regular
expressions. Search criterion is the value to be searched
for; entered either directly or as a reference. Search vector is
the single-row or single-column area to be searched.
result_vector is another single-row or single-column range
from which the result of the function is taken. The result is the
cell of the result vector with the same index as the instance
found in the search vector.
When given two parameters, Search vector is first
examined: If Search vector is square or is taller than it is
wide (more rows than columns), LOOKUP searches in
the first column (similar to VLOOKUP), and returns the
corresponding value in the last column. If Search vector
covers an area that is wider than it is tall (more columns

Appendix B Description of Functions 425

Working with different Formulae and Functions

 Syntax Description
than rows), LOOKUP searches in the first row (similar to
HLOOKUP), and returns the corresponding value in the
last row.
Bug 71589: This fails if an alphabetic character is used for
the search criterion.
MATCH(Search criterion, Returns the relative position of an item in an array that
lookup_array, Type) matches a specified value. The function returns the
position of the value found in lookup_array as a number.
Search criterion is the value which is to be searched for.
lookup_array is the vector to be searched. A lookup array
can be a single row or column, or part of a single row or
column. Type may take the values 1, 0, –1 or be omitted.
If Type is value 1 or omitted, lookup_array must be sorted
ascending and the function finds the largest value that is
less than or equal to Search criterion.
If Type is of value 0 the function finds the largest value that
is less than or equal to Search criterion. Values in
lookup_array do not need to be sorted.
If Type is of value -1, the function returns the smallest
value that is greater than or equal to Search criterion in a
lookup_array where values are sorted in descending
order. The search supports regular expressions.
OFFSET(reference, rows, columns, Returns the value of a cell offset by a certain number of
height, width) rows and columns from a given reference point. reference
is the cell from which the function searches for the new
reference. rows is the number of cells by which the
reference was corrected up (negative value) or down.
columns is the number of columns by which the reference
was corrected to the left (negative value) or to the right.
height is the optional vertical height for an area that starts
at the new reference position. width is the optional
horizontal width for an area that starts at the new reference
position.
ROW(reference) Returns the row number of a cell reference. If the reference
is a cell, it returns the row number of the cell. If the
reference is a cell range, it returns the corresponding row
numbers in a one-column array if the formula is entered as
an array formula. If the ROW function with a range
reference is not used in an array formula, only the row
number of the first range cell will be returned. reference is
a cell, an area, or the name of an area. If a reference is not
indicated, Calc automatically sets the reference to the
current cell.
ROWS(array) Returns the number of rows in a reference or array. array
is the reference or named area whose total number of rows
is to be determined.
SHEET(reference) Returns the sheet number of a reference or a string
representing a sheet name. If no parameters are entered,
the result is the sheet number of the spreadsheet
containing the formula. reference (optional) is the
reference to a cell, an area, or a sheet name string.

Appendix B Description of Functions 426

Working with different Formulae and Functions

 Syntax Description
SHEETS(reference) Determines the number of sheets in a reference. If no
parameters are entered, the result is the number of sheets
in the current document. reference (optional) is the
reference to a sheet or an area.
STYLE(Style, Time, Style2) Applies a style Style to the cell containing the formula for a
length of time Time, after which the final style Style2 is
applied. Styles are listed (and may be created) in the
Format > Styles and Formatting (F11) menu and are text
entries entered in double quotes.
The initial style is applied for Time seconds after the cell
itself is recalculated. Please note that a manual
recalculation (F9 key or Tools > Cell Contents >
Recalculate) will not trigger the initial style.
Time and Style2 may together be omitted; Style is then
applied permanently.
This function always returns the value 0, allowing it to be
added to another function without changing the value.
VLOOKUP(Search criterion, array, Searches the first column of an array for the value given
Index, sort order) by Search criterion and if found returns the cell value at
the intersection of the row in which it is found and the
column index given by Index. The search supports regular
expressions. Search criterion is the value searched for in
the first column of the array. If text, it must be entered in
double quotes. array is the reference, which must include
at least two columns. Index is the number of the column in
the array that contains the value to be returned. The first
column has the number 1. If the sort order parameter is
omitted or set to TRUE or not 0, it is assumed that the data
is sorted in ascending order. If the exact Search criterion
is not found, the last value that is smaller than the criterion
will be returned. If the sort order parameter is set to
FALSE or zero, an exact match must be found, otherwise
the error Error: Value Not Available will be the result. Thus
with a value of zero the data does not need to be sorted in
ascending order.

Text functions
Use Calc’s text functions to search and manipulate text strings or character
codes. Table 43: Text functions

Syntax Description
ARABIC(Text) Calculates the value of a Roman numeral. The value range
must be between 0 and 3999 (“MMMIM”). Text is the text that
represents a Roman numeral. It is not case sensitive and is
entered in double quotes.
ASC(text) The ASC function converts full-width to half-width ASCII and
katakana characters. Returns a text string. text is the text that
contains characters to be converted.

Appendix B Description of Functions 427

Working with different Formulae and Functions

 Syntax Description
BAHTTEXT(Number) Converts a number to Thai text, including the Thai currency
names. Number is any number. "Baht" is appended to the
integral part of the number, and "Satang" is appended to the
decimal part of the number.
BASE(number, radix, Minimum Converts a positive integer to a specified base then into text
length) using the characters from the base’s numbering system
(decimal, binary, hexadecimal, etc.). Only the digits 0-9 and the
letters A-Z are used. number is the positive integer to be
converted. radix is the base of the number system. It may be
any positive integer between 2 and 36. Minimum length
(optional) is the minimum length of the character sequence that
has been created. If the text is shorter than the indicated
minimum length, zeros are added to the left of the string.
CHAR(number) Converts a number into a character according to the current
code table. The number can be a two-digit or three-digit integer
number. number is a number between 1 and 255 representing
the code value for the character.
CLEAN(text) Removes all non-printing characters from the string entered into
text. Text is entered using double quotes.
CODE(text) Returns a numeric code for the first character in a text string.
text is the text for which the code of the first character is to be
found and is entered in double quotes.
CONCATENATE(text 1, text 2, Combines several text strings into one string. text 1, text 2, …,
…, text 30) text 30 are text passages that are to be combined into one string.
DECIMAL(text, radix) Converts text with characters from a number system to a
positive integer in the decimal system. The radix value defines
the number system to which the text belongs. Any characters
not in the number system defined are ignored. text is the text to
be converted and must be entered using double quotes. The
text field is not case-sensitive. radix is the base of the number
system from which the conversion is to take place. It may be
any positive integer between 2 and 36.
DOLLAR(value, decimals) Converts a number to text in the locale currency format,
rounded to a specified decimal place. value is the number to be
converted; it can be a number, a reference to a cell containing
a number, or a formula which returns a number. decimals
(optional) is the number of decimal places to be used. If no
decimals value is specified, all numbers in currency format will
be displayed with two decimal places. The currency format is
set in the system settings.
EXACT(text_1, text_2) Compares two text strings and returns TRUE if they are
identical. This function is case-sensitive. text_1 is the first
text to compare. text_2 is the second text to compare. Both
arguments if entered directly must be in double quotes.

Appendix B Description of Functions 428

Working with different Formulae and Functions

 Syntax Description
FIND(find_text, text, position) Looks for a string of text within another string and returns the
position in the searched text where the searched-for text begins.
Where to begin the search can also be defined. The search
term can be a number or any string of characters. The search is
case-sensitive. find_text is the text to be found. text is the text
which is being searched. position (optional) is the position in
the text from which the search starts. Text must be entered in
double quotes.
FIXED(number, Decimals, No Returns a number, displayed as text, with a fixed number of
thousands separator) decimal places and with or without a thousands separator. This
function can be used to apply a uniform format to a column of
numbers. number is the number to be formatted. Decimals is
the number of decimal places to be displayed. If Decimals is
negative, the number is rounded to ABS(number) Decimals
places to the left from the decimal point. No thousands
separator (optional) determines whether the thousands
separator is used or not. If the parameter is equal to 0 or
omitted, the thousands separators of the current locale setting
are displayed, else the separators are suppressed.
JIS(text) The JIS function converts half-width to full-width ASCII and
katakana characters. Returns a text string. text is the text that
contains characters to be converted. This is the complementary
function to ASC.
LEFT(text, number) Returns the number of characters from the left of a text string
text determined by number. If this parameter is omitted, one
character is returned. If number is greater than the length of the
string, the whole string is returned.
LEN(text) Returns the length of a string including spaces. text is the text
whose length is to be determined.
LOWER(text) Converts all uppercase letters in a text string to lowercase. text
is the text to be converted.
MID(text, start, number) Returns a text segment of a character string. The parameters
specify the starting position and the number of characters to
return. text is the text containing the characters from which to
extract. start is the position marking the beginning of the text to
extract. number is the number of characters from that point on
to be returned. If number is greater than LEN(text) minus start,
then the text from start to the end of text is returned.
NUMBERVALUE(text, Convert text to number, in a locale-independent way. Converts
decimal_separator, given text value text into a number. If text is a reference, it is
group_separator) first dereferenced. decimal_separator and group_separator
are optional parameters. If text contains a separator, then that
separator must be entered into the relevant optional parameter.
All parameters are entered in double quotes.
Text is transformed according to the following rules:
1) Starting from the beginning, remove all occurrences of
the group_separator before any decimal_separator.
2) Starting from the beginning, replace the first occurrence
in the text of the decimal_separator character with the
FULL STOP (U+002E) character.

Appendix B Description of Functions 429

Working with different Formulae and Functions

 Syntax Description
3) Remove all whitespace characters (5.14).
4) If the first character of the resulting string is a period FULL
STOP (U+002E) then prepend a zero.
5) If the string ends in one or more instances of PERCENT
SIGN (U+0025), remove the percent sign(s).
If percent signs were removed in step 5, divide the value of the
returned number by 100 for each percent sign removed.
PROPER(text) Capitalizes the first letter in all words of a text string. text is the
text to be converted.
REPLACE(Text, position, Replaces part of a text string with a different text string. This
length, new text) function can be used to replace both characters and numbers
(which are automatically converted to text). The result of the
function is always displayed as text. To perform further
calculations with a number which has been replaced by text,
convert it back to a number using the VALUE function. Any text
containing numbers must be enclosed in quotation marks so it is
not interpreted as a number and automatically converted to text.
Text is text, a part of which will be replaced. position is the
position within the text where the replacement will begin. length
is the number of characters in text to be replaced. new text is
the text which replaces text..
REPT(text, number) Repeats a character string by the given number of copies. text
is the text to be repeated. number is the number of repetitions.
The result can be a maximum of 255 characters.
RIGHT(text, number) Returns the right-most number of characters of a text string. If
optional number is omitted, 1 is assumed and the right-most
character is returned. If number is greater than the length of
text, the whole text is returned.
ROMAN(Number, Mode) Converts a number into a Roman numeral. The value range
must be between 0 and 3999; the modes can be integers from 0
to 4. Number is the number that is to be converted into a
Roman numeral. Mode (optional) indicates the degree of
simplification. The higher the value, the greater is the
simplification of the Roman numeral.
ROT13(Text) Encrypts a character string by moving the characters 13
positions in the alphabet. After the letter Z, the alphabet begins
again (Rotation). Entering text encrypted by this method, into
the function decrypts the text. Text is the character string to be
encrypted/decrypted.
SEARCH(find_text, text, Returns the start position of a text string within a larger string.
position) The start position for the search can be set as an option. The
search text can be a number or any sequence of characters.
The search is not case-sensitive. The search supports regular
expressions. find_text is the text to be searched for. text is
the text where the search will take place. position (optional) is
the position in the text where the search is to start.

Appendix B Description of Functions 430

Working with different Formulae and Functions

 Syntax Description
SUBSTITUTE(text, search_text, Substitutes new text for old text in a string. text is the text in
new text, occurrence) which text segments are to be exchanged. search_text is the
text segment that is to be replaced (a number of times). new
text is the text that is to replace the text segment. occurrence
(optional) indicates how many occurrences of the search text
are to be replaced. If this parameter is missing, the search text
is replaced throughout.
T(value) Returns value if text, else returns a blank text string. value is
the value to be evaluated. A reference can be used as a
parameter. If the dereferenced value is not of type text, the
result will be an empty string.
TEXT(number, Format) Converts a number into text according to a given format.
number is the numerical value to be converted. Format is the
text which defines the format and can be found on the Numbers
tab in the Format Cells dialog. Use decimal and thousands
separators according to the language set in the cell format.
TRIM(text) Returns a text string from which leading and trailing spaces
have been removed, and replaces all internal multiple spaces
with a single space. text is the text from which spaces are to be
removed.
UNICHAR(number) Returns the character represented by the given number
according to the [UNICODE] Standard. number is a decimal
integer value between 0 and 1114111.
UNICODE(text) Returns the [UNICODE] code point corresponding to the first
character of the text value. text is a string from which the code
number is returned.
UPPER(text) Converts the string specified in the text parameter to uppercase
characters.
VALUE(text) Converts a text string into a number. text is the text to be
converted to a number.

Add-in functions
Table 44: Add-in functions

Syntax Description
BESSELI(X, N) Calculates the modified Bessel function In(x). X is the value
on which the function will be calculated. N is the order of the
Bessel function.
BESSELJ(X, N) Calculates the Bessel function Jn(x) (cylinder function). X is
the value on which the function will be calculated. N is the
order of the Bessel function.
BESSELK(X, N) Calculates the modified Bessel function Kn(x). X is the
value on which the function will be calculated. N is the order
of the Bessel function.

Appendix B Description of Functions 431

Working with different Formulae and Functions

Source: - Libre Office Calc Guide: Version 4.1
https://documentation.libreoffice.org/assets/Uploads/Documentation/en/CG4.1/CG41CalcGuideLO.pdf

Working with different Formulae and Functions