Applications should document all implementation-defined and variances from this standard in a manner that the application users can obtain the information.

In a few cases a specific approach is required (e.g., string indexes begin at one), which may be different than the user interface of some implementations.

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

• ●Some conversions between types are not required to be automatic. In particular, applications may, but need not, perform automatic conversion of text in a cell when it is to be used as a number (see Auto Text to Number). 

• ●There need not be a distinguishable Logical type. Applications may have a Logical type distinct from Number (see Distinct Logical), but Logical values may also be represented by the Number type using the values 1 (True) and 0 (False). This means that functions that take number sequences (such as SUM) may or may not include true and false values in the sequence. 

• ●Applications vary on the set of Errors they support. In this specification. The only distinguished Error is #N/A; all other errors are simply errors, allowing applications to choose the Error set that best meets their needs. 

• ●In this specification, string index positions start from 1. Users of applications with string index positions starting from 0 shall add and subtract 1 on import/export of this format, as appropriate. 

• ●Database criteria match patterns (such as the pattern matching language for text) have historically varied: Some support glob syntax (e.g., a*b is a, followed by 0 or more characters, followed by b), while others support traditional regular expression syntax (e.g., a*b is zero or more a’s, followed by b). This specification supports both pattern languages. 

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 #VALUE! 

         Note 3: in cell D4 : =ABS(A1:C1) => #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) => #VALUE! 

2. 2)Matrix evaluation. 

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

   1. 2.1)The portion of a non-scalar result to be displayed may 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";#VALUE!} 

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 value. 

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. All two-digit year values are 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 are performed if the results are not within HOST-ITERATION-MAXIMUM-DIFFERENCE. 

The function HOST-REFERENCE-RESOLVER(Reference) is implementation defined. This function takes as input a Unicode string containing a Reference according to section 4.8 and returns a resolved value.

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.

Evaluators shall support all dates from 1904-01-01 through 9999-12-31 (inclusive) in calculations, should support dates from 1899-12-30 through 9999-12-31 (inclusive) and may support a wider date range.

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.

A Logical value is a subtype of Number where TRUE() returns 1 and FALSE() returns 0.

A complex number (sometimes also called an imaginary number) is a pair of real numbers including a real part and an imaginary part. In mathematics, complex numbers are often written as x + iy, where x (the real part) and y (the imaginary part) are real numbers and i is

. A complex number can also be written as reiθ = rcos(θ) + irsin(θ), where r is the modulus of the complex number (a real number) and θ is the argument or phase (a real number representing an angle in radians).

A complex number may, but need not be, represented as a Number or Text. The results of the functions ISNUMBER() and ISTEXT() are implementation-defined when applied to a complex number.

A Logical value (also called a Boolean value) is a value with one of two values: TRUE() and FALSE().

An Error is one of a set of possible error values. Implementations may have many different error values, but one error value in particular is distinct: #N/A, the result of the NA() function. Users may choose to enter some data values as #N/A, so that this error value propagates to any other formula that uses it, and may test for this using the function ISNA().

Functions and operators that receive one or more error values as an input shall produce one of those input error values as their result, except when the formula or operator is specifically defined to do otherwise.

In an OpenDocument document, if an error value is the result of a cell computation it shall be stored as if it was a string. That is, the office:value-type of an error value is string; if the computed value is stored, it is stored in the attribute office:string-value.

Note: This does not change an Error into a string type (since the Error will be restored on recalculation); this enables applications which cannot recalculate values to display the error information.

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

Cell positions in a cell cuboid/rectangle/strip can resolve to empty cells (section 3.7).

The definitions of specific operations and functions that allow references as operands and parameters stipulate any particular limitations there are on forms of references and how empty cells, when permitted, are interpreted.

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.

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 shall be 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.

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 insensitive.

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.

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. When a formula is stored in an XML attribute, XML escaping rules apply: thus inside an XML attribute double-quote characters shall be escaped (e.g., as &quot;) and carriage return characters in a String (e.g., as &#x0D;) . 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 “^”, that “^” is left-associative, and that 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 NA().

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 NA().

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.

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 Lables" 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 document type 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 “'” (APOSTROPHE, U+0027). While both Source and QuotedSheetName can begin with the single-quote character “'” (APOSTROPHE, U+0027), 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; a non-empty sheetname shall be followed by the '.' (FULL STOP, U+002E) character as the next non-whitespace.

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

Inline Error constants shall have the following syntax:

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

Specific Error values are indicated by an identifier.

An unknown constant Error value shall be mapped into an Error value 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 constant back out, where possible, but evaluators may write a different Error value for a formula than they did when reading it for Errors other than #N/A. Whitespace shall not be included in an Error name.

Evaluators should use a human-comprehensible name, not a numeric id, for constant Error values 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 parentheses. 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.

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

• ●Name: The function/operator name., 

• ●Summary: One sentence briefly describing the function or operator. 

• ●Syntax: 

  • ●Parameter names are shown in order, with each parameter prefixed by the type or pseudo-type of that parameter. If the type has multiple names separated by “|”, then any of those types are permitted. 

  • ●A { ... } indicates a list of zero or more parameters, separated by the function parameter separator character. 

  • ●A { ... } followed by a superscripted + indicates a list of one or more parameters, separated by the function parameter separator character. 

  • ●Components surrounded by [ ... ] are optional. Optional components may be omitted. 

  • ●An optional parameter followed by the = symbol has the default value given after the equal sign. 

  • ●Parameters are separated with a semicolon (";"), as per the OpenFormula syntax. 

    When a function is given a value of a different type, the parameters are first converted using the implicit conversion rules before the function operates on its parameters. 

Evaluators may extend functions by permitting fewer or additional parameters, which documents may use. Extended functions may result in a lack of interoperability.

• ●Returns: Return type (e.g., Number, Text, Logical, Reference). 

• ●Constraints: A description of constraints, in addition to the constraints imposed by the parameter types. If there are no additional constraints beyond those imposed by the parameter types, this is "None". If a constraint is not met, the function/operator shall return an Error unless otherwise noted. 

• ●Semantics: This text describes what the function/operator does. 

  If a parameter is a pseudotype, but the provided value fails to meet the requirements for that type, the behavior is implementation-defined. 

  Note: Functions and operators are defined by mathematical formulas or by an OpenFormula formula. Formulas define the correct result, and not the algorithm for calculation. Since computing systems have limited precision and range of numbers, some functions cannot or should not be naively implemented as their formulas suggest. This specification defines the mathematically correct answer, and allows implementors to choose the best algorithm that will meet that definition. 

• ●Comment: Explanatory comment. 

• ●See also A list of related operators and functions. 

The implicit conversion operators omit many of these items, e.g., the syntax (since there is none).

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 value is of type:

• ●Number, return it. 

• ●Logical, return 0 if FALSE, 1 if TRUE. 

• ●Text: The specific conversion is implementation-defined; an evaluator may return 0, an Error value, or the results of its attempt to convert the Text value to a Number (and fall back to 0 or Error if it fails to do so). Evaluators may apply VALUE() or some other function to do this conversion, should they choose to do so. Conversion depends on the actual locale the application runs in, especially if group or decimal separators are involved. 

• ●Reference: If the reference covers more than one cell, do an implied intersection to determine which cell to use. Then obtain the value of the single cell and perform the rules as above. If the calculation setting “precision-as-shown” is true, then convert the number to the closest possible representation of the displayed number. If the cell is empty (blank), use 0 (zero) as the value. Evaluators may choose to convert references to Text in a different manner than they handle converting embedded Text to a Number. 

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:

• ●Number that is not complex, use the Number with 0 as the imaginary part. 

• ●Text, attempt to convert to complex number using VALUE(). If it is a number that is not complex, use it. If the text matches one of these patterns, accept it: 

    ([+-]?Number [+-])?Number[ij]

    [+-]?Number[ij]

• ●Logical, convert to Number and then handle as Number. 

• ●reference: Convert to Scalar, then use the rules above.  If the reference is to an empty cell, consider it equal to 0. 

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 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:

• ●Number, return it. 

• ●Text, pass to DATEVALUE, and if non-Error, return it. If DATEVALUE would return an Error, an evaluator may attempt to convert to a Number in other ways (such as by calling VALUE); this is implementation-defined. If the evaluator cannot convert to Number, it returns an Error. 

• ●Logical, the result is implementation-defined, either a Number or Error 

• ●Reference: perform conversion to scalar, then perform as above.  If the cell is empty, return 0. 

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

• ●Number, return it. 

• ●Text, pass to TIMEVALUE, and if non-Error, return it. If TIMEVALUE would return an Error, an evaluator may attempt to convert to a Number in other ways (such as by calling VALUE); this is implementation-defined. If the evaluator cannot convert to Number, it returns an Error. 

• ●Logical, the result is implementation-defined, either a Number or Error 

• ●Reference: perform conversion to scalar, then perform as above.  If the cell is empty, return 0. 

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 second number into the first.

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, Error #NULL! 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.

Summary: Calculates the determinant of a matrix.

Syntax: MDETERM( ForceArray Array matrix )

Returns: Number

Constraints: Only square matrices are allowed.

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

 

where P denotes a permutation of the numbers 1, 2, ..., n and

is the sign of the permutation, which is +1 for an even amount of permutations (i.e., permutations that can be written as the composition of an even number of transpositions), -1 otherwise. A transposition on 1, ..., n is a permutation of 1, ..., n with exactly (n-2) numbers fixed.

Summary: Returns the inverse of a matrix.

Syntax: MINVERSE( ForceArray Array matrix )

Returns: Array

Constraints: Only square matrices are allowed.

Semantics: Calculates the inverse

 

of matrix A. The matrix A multiplied with its inverse

results in the unity matrix of the same dimension as A:

 

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)

Semantics: Returns the matrix product of the two matrices. The elements

of the resulting matrix

formula

, are defined by:

formula

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.

formula

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.

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.

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 by 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

Semantics: If X=a+bi or X=a+bj, the absolute value =

 

; if N=r(cosφ + isinφ), the absolute value = r.

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.

Summary: Returns the cotangent of a complex number

Syntax: IMCOT(Complex N)

Returns: Complex

Constraints: None

Semantics: Equivalent to the following (except N is computed only once):

    IMDIV(IMCOS(N);IMSIN(N))

Summary: Returns the cosecant of a complex number

Syntax: IMCSC(Complex N)

Returns: Complex

Constraints: None

Semantics: Equivalent to the following:

    IMDIV(1;IMSIN(N))

Summary: Returns the hyperbolic cosecant of a complex number

Syntax: IMCSCH( Complex N )

Returns: Number

Constraints: None

Semantics: Computes the hyperbolic cosecant. This is equivalent to:

    IMDIV(1;IMSINH(N))

See also IMSINH, CSCH

Summary: Divides the second number into the first.

Syntax: IMDIV( Complex X ; Complex Y )

Returns: Complex

Constraints: None

Semantics: Given X=a+bi and Y=c+di, return the quotient

 

Division by zero returns an Error.

Summary: Returns the exponent of e and a complex number.

Syntax: IMEXP( Complex X )

Returns: Complex

Constraints: None

Semantics: If X=a+bi, the result is

 

.

Summary: Returns the natural logarithm of a complex number.

Syntax: IMLN( Complex X )

Returns: Complex

Constraints: X ≠ 0

Semantics: COMPLEX(LN(IMABS(X)); IMARGUMENT(X)) .

Summary: Returns the common logarithm of a complex number.

Syntax: IMLOG10( Complex X )

Returns: Complex

Constraints: X ≠ 0

Semantics: IMLOG10(X) is IMDIV(IMLN(X);COMPLEX(LN(10);0)) .

Summary: Returns the binary logarithm of a complex number.

Syntax: IMLOG2( Complex X )

Returns: Complex

Constraints: X ≠ 0

Semantics: IMLOG2(X) is IMDIV(IMLN(X);COMPLEX(LN(2);0)) .

Summary: Returns the complex number X raised to the Yth power.

Syntax: IMPOWER( Complex X ; Complex Y ) or IMPOWER( Complex X ; Number Y)

Returns: Complex

Constraints: X ≠ 0

Semantics: IMPOWER(X;Y) is IMEXP(IMPRODUCT(Y; IMLN(X)))

An evaluator implementing this function shall permit any Number Y but may also allow any Complex Y.

Summary: Returns the product of complex numbers.

Syntax: IMPRODUCT( { ComplexSequence N }+ )

Returns: Complex

Constraints: None

Semantics: Multiply the complex numbers together. Given two complex numbers X=a+bi and Y=c+di, the product X*Y = (ac-bd) + (ad+bc)i

Summary: Returns the real coefficient of a complex number

Syntax: IMREAL( Complex N )

Returns: Number

Constraints: None

Semantics: If N=a+bi or N=a+bj, then the real coefficient is a.

Summary: Returns the sine of a complex number

Syntax: IMSIN( Complex N )

Returns: Complex

Constraints: None

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

Summary: Returns the secant of a complex number

Syntax: IMSEC(Complex N)

Returns: Complex

Constraints: None

Semantics: Equivalent to the following:

    IMDIV(1;IMCOS(N))

Summary: Returns the hyperbolic secant of a complex number

Syntax: IMSECH( Complex N )

Returns: Number

Constraints: None

Semantics: Computes the hyperbolic secant. This is equivalent to:

    IMDIV(1;IMCOSH(N))

See also IMCOSH, SECH

Summary: Returns the square root of a complex number

Syntax: IMSQRT( Complex N )

Returns: Complex

Constraints: None

Semantics: If N= 0+0i, then IMSQRT(N)=0. Otherwise IMSQRT(N) is SQRT(IMABS(N)) * sin(IMARGUMENT(N)/2) + SQRT(IMABS(N)) * cos(IMARGUMENT(N)/2)i.

Summary: Subtracts the second complex number from the first.

Syntax: IMSUB( Complex X ; Complex Y )

Returns: Complex

Constraints: None

Semantics: Subtract complex number Y from X.

Summary: Sums (add) a set of complex numbers, including all numbers in ranges

Syntax: IMSUM( { ComplexSequence N }+ )

Returns: Complex Number

Constraints: None

Semantics: Adds complex numbers together. Text that cannot be converted to a complex number is ignored.

It is implementation-defined what happens if this function is given zero parameters; an evaluator may either produce an Error or the Number 0 if it is given zero parameters.

Summary: Returns the tangent of a complex number

Syntax: IMTAN(Complex N)

Returns: Complex

Constraints: None

Semantics: Equivalent to the following (except N is computed only once):

    IMDIV(IMSIN(N);IMCOS(N))

Summary: Finds the average of values in a given field from the records (rows) in a database that match a search criteria.

Syntax: DAVERAGE( Database D ; Field F ; Criteria C )

Returns: Number

Constraints: None

Semantics: Perform AVERAGE on data records in database D field F that match criteria C.

Summary: Counts the number of records (rows) in a database that match a search criteria and contain numerical values.

Syntax: DCOUNT( Database D ; Field F ; Criteria C )

Returns: Number

Constraints: None

Semantics: Perform COUNT on data records in database D field F that match criteria C.

Summary: Counts the number of records (rows) in a database that match a search criteria and contain values (as COUNTA).

Syntax: DCOUNTA( Database D ; Field F ; Criteria C )