This prose specification is one component of a Work Product which includes:

This document is part of the Open Document Format for Office Applications (OpenDocument) Version 1.3 specification. It defines a formula language for OpenDocument documents, which is also called OpenFormula.

OpenFormula is a specification of an open format for exchanging recalculated formulas between office applications, in particular, formulas in spreadsheet documents. OpenFormula defines data types, syntax, and semantics for recalculated formulas, including predefined functions and operations.

OpenFormula is intended to be a supporting document to the Open Document Format for Office Applications (OpenDocument) format, particularly for defining its attributes table:formula and text:formula. It can also be used in other circumstances where a simple, easy-to-read infix text notation is desired for exchanging recalculated formulas.

Note: Using OpenFormula allows document creators to change the office application they use, exchange formulas with others (who may use a different application), and access formulas far in the future, with confidence that the recalculated formulas in their documents will produce equivalent results if given equivalent inputs.

All text is normative unless otherwise labeled.

OpenFormula defines:

1. 1.data types 

2. 2.syntax 

3. 3.semantics 

The OpenDocument specification defines conformance for formula expressions and evaluators. For evaluators, there are three groups of features that an evaluator may support. This chapter defines the basic requirements for the individual conformance targets.

An OpenDocument Formula Evaluator is a program that can parse and recalculate OpenDocument formula expressions, and that meets the following additional requirements:

1. A)It may implement subsets or supersets of this specification. 

2. B)It shall conform to one of: (C16) OpenDocument Formula Small Group Evaluator, (C17) OpenDocument Formula Medium Group Evaluator or (C18) OpenDocument Formula Large Group Evaluator 

3. C)It may implement additional functions beyond those defined in this specification. It may further implement additional formula syntax, additional operations, additional optional parameters for functions, or may consider function parameters to be optional when they are required by this specification. 

4. D)Applications should clearly document their extensions in their user documentation, both online and paper, in a manner so users would be likely to be aware when they are using a non-standard extension. 

Note 1: An expression may reference a function not defined by this specification by name, or depend on implementation-defined behavior, or on semantics not guaranteed by this specification. Reference to or dependence upon functions or behavior not defined by this specification may impair the interoperability of the resulting expression(s).

An OpenDocument Formula Small Group Evaluator is an OpenDocument Formula Evaluator that meets the following additional requirements:

An OpenDocument Formula Medium Group Evaluator is an OpenDocument Small Group Formula Evaluator that meets the following additional requirements:

An OpenDocument Formula Large Group Evaluator is an OpenDocument Medium Group Formula Evaluator that meets the following additional requirements:

Applications should document all implementation-defined variances from this specification in a manner that enables application users to obtain the information.

In a few cases a specific approach is required (e.g., string indexes begin at one), which may vary in the user interfaces of different implementations.

Note 1: In practice, for nearly all documents the differences are irrelevant. The primary variances and differences from OpenFormula and some existing applications are:

This section describes the basic formula processing model: how expressions are calculated, when recalculation occurs, and limits on formulas.

OpenFormula defines rules for the evaluation of expressions as well as the functions and operators that appear in expressions.

Expressions in OpenFormula shall be evaluated by application of the following rules:

Operators and functions in OpenFormula shall be evaluated according to their definitions by applying the following rules:

1. 1.The value of all expression arguments are computed. Exceptions to computation of all arguments are noted in a function's specification. 

   Note: The practice of computing all argument expressions is known as "eager" evaluation. The IF function is an example of a function that does not require computation of all arguments.  

2. 2.If an argument expression evaluates to Error, calculation of the operator or function may short-circuit and return the Error if the function does not suppress error propagation as noted in the function's specification.  

3. 3.If an operator or function is passed a value of incorrect type, call the appropriate implicit conversion function to convert the value to the correct type. If conversion is not possible, generate an Error.  

4. 4.The function or operation is called with its argument expressions' results, and the result of the function or operation is the evaluation of the expression.  

Non-scalar values passed as arguments to functions are evaluated by intersection or iteration.

1. 1)Evaluation as an implicit intersection of the argument with the expression's evaluation position. 

   1. 1.1)Inline Arrays
      Element (0;0) of the array is used in place of the array. 

      Note 1:
      =ABS({-3;-4})         => ABS(-3)        // row vector
      =ABS({-3|-4})        => ABS(-3)        // column vector
      =ABS({-3;-4|-6;-8})        => ABS(-3)        // matrix
      ={1;2;3|4;5;6}        => 1                // simple display 

   2. 1.2)References 

      1. 1.2.1)If the target reference is a row-vector (Nx1), use the value at the intersection of the evaluation position's column and the reference's row. 

         Note 2:
         in cell B2 : =ABS(A1:C1) => ABS(B1)
         If there is no intersection the result is #N/A or a more specific Error value. 

         Note 3: in cell D4 : =ABS(A1:C1) => #N/A or a more specific Error value. 

      2. 1.2.2)If the target reference is a column-vector (1xM), the value at the intersection of the evaluation position's row and the reference's column. 

         Note 4:
         in cell B2 : =ABS(A1:A3) => ABS(A2)
         in cell D4 : =ABS(A1:A3) => #N/A or a more specific Error value. 

2. 2)Matrix evaluation. 

   If an expression is being evaluated in a cell flagged as a being part of a 'Matrix' (OpenDocument Part 3, 19.683 table:number-matrix-columns-spanned): 

   1. 2.1)The portion of a non-scalar result to be displayed need not be co-extensive with a specified display area. The portion of the non-scalar result to be displayed is determined by: 

      1. 2.1.1)If the position to be displayed exists in the result, display that position. 

      2. 2.1.2)If the non-scalar result is 1 column wide, subsequent columns in the display area display the value in the first column. This applies to
         - scalars '3'
         - singletons '{3}'
         - column vectors '{1|2|3}' 

      3. 2.1.3)If the non-scalar result is 1 row high, subsequent rows in the display area use the value of the first row. This applies to
         - scalars '3'
         - singletons '{3}'
         - row vectors '{1;2;3}' 

      4. 2.1.4)If none of the other rules apply #N/A 

         Note 5:
         in matrix A1:B3 with ={1;2|3;4|5;6}        : cell B2 contains 4.        [Rule 2.1.1]
         in matrix A1:B3 with ={1|3|5}                : cell B2 contains 3.        [Rule 2.1.1 for row, and Rule 2.1.2 column]
         in matrix A1:B3 with ={2;4}                : cell B2 contains 4.        [Rule 2.1.3 for row, and Rule 2.1.1 column]
         in matrix A1:C4 with ={1;2|3;4|5;6}        : cell C1,A4 contain #N/A. [Rule 2.1.4] 

         Note 6: if a value is not requested it is not displayed
         in matrix A1:B2 with ={1;2|3;4|5;6}        : the value '6' is not displayed because B3 is not part of the display matrix. 

   2. 2.2)Calculations with non-scalar inputs are a generalization of (2.1). 

      
      When evaluating a formula in 'matrix' mode, and a non-scalar value is passed to a function argument that expects a scalar, the function is evaluated multiple times, iterating over the non-scalar input(s) and putting the function result into a matrix at the position corresponding to the input. Unary/Binary operators, other than range and union, follow the rules for scalar functions when passed non-scalar values. 

      Inline arrays and references are interchangeable. 

      1. 2.2.1)Functions returning arrays are not eligible for implicit iteration. When evaluated in 'matrix' mode the {0;0}th element is used. 

         Note 7:
         e.g. =SUM(INDIRECT({"A1";"A2")) would produce the value in A1 when evaluated in array mode. 

      2. 2.2.2)The result matrix is rectangular, sized with the maximum number of rows and columns from all non-scalar arguments. 

         Note 8:
         ={1;2}+{3;4;5}        => {4;6;#N/A}
         ={1}+{1;2}        => {2;3} 

      3. 2.2.3)The result matrix is populated by extracting the corresponding value from each of the non-scalar arguments based on the following rules, and evaluating the function with that set of arguments. 

         1. 2.2.3.1)If the argument data is a singleton array or a scalar the value is repeated for each evaluation. 

            Note 9:
            = 1 + {1;2;3|4;5;6}        => {2;3;4|5;6;7}
            = {1} + {1;2;3|4;5;6}        => {2;3;4|5;6;7} 

         2. 2.2.3.2)If the argument data is 1 column wide the value in the corresponding row is used to evaluate all columns in the result matrix. 

            Note 10:
            = {1|2} + {10;20|30;40}        => {11;21|32;42} 

         3. 2.2.3.3)If the argument data is 1 row height the value in the corresponding column is used to evaluate all rows in the result matrix. 

            Note 11:
            = {1;2} + {10;20|30;40}        => {11;22|31;42} 

         4. 2.2.3.4)If one argument data is 1 column wide and another argument data is 1 row height the value of the corresponding row respectively column is used to evaluate all elements in the result matrix. 

            Note 12:
            ={1;2} + {10|20}                => {11;12|21;22} 

         5. 2.2.3.5)If an argument is a 2d matrix the argument value in the position corresponding to the current output position is used if it is within range of the supplied argument, otherwise #N/A is used in the calculation. 

            Note 13:
            =MID("abcd";{1;2};{1;2;3})        => {"a";"bc";#N/A} 

A Formula Evaluator operates in an execution environment (a "host"). The behavior of the Formula Evaluator is parametrized by host-defined properties and functions.

The following properties are host-defined:

1. 1.HOST-CASE-SENSITIVE: if true, text comparisons are case-sensitive. This influences the operators =, <>, <, <=, >, and >=, as well as database query functions that use them. Note that the EXACT function is always case-sensitive, regardless of this calculation setting. 

2. 2.HOST-PRECISION-AS-SHOWN: If true, calculations are performed using rounded values of those displayed; otherwise, calculations are performed using the precision of the underlying numeric representation. 

   Note: This does not impose a particular numeric model. Since implementations may use binary representations, this rounding may be inexact for decimal values. 

3. 3.HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL If true, the specified search criteria shall apply to the entire cell contents if it is a text match using = or <>; if not, only a subpart of the cell content needs to match the text. 

4. 4.HOST-AUTOMATIC-FIND-LABELS: if true, row and column labels are automatically found. 

5. 5.HOST-USE-REGULAR-EXPRESSIONS: If true, regular expressions are used for character string comparisons and when searching. 

6. 6.HOST-USE-WILDCARDS: If true, wildcards question mark '?' and asterisk '*' are used for character-string comparisons and when searching. Wildcards may be escaped with a tilde '~' character. 

7. 7.HOST-NULL-YEAR: This defines how to convert a two-digit year into a four-digit year. Each two-digit year value is interpreted as a year that equals or follows this year. 

8. 8.HOST-NULL-DATE: Defines the beginning of the epoch; a numeric date of 0 equals this date. 

9. 9.HOST-LOCALE: The locale to be used for locale-dependent operations, such as conversion of text to dates, or text to numbers. 

10. 10. HOST-ITERATION-STATUS: If enabled, iterative calculations of cyclic references are performed.  

11. 11. HOST-ITERATION-MAXIMUM-DIFFERENCE: If iterative calculations of cyclic references are enabled, the maximum absolute difference between calculation steps that all involved formula cells must yield for the iteration to end and yield a result.  

12. 12. HOST-ITERATION-STEPS: If iterative calculations of cyclic references are enabled, the maximum number of steps iterations that are performed if the results are not within HOST-ITERATION-MAXIMUM-DIFFERENCE. 

Implementations of OpenFormula typically recalculate formulas when its information is needed. Typical implementations will note what values a formula depends on, and when those dependent values are changed and the formula's results are displayed, it will re-execute the formulas that depend on them to produce the new results (choosing the formulas in the right order based on their dependencies). Implementations may recalculate when a value changes (this is termed automatic recalculation) or on user command (this is termed manual recalculation).

This specification does not, by itself, specify a numerical implementation model, though it does imply some minimal levels of accuracy for most functions. For example, an application cannot say that it implements the infix operator “/” as specified in this document if it implements integer-only arithmetic.

Evaluators which claim to support “basic limits” shall support at least the following limits:

1. 1.formulas up to at least 1024 characters long, as measured when in OpenDocument interchange format not counting the square brackets around cell addresses, or the “.” in a cell address when the sheet name is omitted. 

2. 2.at least 30 parameters per function when the function prototype permits a list of parameters. 

3. 3.permit strings of ASCII characters of up to 32,767 (2^15-1) characters. 

4. 4.support at least 7 nesting levels of functions. 

A number is a numeric value.

Numbers shall be able to represent fractional values (they shall not be limited to only integers). Evaluators may implement Number with a fixed or with a variable bit length. A cell with a constant numeric value has the Number type.

Implementations typically support many subtypes of Number, including Date, Time, DateTime, Percentage, fixed-point arithmetic, and arithmetic supporting arbitrarily long integers, and determine the display format from this.

Note: This specification does not require that specific subtypes be distinguishable from each other, or that the subtype be tracked, but in practice most implementations do such tracking because requiring users to manually format every cell appropriately becomes tedious very quickly. Automatically determining the most likely subtype is especially important for a good user interface when generating OpenDocument format, since some subtypes (such as date, time, and currency) are stored in a different manner depending on their subtype. Thus, this specification identifies some common subtypes and identifies those subtypes where relevant in some function definitions, as an aid to implementing good user interfaces. Many applications vary in the subtype produced when combining subtypes (e.g., what is the result when percentages are multiplied together), so unless otherwise noted these are unspecified. Typical implementations try to heuristically determine the "right" format for a cell when a formula is first created, based on the operations in the formula. Users can then override this format, so as a result the heuristics are not important for data exchange (and thus outside the scope of this specification).

Time is a subtype of Number.

Time is represented as a fraction of a day.

Date is a subtype of Number.

Date is represented by an integer value.

A serial date is the expression of a date as the number of days elapsed from a start date called the epoch.

Note 1: Using expressions that assume serial numbers are based on a particular epoch may cause interoperability issues.

Evaluators shall support positive serial numbers. Evaluators may support negative serial numbers to represent dates before an epoch.

Note 2: It is implementation-defined if the year 1900 is treated as a leap year.

Note 3: Evaluators that treat 1900 as a non-leap year can use the epoch date 1899-12-30 to compensate for serial numbers that originate from evaluators that treat 1900 as a leap year and use 1899-12-31 as an epoch date.

DateTime is a subtype of Number. It is a Date plus Time.

A percentage is a subtype of Number that may be displayed by multiplying the number by 100 and adding the sign “%” or with other formatting depending upon the number format assigned to the cell where it appears.

A currency is a subtype of Number that may appear with or without a currency symbol or with other formatting depending upon the number format assigned to the cell where it appears.

An empty cell is neither zero nor the empty string, and an empty cell can be distinguished from cells containing values (including zero and the empty string). An empty cell is not the same as an Error, in particular, it is distinguishable from the Error #N/A (not available).

A cell position is the location of a single cell at the intersection of a column and a row.

A cell strip consists of cell positions in the same row and in one or more contiguous columns.

A cell rectangle consists of cell positions in the same cell strips of one or more contiguous rows.

A cell cuboid consists of cell positions in the same cell rectangles of one or more contiguous sheets.

A reference is the smallest cuboid that (1) contains specifically-identified cell positions and/or specifically-identified complete columns/rows such that (2) removal of any cell positions either violates condition (1) or does not leave a cuboid.

A reference list contains 1 or more references, in order. A reference list can be passed as an argument to functions where passing one reference results in an identical computation as an arbitrary sequence of single references occupying the identical cell range.

Note 1: For example, SUM([.A1:.B2]) is identical to SUM([.A1]~[.B2]~[.A2]~[.B1]), but COLUMNS([.A1:.B2]), resulting in 2 columns, is not identical to COLUMNS([.A1]~[.B2]~[.A2]~[.B1]), where iterating over the reference list would result in 4 columns.

A reference list cannot be converted to an array.

Note 2: For example, in array context {ABS([.A1]~[.B2]~[.A2]~[.B1])} is an invalid expression, whereas {ABS([.A1:.B2])} is not.

Passing a reference list where a function does not expect one shall generate an Error. Passing a reference list in array iteration context to a function expecting a scalar value shall generate an Error.

An array is a set of rows each with the same number of columns that contain one or more values. There is a maximum of one value per intersection of row and column. The intersection of a row and column may contain no value.

Many functions require a type or a set of types with special properties, and/or process them specially. For example, a "Database" requires headers that are the field names. These specialized types are called pseudotypes.

A Scalar value is a value that has a single value. A reference to more than one cell is not a scalar (by itself), and is converted to one as described below. An array with more than one element is not a scalar. The types Number (including a complex number), Logical, and Text are scalars.

An integer is a subtype of Number that has no fractional value. An integer X is equal to INT(X). Division of one integer by another integer may produce a non-integer.

TextOrNumber is a value that is either a Number or Text.

1. 1.truncate(date1), truncate(date2) 

2. 2.If date1==date2 return 0 

3. 3.If date1> date2, then swap the values of date1 and date2. 

4. 4.If day(date1)==31 then subtract 1 day from date1 

5. 5.If day(date1)==30 and day(date2)==31 then subtract 1 day from date2 

6. 6.If both date1 and date2 are the last day of February, change date2 to the 30th of the month. 

7. 7.If date1 is the last day of February, change it to the 30th of the month. 

8. 8.Return (year(date2)*360 + month(date2)*30 + day(date2)) - (year(date1)*360 + month(date1)*30 + day(date1)). 

1. 1.truncate(date1), truncate(date2) 

2. 2.If date1> date2, then swap the values of date1 and date2. 

3. 3.Return the actual numbers of days between date1 and date2, inclusive of date1, but not inclusive of date2. 

1. 1.truncate(date1), truncate(date2) 

2. 2.If date1==date2 return 0 

3. 3.If date1> date2, then swap the values of date1 and date2. 

4. 4.If day(date1)==31 then subtract 1 from date1 

5. 5.If day(date2)==31 then subtract 1 from date 2 

6. 6.Return (year(date2)*360 + month(date2)*30 + day(date2)) - (year(date1)*360 + month(date1)*30 + day(date1)). 

1. 1.Return 360 

1. 1.Evaluate A: year(date1) != year(date2) 

2. 2.Evaluate B: year(date2)!=year(date1)+1  

3. 3.Evaluate C: month(date1) < month(date2) 

4. 4.Evaluate D: month(date1) == month(date2)  

5. 5.Evaluate E: day(date1) < day(date2) 

6. 6.Evaluate F: (A and B) or (A and C) or (A and D and E) 

7. 7.If F is true then return the average of the number of days in each year between date1 and date2, inclusive.  

8. 8.Otherwise, if A and is-leap-year(year(date1)) then return 366 

9. 9.Otherwise, if a February 29 occurs between date1 and date2 then return 366 

10. 10. Otherwise, if date2 is a February 29, then return 366 

11. 11. Otherwise return 365 

1. 1.Return 365 

A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents.

A reference to an empty cell is interpreted as the numeric value 0.

A matching expression can be:

A database is a rectangular organized set of data. Any database has a set of one or more fields that determine the structure of the database. A database has a set of zero or more records with data, and each record contains data for every field (though that field may be empty).

Evaluators shall support the use of ranges as databases if they support any database functions. The first row of a range is interpreted as a set of field names.

Note: Field names of type Text and unique without regard to case enhance the interoperability of data. It is also a common expectation that rows following the first row of data are data records that correspond to field names in the first row.

A single cell containing text can be used as a database; if it is, it is a database with a single field and no data records.

A field is a value that selects a field in a database; it is also called a field selector. If the field selector is Text, it selects the field in the database with the same name.

Evaluators should match the database field name case-insensitively.

If a field selector is a Number, it is a positive integer and used to select the fields. Fields are numbered from left to right beginning with the number 1.

All functions that accept a field parameter shall, when evaluated, return an Error if the selected field does not exist.

Some functions accept a sequence, i.e., a value that is to be treated as a sequential series of values. The following are sequences: NumberSequence, NumberSequenceList, DateSequence, LogicalSequence, and ComplexSequence.

Any represents a value of any type defined in this specification, including Error values.

Formulas may start with a '=' (EQUALS SIGN, U+003D), which if present may be followed by a “forced recalculate” marker '=' (EQUALS SIGN, U+003D), followed by an expression. If the second '=' (EQUALS SIGN, U+003D) is present, this formula is a "forced recalculation" formula. If a formula is marked as a "forced recalculation" formula, then it should be recalculated whenever one of its predecessors it depends on changes.

Expressed as a grammar in BNF notation, a formula is specified:

Formula ::= Intro? Expression

Intro ::= '=' ForceRecalc?

ForceRecalc ::= '='

The primary component of a formula is an Expression. Formulas are composed of Expressions, which may in turn be composed from other Expressions.

Expression ::=

 Whitespace* (

 Number |

 String |

 Array |

 PrefixOp Expression |

  Expression PostfixOp |

  Expression InfixOp Expression |

  '(' Expression ')' |

  FunctionName '(' ParameterList ')' |

 Reference |

 QuotedLabel |

 AutomaticIntersection |

 NamedExpression |

  Error

 ) Whitespace*

SingleQuoted ::= "'" ([^'] | "''")+ "'"

Constant numbers are written using '.' (FULL STOP, U+002E) dot as the decimal separator. Optional "E" or "e" denotes scientific notation. Syntactically, negative numbers are positive numbers with a prefix "-" (HYPHEN-MINUS, U+002D) operator. A constant number is of type Number.

Number ::= StandardNumber |

   '.' [0-9]+ ([eE] [-+]? [0-9]+)?

StandardNumber ::= [0-9]+ ('.' [0-9]+)? ([eE] [-+]? [0-9]+)?

Evaluators should be able to read the Number format, which accepts a decimal fraction that starts with decimal point '.' (FULL STOP, U+002E), without a leading zero. Evaluators shall write numbers only using the StandardNumber format, which requires a leading digit, and shall not write numbers with a leading '.' (FULL STOP, U+002E).

Constant strings are surrounded by double-quote characters (QUOTATION MARK, U+0022); a literal double-quote character '"' (QUOTATION MARK, U+0022) as string content is escaped by duplicating it.  A constant string is of type Text.

String ::= '"' ([^"#x00] | '""')* '"'

Operators are functions with one or more parameters.

PrefixOp ::= '+' | '-'

PostfixOp ::= '%'

InfixOp ::= ArithmeticOp | ComparisonOp | StringOp | ReferenceOp

ArithmeticOp ::= '+' | '-' | '*' | '/' | '^'

ComparisonOp ::= '=' | '<>' | '<' | '>' | '<=' | '>='

StringOp ::= '&'

There are three predefined reference operators: reference intersection, reference concatenation, and range. The result of these operators may be a 3-dimensional range, with front-upper-left and back-lower-right corners, or even a list of such ranges in the case of cell concatenation.

ReferenceOp ::= IntersectionOp | ReferenceConcatenationOp | RangeOp

IntersectionOp ::= '!'

ReferenceConcatenationOp ::= '~'

RangeOp ::= ':'

Note 1: Prefix “-” has a higher precedence than “^”, “^” is left-associative, and reference intersection has a higher precedence than reference union.

Note 2: Prefix “+” and “-“ are defined to be right-associative. However, note that typical applications which implement at most the operators defined in this specification (as specified) may implement them as left-associative, because the calculated results will be identical.

Note 3: Precedence can be overridden by using parentheses, so "=2+3*4" computes to 14 but "=(2+3)*4" computes 20. Implementations should retain "unnecessary" parentheses and white space, since these are added by people to improve readability.

Functions are called by name, followed by parentheses surrounding a list of parameters. Parameters are separated using the semicolon ';' (SEMICOLON, U+003B) character:

FunctionName ::=  LetterXML (LetterXML | DigitXML |

  '_' | '.' | CombiningCharXML)*

When writing a document using function(s) not defined in this specification, an evaluator shall include a prefix in such function names to identify the original definer of the function's semantics. When the origin of a function cannot be determined, producers may omit a prefix. Producers may use the prefix to differentiate between different definition types. Evaluators that do not support a function should compute its result as some Error value other than #N/A.

Note: Examples of implementation-defined functions include extension functions included with an implementation, user-defined functions written by users, and 3rd party functions distributed in libraries.

Note: Examples of such names include COM.MICROSOFT.CUBEMEMBER, ORG.OPENOFFICE.STYLE, ORG.GNUMERIC.RANDRAYLEIGH, and COM.LOTUS.V98.FOO.

Evaluators should avoid defining evaluator-unique functions beginning with a top-level domain name followed by a period. Evaluators should avoid defining application functions beginning with “NET.”, “COM.”, “ORG.”, or a country code followed by a period.

Evaluators that do not support a function should compute its result as some Error value other than #N/A.

References refer to a specific cell or set of cells. The syntax for a constant reference:

Reference ::= '[' (Source? RangeAddress) | ReferenceError ']'

RangeAddress ::=
 SheetLocatorOrEmpty '.' Column Row (':' '.' Column Row )? |
 SheetLocatorOrEmpty '.' Column ':' '.' Column |
 SheetLocatorOrEmpty '.' Row ':' '.' Row |
 SheetLocator '.' Column Row ':' SheetLocator '.' Column Row |
 SheetLocator '.' Column ':' SheetLocator '.' Column |
 SheetLocator '.' Row ':' SheetLocator '.' Row

SheetLocatorOrEmpty ::= SheetLocator | /* empty */

SheetLocator ::= SheetName ('.' SubtableCell)*

SheetName ::= QuotedSheetName | '$'? [^\]\. #$']+

QuotedSheetName ::= '$'? SingleQuoted

SubtableCell ::= ( Column Row ) | QuotedSheetName

ReferenceError ::= "#REF!"

Column ::= '$'? [A-Z]+

Row ::= '$'? [1-9] [0-9]*

Source ::= "'" IRI "'" "#"

CellAddress ::= SheetLocatorOrEmpty '.' Column Row /* Not used directly */

References always begin with '[' (LEFT SQUARE BRACKET, U+005B); this disambiguates cell addresses from function names and named expressions. SheetNames include single-quote“'” (APOSTROPHE, U+0027) characters by doubling them and having the entire name surrounded by single-quotes. Column labels shall be in uppercase. The syntax supports whole-row and whole-column references. A reference is of type Reference.

A ReferenceError provides information that a formula evaluates to an Error because of a particular reference having been invalidated by actions that occurred after the formula was validly created.

Columns are named by a sequence of one or more uppercase letters A-Z (U+0041 through U+005A).  Columns are named A, B, C, ... X, Y, Z, AA, AB, AC, ... AY, AZ, BA, BB, BC, ... ZX, ZY, ZZ, AAA, AAB, AAC, AAZ, ABA, ABB, and so on.

A quoted label is Text contained in a table as cell content, either literally or as a formula result.

QuotedLabel ::= SingleQuoted

A quoted label identifies a column or a row, depending on the label range in which its text appears.

For a QuotedLabel, first the cells defined in column label ranges (cell ranges of the table:label-cell-range-address attribute (OpenDocument Part 3, 19.660) in the elements <table:label-range> (OpenDocument Part 3, 9.4.9) with attribute table:orientation (OpenDocument Part 3, 19.690.4) set to column) are searched for the string content of QuotedLabel (without the quotes). If found, the corresponding column's range of the data cell range of the table:data-cell-range-address attribute (OpenDocument Part 3, 19.612) is taken as a reference. If not found, the cells defined in row label ranges (attribute table:orientation set to row) are searched and if found the corresponding row's range of the data cell range is taken. Label ranges of the current formula's sheet take precedence over label ranges of other sheets if a name occurs in both.

For a QuotedLabel where no defined label is found, an automatic lookup is performed on the sheet where the formula cell resides, if that document setting is enabled (HOST-AUTOMATIC-FIND-LABELS value true ).

Matches to the upper left of the formula cell are preferred over other matches, followed by matches with a smaller distance. The following algorithm is used:

Cells on the same sheet as the formula cell are examined column-wise from left to right whether they contain the text of QuotedLabel (without the quotes). If more than one cell match, the distance and direction from the formula cell's position is taken into account. The distance is calculated by Distance= ColumnDifference*ColumnDifference+ RowDifference*Row Difference using an idealized layout of square cells. For the direction, during the run two independent match positions are remembered each time Distance is smaller than a previous Distance: Match2 for positions right of and/or below the formula position (FormulaColumn < MatchColumn || FormulaRow < MatchRow), Match1 for all others (not right of and not below). Match1 also holds the very first match, in case there is only one match or all matches are somewhere below or right of the formula cell. After having found the smallest distances the conditions are:

1. 1.If Match1 has the smallest distance, that match is taken. 

2. 2.Else, Match2 (right and/or below) has the smallest or an equal distance: 

   1. 2.1 A match to the upper left (FormulaColumn >= Match1Column && FormulaRow >= Match1Row) takes precedence over matches to other directions. 

   2. 2.2 Else, if there is no match to the upper left: 

      1. 2.2.1 If Match1 is somewhere right of the formula cell (FormulaColumn < Match1Column) it was the first match found in column-wise lookup. 

         1. 2.2.1.1 If Match2 is above the formula cell (FormulaRow >= Match2Row) it is to the upper right of the formula cell and either nearer than Match1 or Match1 is below. Match2 is taken. 

         2. 2.2.1.2 Else Match2 is below the formula cell and Match1 is taken. 

      2. 2.2.2 Else (Match1 not right of the formula cell => two matches below or below and right) the match with the smallest distance is taken. 

If the resulting cell is below or above another cell containing Text a row label is assumed, else a column label is assumed.

Note: Use of automatically looked up column or row labels in expressions impairs interoperability.

For the reference resulting from a single QuotedLabel an implicit intersection is generated if the operator or function with which it is used expects a scalar value. The intersection is generated with the current formula's cell position, thus for a column label an intersection is generated with the formula cell's row, for a row label with the formula cell's column.

When passed as a non-scalar argument (e.g. Array or NumberSequence) to a function, an automatically-looked-up column or row label (not defined label range) is converted to an automatic range reference that is adjusted each time the formula is interpreted. The range is generated from the column below a column label, or the row to the right of a row label, constructed by encompassing contiguous non-empty cells. An empty cell interrupts contiguousness, one empty cell directly below a column label cell or to the right of a row label cell is ignored.

Example:

If any cell content is entered in row 5 the range is regenerated as follows:

An automatic intersection may be used to identify the intersection of two quoted labels. Note that this is different from the IntersectionOp, which takes two references instead of two labels:

AutomaticIntersection ::= QuotedLabel Whitespace* '!!' Whitespace* QuotedLabel

In an automatic intersection, one of the labels identifies a row, the other a column; they may be in either order. Each QuotedLabel is looked up as defined above under "Lookup of Defined Labels" and "Automatic Lookup of Labels". If two data cell ranges are found, the intersection of the column's data cell range and the row's data cell range is generated. If the intersection result is not exactly one cell, an Error is generated.

A NamedExpression references another expression, possibly in a completely different spreadsheet or any other type of document that can be imported into a spreadsheet.

NamedExpression ::= SimpleNamedExpression |
 SheetLocalNamedExpression | ExternalNamedExpression

SimpleNamedExpression ::= Identifier |
 '$$' (Identifier | SingleQuoted)

SheetLocalNamedExpression ::=
 QuotedSheetName '.' SimpleNamedExpression

ExternalNamedExpression ::=
 Source (SimpleNamedExpression | SheetLocalNamedExpression)

Evaluators supporting named expressions shall support Simple Named Expressions that are global to all the sheets in a (spreadsheet) document in the current document. This is a named expression without a Source, QuotedSheetName, or SubtableCell. The type of a named expression is the type of the value that the named expression returns.

Named expressions are case-consistent, meaning that matching is done case-insensitive and identifiers can not differ ONLY in their case. Evaluators should write identifiers with identical case in all locations.

Evaluators may support Sheet-local Named Expressions that are local (attached) to individual sheets. In that case, a non-empty QuotedSheetName can be used to reference a sheet-specific named expression. The most specific named expression for a given expression is used. If the QuotedSheetName is empty, the search for the named expression begins with the current sheet, then up through the container(s) of the sheet (the same is true if the QuotedSheetName rule fragment is not included at all). If there is a non-empty QuotedSheetName, search begins with that named sheet, then up through its container(s) for the given name.

Note: There is no syntax for referencing a named expression without first looking at the current sheet's named expressions; where this is a problem, a user can define a blank sheet and reference that sheet as the starting location for finding the named expression.

If a sheetname is not empty, it shall be quoted using the single-quote character “'” (APOSTROPHE, U+0027). While both Source and QuotedSheetName can begin with the single-quote character, they are distinguished: after the closing single-quote character, a non-empty source shall have the '#' (NUMBER SIGN, U+0023) character as the next non-whitespace character; a non-empty sheetname shall be followed by the '.' (FULL STOP, U+002E) character as the next non-whitespace character.

Evaluators shall support the Error named #N/A. Evaluators may support other Errors. Evaluators may allow entry of errors directly, parse them and recognize them as Errors. Functions shall propagate Errors unless stated otherwise.

Error names shall have the following syntax:

Error ::= '#' [A-Z0-9]+ ([!?] | ('/' ([A-Z] | ([0-9] [!?]))))

Specific Errors are indicated by their corresponding names.

Evaluators may implement other Errors.

An unknown Error name shall be mapped into an Error supported by the evaluator when read (e.g., the application's equivalent of #NAME?), though an evaluator may warn the user if this has or will take place. It is desirable to preserve the original specific Error name when writing an Error back out, where possible, but for Errors other than #N/A evaluators may write a different Error for a formula than they did when reading it. Whitespace shall not be included in an Error name.

Evaluators should use a human-comprehensible name, not a numeric id, for Error names they write.

Inline arrays are enclosed with curly braces. Inside, they contain one or more rows, with each row separated by a row separator:

Array ::= '{' MatrixRow ( RowSeparator MatrixRow )* '}'

MatrixRow ::= Expression ( ';' Expression )*

RowSeparator ::= '|'

Evaluators that support inline arrays shall accept a matrix with one or more rows, each with one or more columns, with the same number of columns in each row, with constant values for each expression. Evaluators that do not support inline arrays, or cannot support a particular use permitted by this syntax, should compute an Error value for such arrays. An inline array is of type Array.

Note: Expression authors should be aware that use of Expression other than constant Number or constant String may impair interoperability.

Whitespace ::= #x20 | #x09 | #x0a | #x0d

For calculation purposes, whitespace is ignored unless it is inside the contents of string constants or text surrounded by single quotes. Evaluators shall ignore any whitespace characters before and/or after any operators, constant numbers, constant strings, constant errors, inline arrays, parentheses used for controlling precedence, and the closing parenthesis of a function call. Whitespace shall be ignored following the initial equal sign(s). Whitespace shall be ignored just before a function name, but whitespace shall not separate a function name from its initial opening parenthesis. Whitespace shall not be used in the interior of a terminating grammar rule (a rule that references no other rule other than character sets, internally or externally-defined), unless specifically permitted by the terminating grammar rule, since these rules define the lexical properties of a component. Evaluators shall not write formulas with whitespace embedded in any unquoted identifier, constant Number, or constant Error.  Evaluators shall treat SPACE (U+0020), CHARACTER TABULATION (U+0009), LINE FEED (U+000A), and CARRIAGE RETURN (U+000D) as whitespace characters.

An embedded line break shall be represented by a single LINE FEED character (U+000A), not by a carriage return-linefeed pair. When embedded in an XML attribute the linefeed character is represented as “&#x0A;”.

Evaluators should retain whitespace entered by the original formula creator and use it when saving or presenting the formula, and should not add additional whitespace unless directed to do so during the process of editing a formula.

OpenFormula defines commonly used operators and functions.

Function names ignore case. Evaluators should write function names in all uppercase letters when writing OpenFormula formulas.

Unless otherwise noted, if any value being provided is an Error, the result is an Error; if more than one Error is provided, one of them is returned (evaluators should return the leftmost Error result).

For every function or operator, the following are defined in this specification:

Any given function or operand takes 0 or more parameters, and each of those parameters has an expected type. The expected type can be one of the base types, identified above. It can also be of some conversion type that controls conversion, e.g., Any means that no conversion is done (it can be of any type); NumberSequence causes a conversion to an ordered sequence of zero or more numbers. If the passed-in type does not match the expected type, an attempt is made to automatically convert the value to the expected type. An Error is returned if the type cannot be converted (this can never happen if the expected type is Any). Unless otherwise noted, any conversion operation applied to a value of type Error returns the same value.

To convert to a scalar, if the value is of type:

In some cases a reference to a single cell is needed, but a reference to multiple cells is provided. In this case an "implied intersection" is performed. To perform an implied intersection:

• •Compute the union of cells contained in the current row and current column of the formula being computed. 

• •Intersect this with the provided reference to multiple cells 

• •If a single cell is referenced; return it; otherwise, return an Error. 

A ForceArray attribute forces calculation of the argument's expression into non-scalar array mode. This means that no implied intersection is performed, instead where a reference to a single cell is expected and multiple cells are provided, iteration over the multiple cells is performed and results are stored in an array that is passed on.

If the expected type is Number, then if the value is of type:

If the expected type is NumberSequence, then if value is of type:

An evaluator may accept complex numbers as Text, Number, or a different distinguishable type.

If the value is:

If the expected type is ComplexSequence, then if value is of type:

• •Number, Text, or Logical, handle as Conversion to Complex Number (creating a sequence of length 1). 

• •Reference, create a sequence of complex numbers from the values of the referenced cells that only includes the values of type Number, Text, and Error. Empty cells are not included in a complex number sequence. If the Logical type is a distinguished type from the Number type, it should not be included in the sequence of numbers; if the Logical type is not a distinguished type, then such values will (of course) be included in the number sequence. 

If the expected type is Logical, then if value is of type:

• •Number, return TRUE for nonzero and FALSE for 0. 

• •Text: The specific conversion is implementation-defined; an evaluator may return FALSE, an Error value, or the results of its attempt to convert the Text value (ignoring case) to a Logical value (and fall back to FALSE or Error if it fails to do so). Conversion depends on the actual locale the evaluator runs in. 

• •Logical, return it. 

• •Reference, convert to scalar and then perform as above.  If the reference is to an empty cell, consider it FALSE. 

If the expected type is LogicalSequence, then if value is of type:

• •Number or Logical, handle as Conversion to Logical (creating a sequence of length 1). 

• •Reference, create a sequence of logical values from the values of the referenced cells that only includes the values of type Logical and Error. If the Logical type is not a distinguished type, then include values of type Number, converting each to a Logical value as described in Conversion to Logical. Empty cells are not included in a LogicalSequence. 

If the expected type is Text, then if value is of type:

• •Number, transform into Text (with no whitespace). 

• •Text, return it. 

• •Logical, return "TRUE" if it is true and "FALSE" if it is false. 

• •Reference: perform conversion to scalar. If the referenced cell is empty, treat as an empty string (a text value with length 0). Then perform as above. 

If the expected type is the pseudotype DateParam, then if value is of type:

If the expected type is the pseudotype TimeParam, then if value is of type:

The functions defined under standard operators differ from other functions only by their frequency of use. That frequency of use has lead to the colloquial terminology, standard operators.

Summary: Add two numbers.

Syntax: Number Left + Number Right

Returns: Number

Constraints: None

Semantics: Adds numbers together.

Summary: Subtract the second number from the first.

Syntax: Number Left - Number Right

Returns: Number

Constraints: None

Semantics: Subtracts one number from another number.

Summary: Multiply two numbers.

Syntax: Number Left * Number Right

Returns: Number

Constraints: None

Semantics: Multiplies numbers together.

Summary: Divide the first number by the second.

Syntax: Number Left / Number Right

Returns: Number

Constraints: None

Semantics: Divides numbers.  Dividing by zero returns an Error.

Summary: Exponentiation (Power).

Syntax: Number Left ^ Number Right

Returns: Number

Constraints: NOT(AND(Left=0; Right=0)); Evaluators may evaluate expressions where OR(Left != 0; Right != 0) evaluates to a non-Error value.

Semantics: Returns POWER(Left, Right).

Summary: Report if two values are equal

Syntax: Scalar Left = Scalar Right

Returns: Logical

Constraints: None

Semantics: Returns TRUE if two values are equal. If the values differ in type, return FALSE. If the values are both Number, return TRUE if they are considered equal, else return FALSE. If they are both Text, return TRUE if the two values match, else return FALSE. For Text values, if the calculation setting HOST-CASE-SENSITIVE is false, text is compared but characters differencing only in case are considered equal. If they are both Logicals, return TRUE if they are identical, else return FALSE. Error values cannot be compared to a constant Error value to determine if that is the same Error value.

Evaluators may approximate and test equality of two numeric values with an accuracy of the magnitude of the given values scaled by the number of available bits in the mantissa, ignoring some least significant bits and thus providing compensation for not exactly representable values.

The result of “1=TRUE()” is FALSE for evaluators that implement a distinct Logical type and TRUE if they don't.

Summary: Report if two values are not equal

Syntax: Any Left <> Any Right

Returns: Logical

Constraints: None

Semantics: Returns NOT(Left = Right) if Left and Right are not Error. For Text values, if the calculation setting HOST-CASE-SENSITIVE is false, text is compared but characters differencing only in case are considered equal.

If either Left and Right are an Error, the result is an Error; this operator cannot be used to determine if two Errors are the same kind of Error.

Note: In some user interfaces the infix operator “<>” is displayed (or accepted) as “!=” or “≠”.

Summary: Report if two values have the given order

Syntax: Scalar Left op Scalar Right

 where op is one of: "<", "<=", ">", or ">="

Returns: Logical

Constraints: None

Semantics: Returns TRUE if the two values are less than, less than or equal, greater than, or greater than or equal (respectively). If both Left and Right are Numbers, compare them as numbers. If both Left and Right are Text, compare them as text; if the calculation setting HOST-CASE-SENSITIVE is false, text is compared but characters are compared ignoring case. If the values are both Logical, convert both to Number and then compare as Number.

These functions return one of TRUE, FALSE, or an Error if Left and Right have different types, but it is implementation-defined which of these results will be returned when the types differ.

Summary: Concatenate two strings.

Syntax: Text Left & Text Right

Returns: Text

Constraints: None

Semantics: Concatenates two text (string) values.

Note: The infix operator “&” is equivalent to CONCATENATE(Left,Right).

Summary: Computes an inclusive range given two references

Syntax: Reference Left : Reference Right

Returns: Reference

Constraints: None

Semantics: Takes two references and computes the range, that is, a reference to the smallest 3-dimensional cube of cells that include both Left and Right including the cells on sheets positioned between Left and Right. Left and Right need not be a single cell. For an expression such as [.B4:.B5]:[.C5] the resulting range is B4:C5. In case Left and/or Right involve a reference list (result of operator reference union), the range is computed and extended for each element of the list(s).

Note: For example, (a,b,c,d denoting one reference each) (a~b):(c~d) computes a:b:c:d determining the outermost front-top-left and rear-bottom-right corners.

Left and Right may also be defined names or the result of a function returning a reference, such as INDIRECT.

Summary: Compute the intersection of two references

Syntax: Reference Left ! Reference Right

Returns: Reference

Constraints: None

Semantics: Takes two references and computes the intersection - a reference to the intersection of cells in both Left and Right. If there are no cells in common, returns an Error.

If Left or Right are not of type Reference or ReferenceList, an Error shall be returned.

If Left and/or Right are reference lists (result of infix operator reference concatenation), the intersection is computed for each combination of Left and Right, producing a reference list of intersections.

Note 1: For example (a,b,c,d denoting one reference each):
(a~b)!(c~d) will compute (a!c)~(a!d)~(b!c)~(b!d)

If for a resulting intersection there are no cells in common, the element is ignored and omitted from the result list. If for all intersections there are no cells in common and the result list is empty, an Error is returned.

Note 2: Intersection is notated as "!" in OpenFormula format, but as a space character in some user interfaces.

Summary: Concatenate two references

Syntax: Reference Left ~ Reference Right

Returns: ReferenceList

Constraints: None

Semantics: Takes two references and computes the "cell union", which is a concatenation of the reference Left followed by the reference Right. This is not the same as a union in set theory; duplicate references to cells are not removed. The resulting reference will have the number of areas, as reported by AREAS, as AREAS(Left)+AREAS(Right).

Note: Concatenation is notated as "~" in OpenFormula format, but as a comma or “+” in some user interfaces.

If Left or Right are not of type Reference or ReferenceList, an Error shall be returned.

Test Cases:

Summary: Divide the operand by 100

Syntax: Number Left %

Returns: Number

Constraints: None

Semantics: Computes Left / 100.

Summary: No operation; returns its one argument.

Syntax: + Any Right

Returns: Any

Constraints: None

Semantics: Returns the value given to it. Note that this does not convert a value to the Number type. In fact, it does no conversion at all of a Number, Logical, or Text value - it returns the same Number, Logical, or Text value (respectively). The "+" applied to a reference may return the reference, or an Error.

Summary: Negate its one argument.

Syntax: - Number Right

Returns: Number

Constraints: None

Semantics: Computes 0 - Right.

Matrix functions operate on matrices.

A matrix with M rows and N columns is defined by

Summary: Calculates the determinant of a matrix.

Syntax: MDETERM( ForceArray Array A )

Returns: Number

Constraints: Only square matrices are allowed.

Semantics: Returns the determinant of matrix A. The determinant is defined by

Summary: Returns the inverse of a matrix.

Syntax: MINVERSE( ForceArray Array A )

Returns: Array

Constraints: Only square matrices are allowed.

Invertible matrices have a non-zero determinant. If the matrix is not invertible, this function should return an Error value.

Summary: Multiplies the matrices A and B.

Syntax: MMULT( ForceArray Array A ; ForceArray Array B )

Returns: Array

Constraints: COLUMNS(A) = ROWS(B)

Summary: Creates a unit matrix of a specified dimension N.

Syntax: MUNIT( Integer N )

Returns: Array

Constraints: The dimension has to be greater than zero.

Semantics: Creates the unit matrix (identity matrix) of dimension N.

Summary: Returns the transpose of a matrix.

Syntax: TRANSPOSE( Array A )

Returns: Array

Constraints: None

Semantics: Returns the transpose AT of a matrix A, i.e. rows and columns of the matrix are exchanged.

Evaluators shall support unsigned integer values and results of at least 48 bits (values from 0 to 2^48-1 inclusive). Operations that receive or result in a value that cannot be represented within 48 bits are implementation-defined.

Summary: Returns bitwise “and” of its parameters

Syntax: BITAND( Integer X ; Integer Y )

Returns: Number

Constraints:  X ≥ 0, Y ≥ 0

Semantics: Returns bitwise “and” of its parameters. In the result, each bit position is 1 if and only if all parameters' bits at that position are also 1; else it is 0.

Summary: Returns left shift of value X by N bits (“<<”)

Syntax: BITLSHIFT( Integer X ; Integer N )

Returns: Number

Constraints: X ≥ 0

Semantics: Returns left shift of value X by N bit positions:

• •If N < 0, return BITRSHIFT(X,-N) 

• •if N = 0, return X 

• •if N > 0, return X * 2^N 

Summary: Returns bitwise “or” of its parameters

Syntax: BITOR( Integer X ; Integer Y )

Returns: Number

Constraints: X ≥ 0, Y ≥ 0

Semantics: Returns bitwise “or” of its parameters. In the result, each bit position is 1 if any of its parameters' bits at that position are also 1; else it is 0.

Summary: Returns right shift of value X by N bits (“>>”)

Syntax: BITRSHIFT( Integer X ; Integer N )

Returns: Number

Constraints: X ≥ 0

Semantics: Returns right shift of value X by N bit positions:

• •If N < 0, return BITLSHIFT(X,-N) 

• •if N = 0, return X 

• •if N > 0, return INT(X / 2^N) 

Summary: Returns bitwise “exclusive or” of its parameters

Syntax: BITXOR( Integer X ; Integer Y )

Returns: Number

Constraints: X ≥ 0, Y ≥ 0

Semantics: Returns bitwise “exclusive or” (xor) of its parameters. In the result, each bit position is 1 if one or the other parameters' bits at that position are 1; else it is 0.

Byte-position text functions are like their equivalent ordinary text functions, but manipulate byte positions rather than a count of the number of characters.  Byte positions are integers that may depend on the specific text representation used by the implementation. Byte positions are by definition implementation-dependent and reliance upon them reduces interoperability.

The pseudotypes ByteLength and BytePosition are Integers, but their exact meanings and values are not further defined by this specification.

Summary: Returns the starting position of a given text, using byte positions.

Syntax: FINDB( Text Search ; Text T [ ; BytePosition Start ] )

Returns: BytePosition

Semantics: The same as FIND, but using byte positions.

Summary: Returns a selected number of text characters from the left, using a byte position.

Syntax: LEFTB( Text T [ ; ByteLength Length ] )

Returns: Text

Semantics: As LEFT, but using a byte position.

Summary: Returns the length of given text in units compatible with byte positions

Syntax: LENB( Text T )

Returns: ByteLength

Constraints: None.

Semantics: As LEN, but compatible with byte position values.

Summary: Returns extracted text, given an original text, starting position using a byte position, and length.

Syntax: MIDB( Text T ; BytePosition Start ; ByteLength Length )

Returns: Text

Constraints: Length ≥ 0.

Semantics: As MID, but using byte positions.

Summary: Returns text where an old text is replaced with a new text, using byte positions.

Syntax: REPLACEB( Text T ; BytePosition Start ; ByteLength Len ; Text New )

Returns: Text

Semantics: As REPLACE, but using byte positions.

Summary: Returns a selected number of text characters from the right, using byte position.

Syntax: RIGHTB( Text T [ ; ByteLength Length ] )

Returns: Text

Semantics: As RIGHT, but using byte positions.

Summary: Returns the starting position of a given text, using byte positions.

Syntax: SEARCHB( Text Search ; Text T [ ; BytePosition Start ] )

Returns: BytePosition

Semantics: As SEARCH, but using byte positions.

Functions for complex numbers.

Summary: Creates a complex number from a given real coefficient and imaginary coefficient.

Syntax: COMPLEX( Number Real ; Number Imaginary [ ; Text Suffix ] )

Returns: Complex

Constraints: None

Semantics: Constructs a complex number from the given coefficients. The third parameter Suffix is optional, and should be either “i” or “j”. Upper case “I” or “J” are not accepted for the suffix parameter.

Summary: Returns the absolute value of a complex number.

Syntax: IMABS( Complex X )

Returns: Number

Constraints: None

Summary: Returns the imaginary coefficient of a complex number.

Syntax: IMAGINARY( Complex X )

Returns: Number

Constraints: None

Semantics: If X = a + bi or X = a + bj, then the imaginary coefficient is b.

Summary: Returns the complex argument of a complex number.

Syntax: IMARGUMENT( Complex X )

Returns: Number

Constraints: None

Semantics: If X = a + bi = r(cosφ + isinφ), a or b is not 0 and -π < φ ≤ π, then the complex argument is φ. φ is expressed by radians. If X = 0, then IMARGUMENT(X) is implementation-defined and either 0 or an error.

Summary: Returns the complex conjugate of a complex number.

Syntax: IMCONJUGATE( Complex X )

Returns: Complex

Constraints: None

Semantics: If X = a + bi, then the complex conjugate is a - bi.

Summary: Returns the cosine of a complex number.

Syntax: IMCOS( Complex X )

Returns: Complex

Constraints: None

Semantics: If X = a + bi, then cos(X) = cos(a)cosh(b) - sin(a)sinh(b)i.