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 d…Ennn (this represents d… times 10ⁿⁿⁿ) or d…E-nnn (this represents d… divided by 10ⁿⁿⁿ)
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
The value of the constant does not include those two surrounding quotes
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
In the sentences below, Σ represents a sheet name
References to sheets in the same spreadsheet use an optional $ followed by a sheet name Σ
If the sheet name is preceeded by a $ it denotes a specific sheet intead of a relative sheet
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
Sheet names may not contain *, /, :, ?, [, \, or ] and may not begin or end with apostrophes
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.A1References 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 optionally 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
The name may not begin with RC followed by digits
The Assigned To value can be a cell range (absolute or relative) or a formula.
Example: Inventory Example: March.Inventory Example: _
• A row/column label range
An apostrophe, followed by Unicode characters (except apostrophe), followed by an apostrophe
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: 'My 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]Function parameters must be separated by a semicolon. If you use a comma as the separator you will receive Err:508.
• 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.