Open Document Format for Office Applications (OpenDocument) Version 1.2

Part 2: Recalculated Formula (OpenFormula) Format

Committee Draft 05

19 June 2010

Specification URIs:

This Version:

http://docs.oasis-open.org/office/v1.2/cd05/OpenDocument-v1.2-cd05-part2.odt (Authoritative)
http://docs.oasis-open.org/office/v1.2/cd05/OpenDocument-v1.2-cd05-part2.pdf
http://docs.oasis-open.org/office/v1.2/cd05/OpenDocument-v1.2-cd05-part2.html

Previous Version:

http://docs.oasis-open.org/office/v1.1/OS/OpenDocument-v1.1.odt
http://docs.oasis-open.org/office/v1.1/OS/OpenDocument-v1.1.pdf
http://docs.oasis-open.org/office/v1.1/OS/OpenDocument-v1.1-html/OpenDocument-v1.1.html

Latest Version:

http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt
http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.pdf
http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.html

Technical Committee:

OASIS Open Document Format for Office Applications (OpenDocument) TC

Chairs:

Robert Weir, IBM
Michael Brauer, Oracle Corporation

Editors:

David A. Wheeler <dwheeler@dwheeler.com>,

Patrick Durusau <patrick@durusau.net>
Eike Rathke, Oracle Corporation <erack@sun.com>
Robert Weir, IBM <robert_weir@us.ibm.com>

Related Work:

This document is part of the OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2 specification.

The OpenDocument v1.2 specification has these parts:

OpenDocument v1.2 part 1; OpenDocument Schema
OpenDocument v1.2 part 2 (this part): Recalculated Formula (OpenFormula) Format
OpenDocument v1.2 part 3: Packages

Declared XML Namespaces:

None.

Abstract:

This document is part of the Open Document Format for Office Applications (OpenDocument) Version 1.2 specification.

It defines a formula language to be used in OpenDocument documents.

Status:

This document was last revised or approved by the OASIS Open Document Format for Office Applications (OpenDocument) Technical Committee on the above date. The level of approval is also listed above. Check the current location noted above for possible later revisions of this document. This document is updated periodically on no particular schedule.

Technical Committee members should send comments on this specification to the Technical Committee's email list. Others should send comments to the Technical Committee by using the "Send A Comment" button on the Technical Committee's web page at

www.oasis-open.org/committees/office.

For information on whether any patents have been disclosed that may be essential to implementing this specification, and any offers of patent licensing terms, please refer to the Intellectual Property Rights section of the Technical Committee web page

(www.oasis-open.org/committees/office/ipr.php.

The non-normative errata page for this specification is located at www.oasis-open.org/committees/office.

Notices

Copyright © OASIS® 2002–2010. All Rights Reserved.

All capitalized terms in the following text have the meanings assigned to them in the OASIS Intellectual Property Rights Policy (the "OASIS IPR Policy"). The full Policy may be found at the OASIS website.

This document and translations of it may be copied and furnished to others, and derivative works that comment on or otherwise explain it or assist in its implementation may be prepared, copied, published, and distributed, in whole or in part, without restriction of any kind, provided that the above copyright notice and this section are included on all such copies and derivative works. However, this document itself may not be modified in any way, including by removing the copyright notice or references to OASIS, except as needed for the purpose of developing any document or deliverable produced by an OASIS Technical Committee (in which case the rules applicable to copyrights, as set forth in the OASIS IPR Policy, must be followed) or as required to translate it into languages other than English.

The limited permissions granted above are perpetual and will not be revoked by OASIS or its successors or assigns.

This document and the information contained herein is provided on an "AS IS" basis and OASIS DISCLAIMS ALL WARRANTIES, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO ANY WARRANTY THAT THE USE OF THE INFORMATION HEREIN WILL NOT INFRINGE ANY OWNERSHIP RIGHTS OR ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.

OASIS requests that any OASIS Party or any other party that believes it has patent claims that would necessarily be infringed by implementations of this OASIS Committee Specification or OASIS Standard, to notify OASIS TC Administrator and provide an indication of its willingness to grant patent licenses to such patent claims in a manner consistent with the IPR Mode of the OASIS Technical Committee that produced this specification.

OASIS invites any party to contact the OASIS TC Administrator if it is aware of a claim of ownership of any patent claims that would necessarily be infringed by implementations of this specification by a patent holder that is not willing to provide a license to such patent claims in a manner consistent with the IPR Mode of the OASIS Technical Committee that produced this specification. OASIS may include such claims on its website, but disclaims any obligation to do so.

OASIS takes no position regarding the validity or scope of any intellectual property or other rights that might be claimed to pertain to the implementation or use of the technology described in this document or the extent to which any license under such rights might or might not be available; neither does it represent that it has made any effort to identify any such rights. Information on OASIS' procedures with respect to rights in any document or deliverable produced by an OASIS Technical Committee can be found on the OASIS website. Copies of claims of rights made available for publication and any assurances of licenses to be made available, or the result of an attempt made to obtain a general license or permission for the use of such proprietary rights by implementers or users of this OASIS Committee Specification or OASIS Standard, can be obtained from the OASIS TC Administrator. OASIS makes no representation that any information or list of intellectual property rights will at any time be complete, or that any claims in such list are, in fact, Essential Claims.

The names "OASIS", “OpenDocument”, “Open Document Format” and “ODF” are trademarks of OASIS, the owner and developer of this specification, and should be used only to refer to the organization and its official outputs. OASIS welcomes reference to, and implementation and use of, specifications, while reserving the right to enforce its marks against misleading uses. Please see http://www.oasis-open.org/who/trademark.php for above guidance.

Table of Contents

1Introduction

1.1Introduction

This document is part of the Open Document Format for Office Applications (OpenDocument) Version 1.2 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.

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.

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.

1.2Terminology

All text is normative unless otherwise labeled.

Within the normative text of this specification, the terms "shall", "shall not", "should", "should not", "may" and “need not” are to be interpreted as described in Annex H of [ISO/IEC Directives].

1.3Purpose

OpenFormula defines:

1. 1.data types 

2. 2.syntax 

3. 3.semantics 

for recalculated formulas.

OpenFormula also defines functions.

OpenFormula does not define:

1. 1.a user interface 

2. 2.a general notation for mathematical expressions 

1.4Normative References

[ISO/IEC Directives]        ISO/IEC Directives, Part 2 (Fifth Edition) Rules for the structure and drafting of International Standards, International Organization for Standardization and International Electrotechnical Commission, 2004.

[ODF11]        OASIS Standard, Open Document Format for Office Applications (OpenDocument) v1.1, February 2007, http://docs.oasis-open.org/office/v1.1/OS/OpenDocument-v1.1.pdf.

[RFC3986]        T. Berners-Lee, R. Fielding, L. Masinter, Uniform Resource Identifier (URI): Generic Syntax, http://www.ietf.org/rfc/rfc3986.txt, IETF, 2005.

[RFC3987]        M. Duerst, M. Suignard, Internationalized Resource Identifiers (IRIs), http://www.ietf.org/rfc/rfc3987.txt, IETF, 2005.

[UNICODE]        The Unicode Consortium. The Unicode Standard, Version 5.2.0, defined by: The Unicode Standard, Version 5.2 (Mountain View, CA, The Unicode Consortium, 2009. ISBN 978-1-936213-00-9). (http://www.unicode.org/versions/Unicode5.2.0/).

[XML1.0]        Tim Bray, Jean Paoli, C. M. Sperberg-McQueen, Eve Maler, François Yergeau , Extensible Markup Language (XML) 1.0 (Fourth Edition), http://www.w3.org/TR/2006/REC-xml-20060816/, W3C, 2006.

1.5Non-Normative References

2Expressions and Evaluators

2.1OpenDocument Formula Expression

An OpenDocument formula expression shall adhere to the expression syntax defined in chapter 4. It may use subsets or supersets of OpenFormula.

2.2Evaluators

2.2.1OpenDocument Formula Evaluator

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: An expression may reference a nonstandard function 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 standard may impair the interoperability of the resulting expression(s).

Note: This specification defines formulas in terms of a canonical text representation used for exchange. If formulas are contained in XML attributes some characters shall be escaped as required by the XML specification (e.g., the character & shall be escaped in XML attributes using notations such as &amp;). All string and character literals references by this specification are in the value space defined by [UNICODE] thus, “A” is U+0041, “Z” is U+005A, and the range of characters “A-Z” is the range U+0041 through U+005A inclusive.

2.2.2OpenDocument Formula Small Group Evaluator

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

1. A)It shall implement at least the limits defined in the “Basic Limits” section. 

2. B)It shall implement the syntax defined in these sections on syntax: Criteria; Basic Expressions; Constant Numbers; Constant Strings; Operators; Functions and Function Parameters; Nonstandard Function Names; References; Simple Named Expressions; Errors; Whitespace. 

3. C)It shall implement all implicit conversions for the types it implements, at least Text, Conversion to Number, Reference, Conversion to Logical, and Error. 

4. D)It shall implement the following operators (which are all the operators except reference union (~)): Infix Operator Ordered Comparison ("<", "<=", ">", ">="); Infix Operator "&”; Infix Operator "+”; Infix Operator "-”; Infix Operator "*”; Infix Operator "/”; Infix Operator "^”; Infix Operator "=”; Infix Operator "<>”; Postfix Operator “%”; Prefix Operator “+”; Prefix Operator “-”; Infix Operator Reference Intersection ("!"); Infix Operator Range (":"). 

5. E)It shall implement at least the following functions as defined in this specification: ABS 6.16.2 ; ACOS 6.16.3 ; AND 6.15.2 ; ASIN 6.16.7 ; ATAN 6.16.9 ; ATAN2 6.16.10 ; AVERAGE 6.18.3 ; AVERAGEIF 6.18.5 ; CHOOSE 6.14.3 ; COLUMNS 6.13.5 ; COS 6.16.19 ; COUNT 6.13.6 ; COUNTA 6.13.7 ; COUNTBLANK 6.13.8 ; COUNTIF 6.13.9 ; DATE 6.10.2 ; DAVERAGE 6.9.2 ; DAY 6.10.5 ; DCOUNT 6.9.3 ; DCOUNTA 6.9.4 ; DDB 6.12.15 ; DEGREES 6.16.25 ; DGET 6.9.5 ; DMAX 6.9.6 ; DMIN 6.9.7 ; DPRODUCT 6.9.8 ; DSTDEV 6.9.9 ; DSTDEVP 6.9.10 ; DSUM 6.9.11 ; DVAR 6.9.12 ; DVARP 6.9.13 ; EVEN 6.16.30 ; EXACT 6.20.8 ; EXP 6.16.31 ; FACT 6.16.32 ; FALSE 6.15.3 ; FIND 6.20.9 ; FV 6.12.21 ; HLOOKUP 6.14.5 ; HOUR 6.10.10 ; IF 6.15.4 ; INDEX 6.14.6 ; INT 6.17.3 ; IRR 6.12.25 ; ISBLANK 6.13.14 ; ISERR 6.13.15 ; ISERROR 6.13.16 ; ISLOGICAL 6.13.19 ; ISNA 6.13.20 ; ISNONTEXT 6.13.21 ; ISNUMBER 6.13.22 ; ISTEXT 6.13.25 ; LEFT 6.20.12 ; LEN 6.20.13 ; LN 6.16.39 ; LOG 6.16.40 ; LOG10 6.16.41 ; LOWER 6.20.14 ; MATCH 6.14.9 ; MAX 6.18.45 ; MID 6.20.15 ; MIN 6.18.48 ; MINUTE 6.10.12 ; MOD 6.16.42 ; MONTH 6.10.13 ; N 6.13.26 ; NA 6.13.27 ; NOT 6.15.7 ; NOW 6.10.15 ; NPER 6.12.30 ; NPV 6.12.31 ; ODD 6.16.44 ; OR 6.15.8 ; PI 6.16.45 ; PMT 6.12.37 ; POWER 6.16.46 ; PRODUCT 6.16.47 ; PROPER 6.20.16 ; PV 6.12.42 ; RADIANS 6.16.49 ; RATE 6.12.43 ; REPLACE 6.20.17 ; REPT 6.20.18 ; RIGHT 6.20.19 ; ROUND 6.17.6 ; ROWS 6.13.30 ; SECOND 6.10.16 ; SIN 6.16.55 ; SLN 6.12.46 ; SQRT 6.16.58 ; STDEV 6.18.72 ; STDEVP 6.18.74 ; SUBSTITUTE 6.20.21 ; SUM 6.16.61 ; SUMIF 6.16.62 ; SYD 6.12.47 ; T 6.20.22 ; TAN 6.16.69 ; TIME 6.10.17 ; TODAY 6.10.19 ; TRIM 6.20.24 ; TRUE 6.15.9 ; TRUNC 6.17.9 ; UPPER 6.20.27 ; VALUE 6.13.34 ; VAR 6.18.82 ; VARP 6.18.84 ; VLOOKUP 6.14.12 ; WEEKDAY 6.10.20 ; YEAR 6.10.23 

6. F)It need not evaluate references that contain more than one area. 

7. G)It need not implement inline arrays, complex numbers, and the reference union operator. 

8. H)For expressions embedded in an OpenDocument document, it shall consider the values of the following host-defined properties: HOST-CASE-SENSITIVE, HOST-PRECISION-AS-SHOWN, HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL, HOST-AUTOMATIC-FIND-LABELS, HOST-USE-REGULAR-EXPRESSIONS, HOST-USE-WILDCARDS, HOST-NULL-YEAR, HOST-NULL-DATE. 

9. I)It shall support international characters for named expression identifiers. 

Note: This specification does not mandate a user interface for international characters, so a resource-constrained application may choose to not show the traditional glyph (e.g., it may show the [UNICODE] numeric code instead).

2.2.3OpenDocument Formula Medium Group Evaluator

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

1. A)It shall implement the following functions as defined in this specification: ACCRINT 6.12.2 ; ACCRINTM 6.12.3 ; ACOSH 6.16.4 ; ACOT 6.16.5 ; ACOTH 6.16.6 ; ADDRESS 6.14.2 ; ASINH 6.16.8 ; ATANH 6.16.11 ; AVEDEV 6.18.2 ; BESSELI 6.16.12 ; BESSELJ 6.16.13 ; BESSELK 6.16.14 ; BESSELY 6.16.15 ; BETADIST 6.18.7 ; BETAINV 6.18.8 ; BINOMDIST 6.18.10 ; CEILING 6.17.2 ; CHAR 6.20.3 ; CLEAN 6.20.4 ; CODE 6.20.5 ; COLUMN 6.13.4 ; COMBIN 6.16.16 ; CONCATENATE 6.20.6 ; CONFIDENCE 6.18.16 ; CONVERT 6.16.18 ; CORREL 6.18.17 ; COSH 6.16.20 ; COT 6.16.21 ; COTH 6.16.22 ; COUPDAYBS 6.12.6 ; COUPDAYS 6.12.7 ; COUPDAYSNC 6.12.8 ; COUPNCD 6.12.8 ; COUPNUM 6.12.10 ; COUPPCD 6.12.11 ; COVAR 6.18.18 ; CRITBINOM 6.18.19 ; CUMIPMT 6.12.12 ; CUMPRINC 6.12.13 ; DATEVALUE 6.10.4 ; DAYS360 6.10.7 ; DB 6.12.14 ; DEVSQ 6.18.20 ; DISC 6.12.16 ; DOLLARDE 6.12.17 ; DOLLARFR 6.12.18 ; DURATION 6.12.19 ; EFFECT 6.12.20 ; EOMONTH 6.10.9 ; ERF 6.16.27 ; ERFC 6.16.28 ; EXPONDIST 6.18.21 ; FISHER 6.18.26 ; FISHERINV 6.18.27 ; FIXED 6.20.10 ; FLOOR 6.17.4 ; FORECAST 6.18.28 ; FTEST 6.18.30 ; GAMMADIST 6.18.31 ; GAMMAINV 6.18.32 ; GAMMALN 6.16.35 ; GCD 6.16.36 ; GEOMEAN 6.18.34 ; HARMEAN 6.18.36 ; HYPGEOMDIST 6.18.37 ; INTERCEPT 6.18.38 ; INTRATE 6.12.23 ; ISEVEN 6.13.17 ; ISODD 6.13.23 ; ISOWEEKNUM 6.10.11 ; KURT 6.18.39 ; LARGE 6.18.40 ; LCM 6.16.38 ; LEGACY.CHIDIST 6.18.11 ; LEGACY.CHIINV 6.18.13 ; LEGACY.CHITEST 6.18.15 ; LEGACY.FDIST 6.18.23 ; LEGACY.FINV 6.18.25 ; LEGACY.NORMSDIST 6.18.54 ; LEGACY.NORMSINV 6.18.55 ; LEGACY.TDIST 6.18.77 ; LINEST 6.18.41 ; LOGEST 6.18.42 ; LOGINV 6.18.43 ; LOGNORMDIST 6.18.44 ; LOOKUP 6.14.8 ; MDURATION 6.12.27 ; MEDIAN 6.18.47 ; MINVERSE 6.5.3 ; MIRR 6.12.28 ; MMULT 6.5.4 ; MODE 6.18.50 ; MROUND 6.17.5 ; MULTINOMIAL 6.16.43 ; NEGBINOMDIST 6.18.51 ; NETWORKDAYS 6.10.14 ; NOMINAL 6.12.29 ; ODDFPRICE 6.12.32 ; ODDFYIELD 6.12.33 ; ODDLPRICE 6.12.34 ; ODDLYIELD 6.12.35 ; OFFSET 6.14.11 ; PEARSON 6.18.56 ; PERCENTILE 6.18.57 ; PERCENTRANK 6.18.58 ; PERMUT 6.18.59 ; POISSON 6.18.62 ; PRICE 6.12.39 ; PRICEMAT 6.12.41 ; PROB 6.18.63 ; QUARTILE 6.18.64 ; QUOTIENT 6.16.48 ; RAND 6.16.50 ; RANDBETWEEN 6.16.51 ; RANK 6.18.65 ; RECEIVED 6.12.44 ; ROMAN 6.19.17 ; ROUNDDOWN 6.17.7 ; ROUNDUP 6.17.8 ; ROW 6.13.29 ; RSQ 6.18.66 ; SERIESSUM 6.16.53 ; SIGN 6.16.54 ; SINH 6.16.56 ; SKEW 6.18.67 ; SKEWP 6.18.68 ; SLOPE 6.18.69 ; SMALL 6.18.70 ; SQRTPI 6.16.59 ; STANDARDIZE 6.18.71 ; STDEVA 6.18.73 ; STDEVPA 6.18.75 ; STEYX 6.18.76 ; SUBTOTAL 6.16.60 ; SUMPRODUCT 6.16.64 ; SUMSQ 6.16.65 ; SUMX2MY2 6.16.66 ; SUMX2PY2 6.16.67 ; SUMXMY2 6.16.68 ; TANH 6.16.70 ; TBILLEQ 6.12.48 ; TBILLPRICE 6.12.49 ; TBILLYIELD 6.12.50 ; TIMEVALUE 6.10.18 ; TINV 6.18.78 ; TRANSPOSE 6.5.6 ; TREND 6.18.79 ; TRIMMEAN 6.18.80 ; TTEST 6.18.81 ; TYPE 6.13.33 ; VARA 6.18.83 ; VDB 6.12.51 ; WEEKNUM 6.10.21 ; WEIBULL 6.18.86 ; WORKDAY 6.10.22 ; XIRR 6.12.52 ; XNPV 6.12.53 ; YEARFRAC 6.10.24 ; YIELD 6.12.54 ; YIELDDISC 6.12.55 ; YIELDMAT 6.12.56 ; ZTEST 6.18.87 

2. B)It shall implement the Infix Operator Reference Union ("~") 6.4.13 

3. C)It shall evaluate references with more than one area. 

2.2.4OpenDocument Formula Large Group Evaluator

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

1. A)It shall implement the syntax defined in these sections on syntax: Inline Arrays; Automatic Intersection; External Named Expressions. 

2. B)It shall implement the complex number type as discussed in the section on Complex Number, array formulas, and Sheet-local Named Expressions. 

It shall implement the following functions as defined in this specification: AMORDEGRC 6.12.4 ; AMORLINC 6.12.5 ; ARABIC 6.19.2 ; AREAS 6.13.2 ; ASC 6.20.2 ; AVERAGEA 6.18.4 ; AVERAGEIFS 6.18.6 ; BASE 6.19.3 ; BIN2DEC 6.19.4 ; BIN2HEX 6.19.5 ; BIN2OCT 6.19.6 ; BINOM.DIST.RANGE 6.18.9 ; BITAND 6.6.2 ; BITLSHIFT 6.6.3 ; BITOR 6.6.4 ; BITRSHIFT 6.6.5 ; BITXOR 6.6.6 ; CHISQDIST 6.18.12 ; CHISQINV 6.18.14 ; COMBINA 6.16.17 ; COMPLEX 6.8.2 ; COUNTIFS 6.13.10 ; CSC 6.16.23 ; 6.16.23CSCH 6.16.24 ; DATEDIF 6.10.3 ; DAYS 6.10.6 ; DDE 6.11.2 ; DEC2BIN 6.19.7 ; DEC2HEX 6.19.8 ; DEC2OCT 6.19.9 ; DECIMAL 6.19.10 ; DELTA 6.16.26 ; EDATE 6.10.8 ; ERROR.TYPE 6.13.11; EUROCONVERT 6.16.29 ; FACTDOUBLE 6.16.33 ; FDIST 6.18.22 ; FINDB 6.7.2 ; FINV 6.18.24 ; FORMULA 6.13.12 ; FREQUENCY 6.18.29 ; FVSCHEDULE 6.12.22 ; GAMMA 6.16.34 ; GAUSS 6.18.33 ; GESTEP 6.16.37 ; GETPIVOTDATA 6.14.4 ; GROWTH 6.18.35 ; HEX2BIN 6.19.11 ; HEX2DEC 6.19.12 ; HEX2OCT 6.19.13 ; HYPERLINK 6.11.3 ; IFERROR 6.15.5 ; IFNA 6.15.6 ; IMABS 6.8.3 ; IMAGINARY 6.8.4 ; IMARGUMENT 6.8.5 ; IMCONJUGATE 6.8.6 ; IMCOS 6.8.7 ; IMCOT 6.8.8 ; IMCSC 6.8.9 ; IMCSCH 6.8.10 ; IMDIV 6.8.11 ; IMEXP 6.8.12 ; IMLN 6.8.13 ; IMLOG10 6.8.14 ; IMLOG2 6.8.15 ; IMPOWER 6.8.16 ; IMPRODUCT 6.8.17 ; IMREAL 6.8.18 ; IMSEC 6.8.20 ; IMSECH 6.8.20 ; IMSIN 6.8.19 ; IMSQRT 6.8.22 ; IMSUB 6.8.23 ; IMSUM 6.8.24 ; IMTAN 6.8.25 ; INDIRECT 6.14.7 ; INFO 6.13.13 ; IPMT 6.12.24 ; ISFORMULA 6.13.18 ; ISPMT 6.12.26 ; ISREF 6.13.24 ; JIS 6.20.11 ; LEFTB 6.7.3 ; LENB 6.7.4 ; MAXA 6.18.46 ; MDETERM 6.5.2 ; MULTIPLE.OPERATIONS 6.14.10 ; MUNIT 6.5.5 ; MIDB 6.7.5 ; MINA 6.18.49 ; NORMDIST 6.18.52 ; NORMINV 6.18.53 ; NUMBERVALUE 6.13.28 ; OCT2BIN 6.19.14 ; OCT2DEC 6.19.15 ; OCT2HEX 6.19.16 ; PDURATION 6.12.36 ; PERMUTATIONA 6.18.60 ; PHI 6.18.61 ; PPMT 6.12.38 ; PRICEDISC 6.12.40 ; REPLACEB 6.7.6 ; RIGHTB 6.7.7 ; RRI 6.12.45 ; SEARCH 6.20.20 ; SEARCHB 6.7.8 ; SEC 6.16.52 ; SECH 6.16.57 ; SHEET 6.13.31 ; SHEETS 6.13.32 ; SUMIFS 6.16.63 ; TEXT 6.20.23 ; UNICHAR 6.20.25 ; UNICODE 6.20.26 ; VARPA 6.18.85 ; XOR 6.15.10

Note: The following functions are documented by this specification, but not included even in the Large group:CELL 6.13.3 ; DOLLAR 6.20.7

2.3Variances (Implementation-defined, Unspecified, and Behavioral Changes)

Applications should document all implementation-defined and variances from this standard in a manner that the application users can obtain the information (e.g., in the application help for the relevant function).

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

Note: Interoperability is improved by the use of the DATE and TIME functions or the textual ISO 8601 date representation because dates in that format do not rely upon epoch or locale-specific settings.

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

In an OpenDocument file, calculation settings impact formula recalculation, which can be the same or different from a particular application's defaults. These include whether or not text comparisons are case-sensitive, or if search criteria shall apply to the whole cell.

3Formula Processing Model

3.1General

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

3.2Expression Calculation

Conceptually, formulas are recalculated from the “outside in”. Any formula is an expression that produces a result. An expression is calculated as follows:

1. 1.If an expression is a constant number or string, that constant is returned 

2. 2.If it is a reference, the reference is returned. If a reference is to be displayed, the value of the reference is displayed, not the reference itself. 

3. 3.Otherwise, it is one or more operations or functions; in the case of operations, the highest-precedence operation not processed is processed first. 

   1. a)The values of all argument expressions are computed, that is, formulas are normally “eagerly” evaluated. Exceptions to eager evaluation are noted in the function or operation's specification; in particular, the IF() function does not calculate the “else” parameter if the the condition is true, and does not calculate the “then” parameter if the condition is false. The CHOOSE() function does not calculate parameters other than the chosen. Function parameters shall act as if they had been computed in left-to-right order. Operators should act as if they had been computed in the order of precedence and associativity (so they are computed left-to-right for +, *, and so on, but right-to-left for the exponentiation operator ^). 

   2. b)If any of the arguments of the function/operation are not of the correct type, the appropriate implicit conversion function is called to convert it to the correct type for the operator or function. 

   3. c)The operation or function is then called with the resulting values of its arguments. 

The above model only describes how recalculation appears to the end-user. Applications may, and typically do, optimize this process as long as the final results produce the same answer. For example, applications may parse a formula and translate it into some intermediate form (such as a byte code), which immediately descends to the “innermost” computation that needs to be calculated and then works out to the final result.

When a formula is computed, it is notionally provided a "context" as input. The context may include formula variables (including named ranges, document variables, fields, and so on), and/or additional function definitions that the formula can call. A formula may also be provided as input an ordered list of zero or more parameters (though the syntax for parameters is not given in this version of the specification). In an OpenDocument formula, this context also includes calculation settings (such as whether or not text comparisons are case-sensitive).

A formula may include calls to functions, which are normally provided the same context but with their own set of ordered parameters.

Any formula computes a single result, though that single result may actually be a set of values.

3.3Non-Scalar Evaluation (aka 'Array expressions')

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:
      =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:
         in cell B2 : =ABS(A1:C1) => ABS(B1)
         If there is no intersection the result is #VALUE! 

         Note: 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:
         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:
         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 : 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:
         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:
         ={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:
            = 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:
            = {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:
            = {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:
            ={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:
            =MID("abcd";{1;2};{1;2;3})        => {"a";"bc";#VALUE!} 

3.4Host-Defined Behaviors

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 the initial text needs to match. 

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. 

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.

3.5When recalculation occurs

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

Some functions' dependencies are difficult to determine and/or should be recalculated more frequently. These include functions that return today's date or time, random number generator functions (such as RAND 6.16.50), or ones that indirectly determine the cells to act on. Many implementations always recalculate formulas including such functions whenever a recalculation occurs. Functions that are always recalculated whenever a recalculation occurs are termed volatile functions. Functions that are often volatile functions include CELL 6.13.3, HYPERLINK 6.11.3, INDIRECT 6.14.7, INFO 6.13.13, NOW 6.10.15, OFFSET 6.14.11, RAND 6.16.50 and TODAY 6.10.19. Functions that depend on the cell position of the formula they are contained in or the position of a cell they reference need to be recalculated whenever that cell is moved, such functions are COLUMN 6.13.4, ROW 6.13.29 and SHEET 6.13.31. In addition, formulas may indicate that they should always be recalculated during a recalculation process by including a forced recalculation marker, as described in the syntax below.

3.6Numerical Models

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.

In practice, applications tend to use at least one IEEE 754-1985 binary floating-point representation, using at least the 64-bit representation and possibly larger widths for intermediate results. When IEEE 754 representations are used, results such as Inf (infinity) and Nan (not a number) are considered an Error value. Applications may use IEEE 854-1987 (which covers decimal arithmetic). In general, applications are encouraged to use appropriate standards for their numerical models. This means that applications will often not produce “exact” results, but only approximate results for a large number of places.

3.7Basic Limits

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. 

4Types

4.1General

All values defined by OpenFormula have a type. OpenFormula defines Text, Number, Complex Number, Logical, Error, Reference, ReferenceList and Array types.

4.2Text (String)

A Text value (also called a string value) is a sequence of zero or more characters.

Evaluators should accept [UNICODE] strings, but shall accept strings of ASCII (Unicode U+0020 through U+007F, inclusive) characters.

A text value of length zero is termed the empty string.

Index positions in a text value begin at 1.

4.3Number

4.3.1General

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 an arbitrary fixed or with a variable bit length. A cell with a constant numeric value has the Number type.

Note: Many evaluators implement numbers as 64-bit IEEE floating point values and use the CPU's floating-point instructions where available (so intermediate values may be represented using more than 64 bits).

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. All such Number subtypes shall yield True for the ISNUMBER 6.13.22 function. 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).

All Number subtypes shall yield True for the ISNUMBER function.

Note: This specification does not require that specific subtypes be distinguishable from each other, or that the subtype be tracked, but in practice most evaluators do such tracking. Automatically determining the most likely subtype is important for a good user interface and when generating OpenDocument format, since some subtypes (such as date, time, and currency) are stored in a different manner depending on their subtype. 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. Expression authors can then override this display format, so as a result the heuristics are not important for data exchange (and thus outside the scope of this specification).

4.3.2Time

Time is a subtype of Number.

Time is represented as a fraction of a day.

4.3.3Date

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: 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: It is implementation-defined if the year 1900 is treated as a leap year.

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

4.3.4DateTime

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

4.3.5Percentage

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.

4.3.6Currency

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.

4.3.7Logical (Number)

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

The implicit conversion operator “Convert to Logical” 6.3.12, when a Number is passed as a condition, 0 is considered False and all other numeric values are considered True.

Note: Logical values can be a distinct type from Number. 4.5

4.4Complex Number

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.

Functions and operators that accept complex numbers shall accept Text values as complex numbers (6.3.10 Conversion to Complex Number, as well as Numbers that are not complex numbers.

Note: IMSUM("3i";4) will produce the same result as COMPLEX(4;3).

Note: Expression authors should be aware that use of functions that are not defined by OpenFormula as accepting complex numbers as input may impair interoperability.

Equality can be tested using IMSUB to compute the difference, use IMABS to find the absolute difference, and then ensure the absolute difference is smaller than or equal to some nonnegative value (for exact equality, compare for equality with 0).

4.5Logical (Boolean)

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

Note: Logical values can be represented as a subtype of Number. 4.3.7

4.6Error

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.

4.7Empty Cell

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

4.8Reference

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.

4.9ReferenceList

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

4.10Array

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.

4.11Pseudotypes

4.11.1General

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.

4.11.2Scalar

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.

4.11.3DateParam

A DateParam is a value that is either a Number (interpreted as a serial number; 4.3.3) or Text; text is automatically converted to a date value. 6.3.15

4.11.4TimeParam

A TimeParam is a value that is either a Number (interpreted as a serial number; 4.3.2) or Text; text is automatically converted to a time value (fraction of a day). 6.3.16

4.11.5Integer

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.

4.11.6Basis

A basis is a subtype of Integer (and thus of Number) that indicates the calendar system conventions to be used.   If basis is omitted from a financial function, the default is basis 0.

Basis values are defined as follows (where x/y indicates x days per month and y days per year):

• ●.Basis 0 or omitted (30/360):  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference of days is 0.  Assumes that each month has 30 days and the total number of days in the year is 360 by making the following adjustments: 

  • ○.If both day-of-months are 31, they are changed to 30 

  • ○.Otherwise, if date1’s day-of-month is 31, it is changed to 30 

  • ○.Otherwise, if date1’s day-of-month is 30 and date2’s day-of-month is 31, date2’s day-of-month is changed to 30 (note that date2’s day-of-month will stay 31 if date1’s day < 30) 

  • ○.Otherwise, if both dates are the last day of February in their respective years, both day-of-month is changed to 30 

  • ○.Otherwise, if date1 is the last day of February, its day-of-month is changed to 30 

  Then computes the difference as
  (date2.year*360 + date2.month*30 + date2.day) - (date1.year*360 + date1.month*30 + date1.day). 

• ●.Basis 1 (Actual/actual):  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference in days is 0.  If date1 and date2 not “less than or equal to a year apart” (as defined below), then the days in the years between the dates is the average number of days in the years between date1 and date2, inclusive.  Otherwise, the days in the years between the dates is 365, except for these cases (where it is 366):  the dates are in the same year and it is a leap-year, a February 29 occurs between the two dates, or date2 is February 29.
  
  To determine if date1 and date2 are “less than or equal to a year apart” for purposes of this algorithm, one of these conditions much be true: 

  • ○.The two dates have the same year 

  • ○.Date2’s year is exactly one more than date1’s year, and ((date1.month > date2.month) or ((date1.month == date2.month) and (date1.day >= date2.day))) 

• ●.Basis 2 (Actual/360): Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference of days is 0.  Computes the actual difference in days, and presumes there are always 360 days per year. 

• ●.Basis 3 (Actual/365): Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference of days is 0.  Computes the actual difference in days, and presumes there are always 365 days per year. 

• ●.Basis 4 (30/360):  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference of days is 0.  Assumes that each month has 30 days and the total number of days in the year is 360; any day-of-month (in date1, date2, or both) with a value of 31 is changed to 30.  February dates are never changed, because there is no February 31. 

  Then computes the difference as
  (date2.year*360 + date2.month*30 + date2.day) - (date1.year*360 + date1.month*30 + date1.day). 

4.11.7Criterion

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 Number or Logical value. A matching cell content equals the Number or Logical value. 

• ●A value beginning with a comparator (<, <=, =, >, >=, <>). 6.4.9 

  For =, if the value is empty it matches empty cells. 4.7 

  For <>, if the value is empty it matches non-empty cells. 

  For <>, if the value is not empty it matches any cell content except the value, including empty cells. 

  Note: "=0" does not match empty cells. 

  For = and <>, if the value is not empty and can not be interpreted as a Number type or one of its subtypes and the calculation setting “search-criteria-must-apply-to-whole-cell” is true, comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria. For = and <>, if the value is not empty and can not be interpreted as a Number type or one of its subtypes 3.4 applies. 

• ●Other Text value. If calculation setting “search-criteria-must-apply-to-whole-cell” is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria. 

4.11.8Database

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.

Evaluators supporting databases and named ranges shall support the use of named ranges as a range, and the use of a Text value as a database (which, if it matches the name of a named range, will be considered the named range).

Note: It is considered good practice to define a named range for a database because it eases maintenance (the range can be changed for all functions by changing just one definition).  However, the use of named ranges for a database is not required by this specification.

4.11.9Field

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.

4.11.10Criteria

A criteria is a rectangular set of values, with at least one column and two rows, that selects matching records from a database. The first row lists fields against which expressions will be matched. 4.11.9 Rows after the first row contain fields with expressions for matching against database records.

For a record to be selected from a database, all of the expressions in a row of criteria shall match.

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

• ●Expressions are matched as per 4.11.7 Criterion. 

4.11.11Sequences (NumberSequence, NumberSequenceList, DateSequence, LogicalSequence, and ComplexSequence)

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.

When evaluating a function that accepts a sequence, the evaluator shall follow the rules for that sequence as defined in section 5.3. When processing a ReferenceList, the references are processed in order (first, second if any, and so on). In a cuboid, the first sheet is first processed, followed by later sheets (if any) in order. Inside a sheet, it is implementation-defined as to whether the values are processed row-at-a-time or column-at-a-time, but it must be one of these two processing orders. If processing row-at-a-time, the sequence must be produced by processing each row in turn, from smallest to largest column value (e.g., A1, B1, C1). If processing column-at-a-time, the sequence must be produced by processing each column at a time, from the smallest to the largest row value (e.g., A1, A2, A3).

5Expression Syntax

5.1General

The OpenFormula syntax is defined using the BNF notation of the XML specification, chapter 6 [XML1.0]. Each syntax rule is defined using "::=".

Note: Formulas are typically embedded inside an XML document. When this occurs, characters (such as "<", ">", '"', and "&") shall be escaped, as described in section 2.4 of the XML specification [XML1.0]. In particular, the less-than symbol "<" is typically represented as “&lt;”, the double-quote symbol as “&quot;”, and the ampersand symbol as “&amp;” (alternatively, a numeric character reference can be used).

5.2Basic Expressions

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 in BNF grammar, a formula is specified:

Formula ::= Intro? Expression

Intro ::= '=' ForceRecalc?

ForceRecalc ::= '='

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

Expression ::=

Whitespace* (

Number |

String |

Array |

PrefixOp Expression |

Expression PostfixOp |

Expression InfixOp Expression |

'(' Expression ')' |

FunctionName Whitespace* '(' ParameterList ')' |

Reference |

QuotedLabel |

AutomaticIntersection |

NamedExpression |

Error

) Whitespace*

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

5.3Constant Numbers

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

5.4Constant Strings

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] | '""')* '"'

5.5Operators

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

Table 1 - Operators defines the associativity and precedence of operators, from hightest to lowest precedence.

Table 1 - Operators

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

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

5.6Functions and Function Parameters

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

FunctionName ::= Identifier

Identifier ::= LetterXML (LetterXML | DigitXML |

'_' | '.' | CombiningCharXML)*

Where LetterXML, DigitXML, and CombiningCharXML are Letter, Digit, and CombiningChar as they are defined in [XML1.0].

Function names are case-insensitive.

Function calls shall be given a parameter list, though it may be empty. An empty list of parameters is considered a call with 0 parameters, not a call with one parameter that happens to be empty. TRUE() is syntactically a function call with 0 parameters. It is syntactically legitimate to provide empty parameters, though functions may not accept empty parameters unless otherwise noted:

ParameterList ::= /* empty */ |

Parameter (Separator EmptyOrParameter )* |

Separator EmptyOrParameter /* First param empty */

(Separator EmptyOrParameter )*

EmptyOrParameter ::= /* empty */ Whitespace* | Parameter

Parameter ::= Expression

Separator ::= ';'

5.7Nonstandard Function Names

When writing a document using function(s) not defined in this specification, an evaluator should include a prefix in such function names to identify the original definer of the function's semantics.

The prefix should begin with a domain name owned by the definer, in reverse order, and should be in uppercase letters (where lower/uppercase letters apply). This prefix should be the shortest prefix sufficient to identify the application company/project, followed by a period, optionally followed by version information or more specific product identification and a period, followed by the original function name itself. The version information should be included if an application substantially changes the semantics of a function (as viewed by users of that function) and one of those later versions of the function is intended. Evaluators may implement functions originally defined by another evaluator, and thus may read and/or write function names that use another evaluator's prefix.

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

5.8References

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

Reference ::= '[' Source? RangeAddress ']'

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

SubtableCell ::= ( Column Row ) | QuotedSheetName

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.

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 a RangeAddress does not contain a Column element or does not contain a Row element, it specifies a cell rectangle (4.8 Reference). If it contains Row elements, the cell rectangle starts on the first column and ends on the last column the evaluator supports. If it contains Column elements, the cell rectangle starts on the first row and ends on the last row the evaluator supports.

If in a RangeAddress the first part (left of ':' colon) contains a SheetLocator and the second part (right of ':' colon) does not contain a SheetLocator, the second part inherits the SheetLocator from the first part.

If a RangeAddress contains two different SheetLocators, it specifies a cell cuboid (4.8 Reference).

A reference with an explicit row or column value beyond the capabilities of an evaluator shall be computed as an Error, and not as a reference.

Note that references can include a single embedded “:” separator.  Evaluators should use references with embedded “:” separators inside the [..] markers, instead of the general-purpose “:” operator, when saving files, and where there is a choice of cells to join, and evaluators should choose the leftmost pair.

Source location's IRI is described in [RFC3987], Internationalized Resource Identifiers (IRIs), based on [RFC3986], Uniform Resource Identifier (URI): General Syntax. Evaluators should support absolute IRIs (URLs are IRIs too). Evaluators should support relative IRIs, which can be distinguished because they do not begin with [A-Za-z]+ ":". Relative IRIs are formed according to section 6.5 of RFC3987, respectively section 4.2 of RFC3986. Evaluators should always use a “./” prefix when writing a relative IRI, since this is unambiguous. Evaluators should support the file scheme (file:// prefix).

Evaluators may support a variety of IRI/URI/URL schemes (such as “http:”).

5.9Reference List

A reference list is the result of the Infix Operator Reference Concatenation 6.4.13 '~', the syntax is:

ReferenceList ::= Reference (Whitespace* ReferenceConcatenationOp Whitespace* Reference)*

A reference list can be passed as an argument to functions expecting a reference parameter where passing one reference results in an identical computation as an arbitrary sequence of single references occupying the identical cell range.  A reference list cannot be converted to an array.

5.10Quoted Label

5.10.1General

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.

5.10.2Lookup of Defined Labels

For a QuotedLabel, first the cells defined in column label ranges (cell ranges of the table:label-cell-range-address attribute in the elements <table:label-range> with attribute table:orientation 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 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.

5.10.3Automatic Lookup of Labels

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 quadratic 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 lable is assumed, else a column label is assumed.

5.10.4Implicit Intersection

For the reference resulting from a single QuotedLabel an implicit intersection is generated if the operator or function where it is used with 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.

5.10.5Automatic Range

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

Example:

Table 2 - Automatic Range

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

Table 3 - Automatic Range

5.10.6Automatic Intersection

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.

5.11Named Expressions

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.

Expressions should limit the names of their identifiers to only ([UNICODE]) letters, underscores, and digits, not including patterns that look like cell references or the words True or False.

Note: Some evaluators do not support the use of Unicode for identifiers.

Identifier ::= ( LetterXML

•   (LetterXML | DigitXML | '_' | CombiningCharXML)* ) 

  - ( [A-Za-z]+[0-9]+ ) 

  - ([Tt][Rr][Uu][Ee]) - ([Ff][Aa][Ll][Ss][Ee]) 

5.12Constant Errors

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.

Table 4 is a list of constant error names that are used by several existing implementations. Evaluators may implement other constant Error values.

Table 4 - Possible Other Constant Error Values

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.

5.13Inline Arrays

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.

5.14Whitespace

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.

6Standard Operators and Functions

6.1General

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

6.2Common Template for Functions and Operators

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

6.3Implicit Conversion Operators

6.3.1General

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.

6.3.2Conversion to Scalar

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

• ●Number, Logical, or Text, return the value. 

• ●reference to a single cell: obtain the value of the referenced cell, and return that value. 

• ●reference to more than one cell: do an implied intersection, 6.3.3, to determine which single cell to use, then handle as a reference to a single cell. 

6.3.3Implied intersection

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. 

6.3.4Force to array context (ForceArray)

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.

See also Non-Scalar Evaluation 3.3

6.3.5Conversion to Number

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. 

6.3.6Conversion to Integer

If the expected type is Integer for a function or operator, apply the “Conversion to Number” operation. 6.3.5 Then, if the result is a Number but not an integer, apply the specific conversion from Number to integer specified by that particular function/operator. If the function or operator does not specify any particular conversion operation, then the conversion from a non-integer Number into an integer is implementation-defined.

Many different conversions from a non-integer number into an integer are possible. The conversion direction may be towards negative infinity, towards positive infinity, towards zero, away from zero, towards the nearest even number, or towards the nearest odd number. A conversion can select the nearest integer, the nearest even or odd integer, or the “next” integer in the given direction if it is not already an integer. If a conversion selects the nearest integer, a direction is still needed (for when a value is halfway between two integers). In this specification, this conversion is referred to as “rounding” or “truncation”; these terms by themselves do not specify any specific operation.

If a function specifies its rounding operation using a series of capital letters, the function defined in this specification for that function is used to do the conversion to integer. Common such functions are:

• ●INT, which if given non-integer rounds down to the next integer towards negative infinity, regardless of whether or not it is the closest integer. 

• ●ROUND, which if given non-integer rounds to the nearest integer. If the input number is halfway between integers, it rounds away from zero. 

• ●TRUNC, which if given non-integer rounds towards zero, regardless of whether or not that integer is the closest integer. 

6.3.7Conversion to NumberSequence

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

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

• ●reference, create a sequence of numbers from the values of the referenced cells that only includes the values of type Number or Error. Thus, Empty cells and Text that could be converted into a value is not included in a 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. 

6.3.8Conversion to NumberSequenceList

Identical to Conversion to NumberSequence 6.3.7 , with the addition that instead of a Reference also a ReferenceList is accepted as argument. Each Reference in the list is converted to a NumberSequence in the order of occurrence.

6.3.9Conversion to DateSequence

Identical to Conversion to NumberSequence 6.3.7 except that each element in the list represents a serial date value of subtype Date.

6.3.10Conversion to Complex Number

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. 

6.3.11Conversion to ComplexSequence

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. 

6.3.12Conversion to Logical

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

6.3.13Conversion to LogicalSequence

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. 

6.3.14Conversion to Text

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. 

6.3.15Conversion to DateParam

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. 

6.3.16Conversion to TimeParam

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. 

6.4Standard Operators

6.4.1General

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

6.4.2Infix Operator "+"

Summary: Add two numbers.

Syntax: Number Left + Number Right

Returns: Number

Constraints: None

Semantics: Adds numbers together.

See also Infix Operator "-" 6.4.3, Prefix Operator "+" 6.4.15

6.4.3Infix Operator "-"

Summary: Subtract the second number from the first.

Syntax: Number Left - Number Right

Returns: Number

Constraints: None

Semantics: Subtracts one number from another number.

See also Infix Operator "+" 6.4.2, Prefix Operator "-" 6.4.16

6.4.4Infix Operator "*"

Summary: Multiply two numbers.

Syntax: Number Left * Number Right

Returns: Number

Constraints: None

Semantics: Multiplies numbers together.

See also Infix Operator "+" 6.4.2, Infix Operator "/" 6.4.5

6.4.5Infix Operator "/"

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.

See also Infix Operator "-" 6.4.3, Infix Operator "*" 6.4.4

6.4.6Infix Operator "^"

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

See also Infix Operator "*" 6.4.4, POWER 6.16.46

6.4.7Infix Operator "="

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.

See also Infix Operator "<>" 6.4.8

6.4.8Infix Operator "<>"

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 “≠”.

See also Infix Operator "=" 6.4.7

6.4.9Infix Operator Ordered Comparison ("<", "<=", ">", ">=")

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.

See also Infix Operator "<>" 6.4.8, Infix Operator "=" 6.4.7

6.4.10Infix Operator "&"

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

See also Infix Operator "+" 6.4.2, CONCATENATE 6.20.6

6.4.11Infix Operator Reference Range (":")

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.

See also Infix Operator Reference Union 6.4.13, Infix Operator Reference Intersection 6.4.12, INDIRECT 6.14.7

6.4.12Infix Operator Reference Intersection ("!")

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: 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: Intersection is notated as "!" in OpenFormula format, but as a space character in some user interfaces.

See also Infix Operator Reference Union 6.4.13

6.4.13Infix Operator Reference Concatenation ("~") (aka Union)

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:

See also Infix Operator Reference Range 6.4.11, Infix Operator Reference Intersection 6.4.12

6.4.14Postfix Operator "%"

Summary: Divide the operand by 100

Syntax: Number Left %

Returns: Number

Constraints: None

Semantics: Computes Left / 100.

See also Prefix Operator "-" 6.4.16, Prefix Operator "+" 6.4.15

6.4.15Prefix Operator "+"

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.

See also Infix Operator "+" 6.4.2

6.4.16Prefix Operator "-"

Summary: Negate its one argument.

Syntax: - Number Right

Returns: Number

Constraints: None

Semantics: Computes 0 - Right.

See also Infix Operator "-" 6.4.3

6.5Matrix Functions

6.5.1General

Matrix functions operate on matrices.

A matrix with M columns and N rows is defined by

 

The dimension subscript may be omitted, if the context allows it, i.e.

. Matrices are represented by upper case letters. The elements of a matrix are denoted by the corresponding lower case letter and a subscript, which defines the column and the row.
Square matrices have the same amount of columns and rows, i.e.

.

6.5.2MDETERM

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 and -1 for an odd amount.
Matrices with a non-zero determinant are invertible.

See also MINVERSE 6.5.3

6.5.3MINVERSE

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.

See also MDETERM 6.5.2

6.5.4MMULT

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

, are defined by:

 

6.5.5MUNIT

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.

 

6.5.6TRANSPOSE

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.

 

6.6Bit operation functions

6.6.1General

Evaluators shall support unsigned integer values and results of at least 48 bits (values from 0 to 2^48-1 inclusive).

6.6.2BITAND

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.

6.6.3BITLSHIFT

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 

6.6.4BITOR

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.

6.6.5BITRSHIFT

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) 

See also BITAND 6.6.2, BITXOR 6.6.6, BITLSHIFT 6.6.3

6.6.6BITXOR

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.

See also BITAND 6.6.2, BITOR 6.6.4, OR 6.15.8

6.7Byte-position text functions

6.7.1General

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.

6.7.2FINDB

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.

See also FIND 6.20.9 , LEFTB 6.7.3 , RIGHTB 6.7.7

6.7.3LEFTB

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.

See also LEFT 6.20.12, RIGHT 6.20.19, RIGHTB 6.7.7

6.7.4LENB

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.

See also LEN 6.20.13, LEFTB 6.7.3, RIGHTB 6.7.7

6.7.5MIDB

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.

See also MID 6.20.15, LEFTB 6.7.3, RIGHTB 6.7.7, REPLACEB 6.7.6

6.7.6REPLACEB

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.

See also REPLACE 6.20.17, LEFTB 6.7.3, RIGHTB 6.7.7, MIDB 6.7.5, SUBSTITUTE 6.20.21

6.7.7RIGHTB

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.

See also RIGHT 6.20.19, LEFTB 6.7.3

6.7.8SEARCHB

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.

See also SEARCH 6.20.20, EXACT 6.20.8, FIND 6.20.9, FINDB 6.7.2

6.8Complex Number Functions

6.8.1General

Functions for complex numbers.

6.8.2COMPLEX

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.

6.8.3IMABS

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.

6.8.4IMAGINARY

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.

See also IMREAL 6.8.18

6.8.5IMARGUMENT

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.

See also IMABS 6.8.3

6.8.6IMCONJUGATE

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.

6.8.7IMCOS

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.

See also IMSIN 6.8.19

6.8.8IMCOT

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

See also IMTAN 6.8.19

6.8.9IMCSC

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

See also IMSIN 6.8.19

6.8.10IMCSCH

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

6.8.11IMDIV

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.

See also IMDIV 6.8.11

6.8.12IMEXP

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

 

.

See also IMLN 6.8.13

6.8.13IMLN

Summary: Returns the natural logarithm of a complex number.

Syntax: IMLN( Complex X )

Returns: Complex

Constraints: None

Semantics: If X=r(cos φ + isin φ) , φ is expressed by radians, then the natural logarithm is returned.

See also IMEXP 6.8.12 , IMLOG10 6.8.14

6.8.14IMLOG10

Summary: Returns the common logarithm of a complex number.

Syntax: IMLOG10( Complex X )

Returns: Complex

Constraints: None

Semantics: If X=r(cos φ + isin φ) , φ is expressed by radians, then the common logarithm is returned.

See also IMLN 6.8.13 , IMPOWER 6.8.16

6.8.15IMLOG2

Summary: Returns the binary logarithm of a complex number.

Syntax: IMLOG2( Complex X )

Returns: Complex

Constraints: None

Semantics: If X=r(cos φ + isin φ) , φ is expressed by radians, then the binary logarithm is returned.

See also IMLN 6.8.13 , IMPOWER 6.8.16

6.8.16IMPOWER

Summary: Returns the power of N and a complex number.

Syntax: IMPOWER( Complex X ; Number n )

Returns: Complex

Constraints: None

Semantics: If X=a+bi=r(cos φ + isin φ) , the result is

 

.

See also IMEXP 6.8.12

6.8.17IMPRODUCT

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

See also IMDIV 6.8.11

6.8.18IMREAL

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.

See also IMAGINARY 6.8.4

6.8.19IMSIN

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.

See also IMCOS 6.8.7

6.8.20IMSEC

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

See also IMCOS 6.8.7

6.8.21IMSECH

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

6.8.22IMSQRT

Summary: Returns the square root of a complex number

Syntax: IMSQRT( Complex N )

Returns: Complex

Constraints: None

Semantics: If N=r(cosφ + isinφ), φ is expressed by radians, then the square root of N is returned.

See also IMPOWER 6.8.16

6.8.23IMSUB

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.

See also IMSUM 6.8.24

6.8.24IMSUM

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.

See also IMSUB 6.8.23

6.8.25IMTAN

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

See also IMSIN, IMCOS, IMCOT 6.8.23

6.9Database Functions

6.9.1General

Database functions use the variables, Database 4.11.8, Field 4.11.9, and Criteria 4.11.10.

The results of database functions may change when the values of the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS properties change. 3.4

6.9.2DAVERAGE