[Tutorial] Calc formula terms

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Calc formula terms

Postby MrProgrammer » Fri May 31, 2019 5:57 pm

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.

In this post the word apostrophe always means Unicode character U+0027: '
In this post the word quote always means Unicode character U+0022: "
 ‘ ↖ Those ↗ are not apostrophes: U+2018/U+2019 (Left/Right Single Quotation Mark)
↖ Those ↗ are not quotes:         U+201C/U+201D (Left/Right Double Quotation Mark)

OpenOffice Calc formulas can contain the following terms:

• A numeric constant
A sequence of digits with at most one decimal separator, optionally followed by this sequence:
         E or e, an optional + or -, and at least one digit
If a decimal separator is used — period for English (USA) locale — the constant is locale dependent
The decimal separator for a locale is normally a period or a comma
All numeric constants are positive; one uses the negation operator to obtain negative numbers
Numeric constants can be written to be locale-independent by using exponential notation n…Ennn or n…E-nnn
The following constants are written differently but all have the same value
Example: 3.14159265358979     (for locale using period for decimal separator)
Example: 3,14159265358979 (for locale using comma for decimal separator)
Example: 314159265358979E-14 (locale independent)

• A text constant
A quote, followed by zero or more Unicode characters, followed by a quote
A quote within the constant is represented as two quotes: ""
Example: "Hello world!"   (12 characters)
Example: "" (0 characters: the empty string)
Example: """" (1 character: a quote)
Example: """\""" (3 characters: quote backslash quote)

• A boolean constant (locale dependent)
Calc converts these locale dependent constants to 0 (for FALSE) and 1 (for TRUE)
Example: TRUE    [English (USA) locale]
Example: FALSE [English (USA) locale]

• An array constant
A {, followed by zero or more of the terms above separated by ; or |, followed by }
; is used to separate terms in a row
| is used to begin a new row
All rows must have the same number of columns
All columns must have the same number of rows
Example: {0;1;2;3;4;5;6;7;8;9}            (1 row, 10 columns)
Example: {0|1|2|3|4|5|6|7|8|9} (10 rows, 1 column)
Example: {"Nineteen";19|"Fifty two";52} (2 rows, 2 columns)

• A cell reference
An optional sheet reference terminated by a period, an optional $, a column name, an optional $, a row number
Column names are A through AMJ
Row numbers are 1 through 1048576, or 1 through 65536 before OpenOffice 3.3
Use of the $ is explained in 8. Using formulas and cell references in Ten concepts that every Calc user should know.
Cell references are to single cells; cell ranges are created by using the : operator
Below Σ represents a sheet name
References to sheets in the same spreadsheet use an optional $ followed by a sheet name Σ
If the sheet name contains other than letters, digits, or underscores, surround the name with apostrophes
If the sheet name begins with a digit, surround the name with apostrophes
If the sheet name contains a an apostrophe, use two apostrophes to represent it
Example: $A$1               (upper left cell)
Example: $AMJ$65536 (lower right cell, before OpenOffice 3.3)
Example: $AMJ$1048576 (lower right cell, OpenOffice 3.3 and later)
Example: $Sheet1.A1 (reference to cell on Sheet1)
Example: Sheet1.$A1 (reference to cell on previous sheet when used on Sheet2)
Example: 'Sheet One'.A$1 (sheet name containing a space)
Example: 'Dad''s Sheet'.A1 (sheet name containing an apostrophe)

References to sheets in other spreadsheets are 'file://Φ'#$Σ where Φ is a file name
If the file name contains a an apostrophe, use two apostrophes to represent it
Example: 'file:///Users/Guest User/Desktop/Data.ods'#$Sheet1.A1
Example: 'file:///Users/Guest User/Desktop/Dad''s Data.ods'#$Sheet1.A1
Example: 'file:///Users/Guest User/Desktop/Dad''s Data.ods'#$'Dad''s Sheet'.A1
Example: 'file:///C:/Users/Bozo/Desktop/Data.ods'#$Sheet1.A1

References to "cells" in CSV files always use Sheet1 for the sheet name
Example: 'file:///Users/Guest User/Desktop/Data.csv'#$Sheet1.A1

• A defined name (created with Insert → Names → Define)
A letter or underscore followed by a sequence of letters, digits, underscores, and periods
The name may not end in a period nor look like a cell reference nor be R, RC, or C
The name may not begin with R followed by digits followed by C
Example: Inventory
Example: March.Inventory
Example: _

• A row/column label range
An apostrophe, followed by Unicode characters, followed by an apostrophe
An apostrophe within the label is represented by \'
The option Calc → Calculate → Automatically find row and column labels must be enabled
Defined names offer much of the same function as row/column label ranges but with additional features
Example: 'Expenses'
Example: 'Dad\'s Expenses'

• Functions (locale dependent)
A letter, optionally followed by letters, digits, underscores, and periods, a (, zero or more parameters separated by ;, a )
Parameters can be functions or any of the terms above
Entering the function's name in the wrong language for the locale will produce a #NAME? error
Example: TODAY()                                    [English (USA) locale, current date]
Example: AUJOURDHUI() [French (France) locale, current date]
Example: HEUTE() [German (Germany) locale, current date]
Example: HOY() [Spanish (Spain) locale, current date]
Example: IF(A2=B2;"Equal";IF(A2<B2;"A<B";"A>B")) [English (USA) locale, nested IF functions]

• Calc's Operators: +, :, !, ~, -, %, ^, /, *, +, -, &, =, >, >=, <>, <, <=
See [Tutorial] Order of Operations in Calc

• Parentheses
( and ) are used to modify the standard order of operations
A parenthesized expression is treated as a single term of a formula

Warning: LibreOffice provides options to change the array constant separators and the function parameter separators, so syntax in LibreOffice formulas may differ from what is shown here.

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Posts: 3709
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Return to Calc

Who is online

Users browsing this forum: No registered users and 1 guest