For discussion: Calc formula terms

Discuss the spreadsheet application
Post Reply
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

For discussion: Calc formula terms

Post by MrProgrammer »

I've had difficulty finding good documentation about the terms one can use in an OpenOffice Calc formula. My intent is to gather this information into a future tutorial. Forum volunteers, please review this material and add your comments to this topic.

OpenOffice Calc formulas can contain the following terms:

• A numeric literal
A sequence of digits with at most one decimal separator, optionally followed by: E or e, an optional + or -, and at least one digit
If a decimal separator is used (period for English (USA) locale), the literal is locale dependent
All numeric literals are positive; one uses the negation operator to obtain negative numbers
Numeric literals can be written to be locale-independent by using exponential notation n…Ennn or n…E-nnn
Example: 314159265358979E-14

• A text literal
A ", followed by zero or more Unicode characters, followed by a "
" within the literal is represented as ""
Example: "Hello world!"

• A boolean literal (locale dependent)
TRUE and FALSE are the two boolean literals for the English (USA) locale
Example: TRUE

• An array literal
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: {"Nineteen";19|"Fifty two";52}

• A cell reference
An optional sheet reference terminated by a period, an optional $, a column name, an optional $, a row number
Below Σ represents a sheet name
Column names are A through AMJ
Row numbers are 1 through 1048576, or 1 through 65536 before OOo 3.3
Cell references are to single cells; cell ranges are created by using the : operator
Internal sheet references are an optional $ followed by a sheet name
External sheet references are 'file://Φ'#$Σ where Φ is a file name
If the file name contains a ', use '' (two apostrophes) instead of the '
If the sheet name (internal or external) contains other than letters, numbers, or underscores, write it as 'Σ'
Example: $Sheet1.A1
Example for external file name A'B.ods and sheet name C D: 'file:///Users/Guest User/A''B.ods'#$'C D'.$A$1

• 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
The name may not look like a cell reference
The name may not be R, RC, or C
The name may not begin with R followed by digits followed by C
Example: _

• A row/column label
A ', followed by Unicode characters, followed by '
' 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 labels but with additional features
Example: 'Expenses'

• Functions
A letter, optionally followed by letters, numbers, underscores, and periods, a (, zero or more parameters separated by ;, a )
Parameters can be functions or any of the terms above
Example: IF(A2=B2;"Equal";IF(A2<B2;"A<B";"A>B"))

• Calc's Operators
See [Tutorial] Order of Operations in Calc

• Parentheses
( and ) are used to modify the standard order of operations
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: For discussion: Calc formula terms

Post by RusselB »

This looks good upon a quick first review. I'd just like to add that some people might refer to ' (apostrophe) and " (double apostrophe) as single/double quotation marks.
When I was in school, I was taught that the ' was referred to an apostrophe when used to form a contraction (eg: it's for it is) or when the character was used to show ownership (eg: Russel's degree).
Otherwise the characters would be referred to a single/double quotation marks.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: For discussion: Calc formula terms

Post by Villeroy »

'Sheet name with spaces' requires apostrophes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: For discussion: Calc formula terms

Post by RoryOF »

A good habit is to replace all spaces with underscore characters.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: For discussion: Calc formula terms

Post by Villeroy »

RoryOF wrote:A good habit is to replace all spaces with underscore characters.
Nevertheless, a syntax guide should mention this special case.

Sheet names with apostrophes require a pair of apostrophes and the inner apostrophes need to be escaped with double-apostrophes as in
='John''s Sheet'.A1
or
='John''s_Sheet'.A1
or
='file:///John''s Directory/John''s File.ods'# 'John''s Sheet'.A1
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply