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.oasisopen.org/office/v1.2/cd05/OpenDocumentv1.2cd05part2.odt (Authoritative)
http://docs.oasisopen.org/office/v1.2/cd05/OpenDocumentv1.2cd05part2.pdf
http://docs.oasisopen.org/office/v1.2/cd05/OpenDocumentv1.2cd05part2.html
Previous Version:
http://docs.oasisopen.org/office/v1.1/OS/OpenDocumentv1.1.odt
http://docs.oasisopen.org/office/v1.1/OS/OpenDocumentv1.1.pdf
http://docs.oasisopen.org/office/v1.1/OS/OpenDocumentv1.1html/OpenDocumentv1.1.html
Latest Version:
http://docs.oasisopen.org/office/v1.2/OpenDocumentv1.2part2.odt
http://docs.oasisopen.org/office/v1.2/OpenDocumentv1.2part2.pdf
http://docs.oasisopen.org/office/v1.2/OpenDocumentv1.2part2.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.oasisopen.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.oasisopen.org/committees/office/ipr.php.
The nonnormative errata page for this specification is located at www.oasisopen.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.oasisopen.org/who/trademark.php for above guidance.
Table of Contents
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, easytoread infix text notation is desired for exchanging recalculated formulas.
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].
OpenFormula defines:
1.data types
2.syntax
3.semantics
for recalculated formulas.
OpenFormula also defines functions.
OpenFormula does not define:
1.a user interface
2.a general notation for mathematical expressions
[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.oasisopen.org/office/v1.1/OS/OpenDocumentv1.1.pdf.
[RFC3986] T. BernersLee, 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 9781936213009). (http://www.unicode.org/versions/Unicode5.2.0/).
[XML1.0] Tim Bray, Jean Paoli, C. M. SperbergMcQueen, Eve Maler, François Yergeau , Extensible Markup Language (XML) 1.0 (Fourth Edition), http://www.w3.org/TR/2006/RECxml20060816/, W3C, 2006.
[JISX0201] JIS X 0201 (1976) to Unicode 1.1 Table, 1994, http://www.unicode.org/Public/MAPPINGS/OBSOLETE/EASTASIA/JIS/JIS0201.TXT. 
[JISX0208] JIS X 0208 (1990) to Unicode, 1994, http://www.unicode.org/Public/MAPPINGS/OBSOLETE/EASTASIA/JIS/JIS0208.TXT. 
[UAX11] Asmus Freytag, East Asian Width, Unicode Standard Annex #11, http://www.unicode.org/reports/tr11/tr1119.html, 2009. 
An OpenDocument formula expression shall adhere to the expression syntax defined in chapter 4. It may use subsets or supersets of OpenFormula.
An OpenDocument Formula Evaluator is a program that can parse and recalculate OpenDocument formula expressions, and that meets the following additional requirements:
A)It may implement subsets or supersets of this specification.
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
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.
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 nonstandard extension.
Note: An expression may reference a nonstandard function by name, or depend on implementationdefined 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 &). 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 “AZ” is the range U+0041 through U+005A inclusive.
An OpenDocument Formula Small Group Evaluator is an OpenDocument Formula Evaluator that meets the following additional requirements:
A)It shall implement at least the limits defined in the “Basic Limits” section.
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.
C)It shall implement all implicit conversions for the types it implements, at least Text, Conversion to Number, Reference, Conversion to Logical, and Error.
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 (":").
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
F)It need not evaluate references that contain more than one area.
G)It need not implement inline arrays, complex numbers, and the reference union operator.
H)For expressions embedded in an OpenDocument document, it shall consider the values of the following hostdefined properties: HOSTCASESENSITIVE, HOSTPRECISIONASSHOWN, HOSTSEARCHCRITERIAMUSTAPPLYTOWHOLECELL, HOSTAUTOMATICFINDLABELS, HOSTUSEREGULAREXPRESSIONS, HOSTUSEWILDCARDS, HOSTNULLYEAR, HOSTNULLDATE.
I)It shall support international characters for named expression identifiers.
Note: This specification does not mandate a user interface for international characters, so a resourceconstrained application may choose to not show the traditional glyph (e.g., it may show the [UNICODE] numeric code instead).
An OpenDocument Formula Medium Group Evaluator is an OpenDocument Small Group Formula Evaluator that meets the following additional requirements:
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
B)It shall implement the Infix Operator Reference Union ("~") 6.4.13
C)It shall evaluate references with more than one area.
An OpenDocument Formula Large Group Evaluator is an OpenDocument Medium Group Formula Evaluator that meets the following additional requirements:
A)It shall implement the syntax defined in these sections on syntax: Inline Arrays; Automatic Intersection; External Named Expressions.
B)It shall implement the complex number type as discussed in the section on Complex Number, array formulas, and Sheetlocal 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
This section describes the basic formula processing model: how expressions are calculated, when recalculation occurs, and limits on formulas.
1.If an expression is a constant number or string, that constant is returned
Any formula computes a single result, though that single result may actually be a set of values.
Nonscalar values passed as arguments to functions are evaluated by intersection or iteration.
1)Evaluation as an implicit intersection of the argument with the expression's evaluation position.
2.2)Calculations with nonscalar inputs are a generalization of (2.1).
4)HOSTAUTOMATICFINDLABELS: if true, row and column labels are automatically found.
8)HOSTNULLDATE: Defines the beginning of the epoch; a numeric date of 0 equals this date.
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 reexecute 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.
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 integeronly arithmetic.
In practice, applications tend to use at least one IEEE 7541985 binary floatingpoint representation, using at least the 64bit 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 8541987 (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.
Evaluators which claim to support “basic limits” shall support at least the following limits:
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.at least 30 parameters per function when the function prototype permits a list of parameters.
3.permit strings of ASCII characters of up to 32,767 (2^151) characters.
4.support at least 7 nesting levels of functions.
All values defined by OpenFormula have a type. OpenFormula defines Text, Number, Complex Number, Logical, Error, Reference, ReferenceList and Array types.
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.
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 64bit IEEE floating point values and use the CPU's floatingpoint 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, fixedpoint 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).
Time is represented as a fraction of a day.
Date is represented by an integer value.
Note: It is implementationdefined if the year 1900 is treated as a leap year.
DateTime is a subtype of Number. It is a Date plus Time.
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
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).
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
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.
Cell positions in a cell cuboid/rectangle/strip can resolve to empty cells (section 3.7).
A reference list cannot be converted to an array.
Many functions require a type or a set of types with special properties, and/or process them specially. For example, a "Database" requires headers that are the field names. These specialized types are called pseudotypes.
A 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
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
Basis values are defined as follows (where x/y indicates x days per month and y days per year):
A reference to an empty cell is interpreted as the numeric value 0.
●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 nonempty 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 “searchcriteriamustapplytowholecell” 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 “searchcriteriamustapplytowholecell” is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria.
Evaluators should match the database field name case insensitive.
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.
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 implementationdefined as to whether the values are processed rowatatime or columnatatime, but it must be one of these two processing orders. If processing rowatatime, the sequence must be produced by processing each row in turn, from smallest to largest column value (e.g., A1, B1, C1). If processing columnatatime, 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).
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 lessthan symbol "<" is typically represented as “<”, the doublequote symbol as “"”, and the ampersand symbol as “&” (alternatively, a numeric character reference can be used).
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 ::= "'" ([^']  "''")+ "'"
'.' [09]+ ([eE] [+]? [09]+)?
StandardNumber ::= [09]+ ('.' [09]+)? ([eE] [+]? [09]+)?
String ::= '"' ([^"#x00]  '""')* '"'
Operators are functions with one or more parameters.
InfixOp ::= ArithmeticOp  ComparisonOp  StringOp  ReferenceOp
ArithmeticOp ::= '+'  ''  '*'  '/'  '^'
ComparisonOp ::= '='  '<>'  '<'  '>'  '<='  '>='
ReferenceOp ::= IntersectionOp  ReferenceConcatenationOp  RangeOp
ReferenceConcatenationOp ::= '~'
Table 1  Operators defines the associativity and precedence of operators, from hightest to lowest precedence.
Table 1  Operators
Associativity 
Operator(s) 
Comments 
left 
: 
Range. 
left 
! 
Reference intersection ([.A1:.C4]![.B1:.B5] is [.B1:.B4]). Displayed as the space character in some implementations. 
left 
~ 
Reference union. Displayed as the function parameter separator in some implementations. 
right 
+, 
Prefix unary operators, e.g., 5 or [.A1]. Note that these have a different precedence than add and subtract. 
left 
% 
Postfix unary operator % (divide by 100). Note that this is legal with expressions (e.g., [.B1]%), it can be duplicated (1%%), and it does not change the meaning of other operations such as "+". 
left 
^ 
Power (2 ^ 3 is 8). 
left 
*,/ 
Multiply, divide. 
left 
+, 
Binary operations add, subtract. Note that unary (prefix) + and  have a different precedence. 
left 
& 
Binary operation string concatenation. Note that unary (prefix) + and  has a different precedence. Note that "&" shall be escaped when included in an XML document 
left 
=, <>, <, <=, 
Comparison operators equal to, not equal to, less than, less than or equal to, greater than, greater than or equal to 
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 caseinsensitive.
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 ::= ';'
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 evaluatorunique functions beginning with a toplevel domain name followed by a period. Evaluators should avoid defining application functions beginning with “NET.”, “COM.”, “ORG.”, or a country code followed by a period.
Evaluators that do not support a function should compute its result as some Error value other than NA().
References refer to a specific cell or set of cells. The syntax for a constant reference:
Reference ::= '[' Source? RangeAddress ']'
SheetLocatorOrEmpty ::= SheetLocator  /* empty */
SheetLocator ::= SheetName ('.' SubtableCell)*
SheetName ::= QuotedSheetName  '$'? [^\]\. #$']+
QuotedSheetName ::= '$'? SingleQuoted  Error
SubtableCell ::= ( Column Row )  QuotedSheetName
CellAddress ::= SheetLocatorOrEmpty '.' Column Row /* Not used directly */
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 generalpurpose “:” 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 [AZaz]+ ":". 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:”).
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.
A quoted label is Text contained in a table as cell content, either literally or as a formula result.
QuotedLabel ::= SingleQuoted
A quoted label identifies a column or a row, depending on the label range in which its text appears.
For a QuotedLabel, first the cells defined in column label ranges (cell ranges of the table:labelcellrangeaddress attribute in the elements <table:labelrange> 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:datacellrangeaddress 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.
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 (HOSTAUTOMATICFINDLABELS 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 columnwise 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.If Match1 has the smallest distance, that match is taken.
2.Else, Match2 (right and/or below) has the smallest or an equal distance:
2.1 A match to the upper left (FormulaColumn >= Match1Column && FormulaRow >= Match1Row) takes precedence over matches to other directions.
2.2 Else, if there is no match to the upper left:
2.2.1 If Match1 is somewhere right of the formula cell (FormulaColumn < Match1Column) it was the first match found in columnwise lookup.
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.1.2 Else Match2 is below the formula cell and Match1 is taken.
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.
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.
When passed as a nonscalar 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
Row 
Data 
Expression 
Result 
Comment  

=SUM('Label') 
3 
Blank cell in row 2 is skipped (two blank cells in row 2 and 3 would not and stop), blank cell in row 5 stops the automatic range. 
If any cell content is entered in row 5 the range is regenerated as follows:
Row 
Data 
Expression 
Result 
Comment  

=SUM('Label') 
15 
Blank cell in row 2 is skipped, blank cell in row 7 stops the automatic range. 
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.
NamedExpression ::= SimpleNamedExpression 
SheetLocalNamedExpression  ExternalNamedExpression
SimpleNamedExpression ::= Identifier 
'$$' (Identifier  SingleQuoted)
SheetLocalNamedExpression ::=
QuotedSheetName '.' SimpleNamedExpression
ExternalNamedExpression ::=
Source '#' (SimpleNamedExpression  SheetLocalNamedExpression)
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)* )
 ( [AZaz]+[09]+ )
 ([Tt][Rr][Uu][Ee])  ([Ff][Aa][Ll][Ss][Ee])
Inline Error constants shall have the following syntax:
Error ::= '#' [AZ09]+ ([!?]  ('/' ([AZ]  ([09] [!?]))))
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
Name 
Comments 
#DIV/0! 
Attempt to divide by zero, including division by an empty cell. ERROR.TYPE of 2 
#NAME? 
Unrecognized/deleted name. ERROR.TYPE of 5. 
#N/A 
Not available. ISNA() applied to this value will return True. Lookup functions which failed, and NA(), return this value. ERROR.TYPE of 7. 
#NULL! 
Intersection of ranges produced zero cells. ERROR.TYPE of 1. 
#NUM! 
Failed to meet domain constraints (e.g., input was too large or too small). ERROR.TYPE of 6. 
#REF! 
Reference to invalid cell (e.g., beyond the application’s abilities). ERROR.TYPE of 4. 
#VALUE! 
Parameter is wrong type. ERROR.TYPE of 3. 
Array ::= '{' MatrixRow ( RowSeparator MatrixRow )* '}'
MatrixRow ::= Expression ( ';' Expression )*
Whitespace ::= #x20  #x09  #x0a  #x0d
OpenFormula defines commonly used operators and functions.
Function names ignore case. Evaluators should write function names in all uppercase letters when writing OpenFormula formulas.
Unless otherwise noted, if any value being provided is an Error, the result is an Error; if more than one Error is provided, one of them is returned (evaluators should return the leftmost Error result).
For every function or operator, the following are defined in this specification:
●Summary: One sentence briefly describing the function or operator.
●Returns: Return type (e.g., Number, Text, Logical, Reference).
●Semantics: This text describes what the function/operator does.
The implicit conversion operators omit many of these items, e.g., the syntax (since there is none).
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 passedin type does not match the expected type, an attempt is made to automatically convert the value to the expected type. An Error is returned if the type cannot be converted (this can never happen if the expected type is Any). Unless otherwise noted, any conversion operation applied to a value of type Error returns the same value.
To convert to a scalar, if the value is of type:
●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.
●Intersect this with the provided reference to multiple cells
●If a single cell is referenced; return it; otherwise, return an Error.
See also NonScalar Evaluation 3.3
If the expected type is Number, then if value is of type:
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 noninteger Number into an integer is implementationdefined.
Many different conversions from a noninteger 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 noninteger rounds down to the next integer towards negative infinity, regardless of whether or not it is the closest integer.
●ROUND, which if given noninteger rounds to the nearest integer. If the input number is halfway between integers, it rounds away from zero.
●TRUNC, which if given noninteger rounds towards zero, regardless of whether or not that integer is the closest integer.
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.
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.
Identical to Conversion to NumberSequence 6.3.7 except that each element in the list represents a serial date value of subtype Date.
An evaluator may accept complex numbers as Text, Number, or a different distinguishable type.
If the expected type is ComplexSequence, then if value is of type:
●Number, Text, or Logical, handle as Conversion to Complex Number (creating a sequence of length 1).
●Reference, create a sequence of complex numbers from the values of the referenced cells that only includes the values of type Number, Text, and Error. Empty cells are not included in a complex number sequence. If the Logical type is a distinguished type from the Number type, it should not be included in the sequence of numbers; if the Logical type is not a distinguished type, then such values will (of course) be included in the number sequence.
If the expected type is Logical, then if value is of type:
If the expected type is LogicalSequence, then if value is of type:
●Number or Logical, handle as Conversion to Logical (creating a sequence of length 1).
●Reference, create a sequence of logical values from the values of the referenced cells that only includes the values of type Logical and Error. If the Logical type is not a distinguished type, then include values of type Number, converting each to a Logical value as described in Conversion to Logical. Empty cells are not included in a LogicalSequence.
If the expected type is Text, then if value is of type:
If the expected type is the pseudotype DateParam, then if value is of type:
●Logical, the result is implementationdefined, either a Number or Error
●Reference: perform conversion to scalar, then perform as above. If the cell is empty, return 0.
If the expected type is the pseudotype TimeParam, then if value is of type:
●Logical, the result is implementationdefined, either a Number or Error
●Reference: perform conversion to scalar, then perform as above. If the cell is empty, return 0.
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.
Syntax: Number Left + Number Right
Semantics: Adds numbers together.
See also Infix Operator "" 6.4.3, Prefix Operator "+" 6.4.15
Summary: Subtract the second number from the first.
Syntax: Number Left  Number Right
Semantics: Subtracts one number from another number.
See also Infix Operator "+" 6.4.2, Prefix Operator "" 6.4.16
Summary: Multiply two numbers.
Syntax: Number Left * Number Right
Semantics: Multiplies numbers together.
See also Infix Operator "+" 6.4.2, Infix Operator "/" 6.4.5
Summary: Divide the second number into the first.
Syntax: Number Left / Number Right
Semantics: Divides numbers. Dividing by zero returns an Error.
See also Infix Operator "" 6.4.3, Infix Operator "*" 6.4.4
Summary: Exponentiation (Power).
Syntax: Number Left ^ Number Right
Semantics: Returns POWER(Left, Right).
See also Infix Operator "*" 6.4.4, POWER 6.16.46
Summary: Report if two values are equal
Syntax: Scalar Left = Scalar Right
See also Infix Operator "<>" 6.4.8
Summary: Report if two values are not equal
Note: In some user interfaces the infix operator “<>” is displayed (or accepted) as “!=” or “≠”.
See also Infix Operator "=" 6.4.7
Summary: Report if two values have the given order
Syntax: Scalar Left op Scalar Right
where op is one of: "<", "<=", ">", or ">="
See also Infix Operator "<>" 6.4.8, Infix Operator "=" 6.4.7
Summary: Concatenate two strings.
Syntax: Text Left & Text Right
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
Summary: Computes an inclusive range given two references
Syntax: Reference Left : Reference Right
See also Infix Operator Reference Union 6.4.13, Infix Operator Reference Intersection 6.4.12, INDIRECT 6.14.7
Summary: Compute the intersection of two references
Syntax: Reference Left ! Reference Right
If Left or Right are not of type Reference or ReferenceList, an Error shall be returned.
See also Infix Operator Reference Union 6.4.13
Summary: Concatenate two references
Syntax: Reference Left ~ Reference Right
If Left or Right are not of type Reference or ReferenceList, an Error shall be returned.
See also Infix Operator Reference Range 6.4.11, Infix Operator Reference Intersection 6.4.12
Summary: Divide the operand by 100
Semantics: Computes Left / 100.
See also Prefix Operator "" 6.4.16, Prefix Operator "+" 6.4.15
Summary: No operation; returns its one argument.
See also Infix Operator "+" 6.4.2
Summary: Negate its one argument.
Semantics: Computes 0  Right.
See also Infix Operator "" 6.4.3
Matrix functions operate on matrices.
A matrix with M columns and N rows is defined by
Summary: Calculates the determinant of a matrix.
Syntax: MDETERM( ForceArray Array matrix )
Constraints: Only square matrices are allowed.
Semantics: Returns the determinant of the matrix. The determinant is defined by
Summary: Returns the inverse of a matrix.
Syntax: MINVERSE( ForceArray Array matrix )
Constraints: Only square matrices are allowed.
Summary: Multiplies the matrices A and B.
Syntax: MMULT( ForceArray Array A ; ForceArray Array B )
Constraints: COLUMNS(A)=ROWS(B)
Summary: Creates a unit matrix of a specified dimension N.
Constraints: The dimension has to be greater than zero.
Semantics: Creates the unit matrix (identity matrix) of dimension N.
Summary: Returns the transpose of a matrix.
Evaluators shall support unsigned integer values and results of at least 48 bits (values from 0 to 2^481 inclusive).
Summary: Returns bitwise “and” of its parameters
Syntax: BITAND( Integer X ; Integer Y )
Summary: Returns left shift of value x by n bits (“<<”)
Syntax: BITLSHIFT( Integer x ; Integer n )
Semantics: Returns left shift of value x by n bit positions:
Summary: Returns bitwise “or” of its parameters
Syntax: BITOR( Integer X ; Integer Y )
Summary: Returns right shift of value x by n bits (“>>”)
Syntax: BITRSHIFT( Integer x ; Integer n )
Semantics: Returns right shift of value x by n bit positions:
See also BITAND 6.6.2, BITXOR 6.6.6, BITLSHIFT 6.6.3
Summary: Returns bitwise “exclusive or” of its parameters
Syntax: BITXOR( Integer X ; Integer Y )
See also BITAND 6.6.2, BITOR 6.6.4, OR 6.15.8
Byteposition 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 implementationdependent and reliance upon them reduces interoperability.
The pseudotypes ByteLength and BytePosition are Integers, but their exact meanings and values are not further defined by this specification.
Summary: Returns the starting position of a given text, using byte positions.
Syntax: FINDB( Text Search ; Text T [ ; BytePosition Start ] )
Semantics: The same as FIND, but using byte positions.
See also FIND 6.20.9 , LEFTB 6.7.3 , RIGHTB 6.7.7
Summary: Returns a selected number of text characters from the left, using a byte position.
Syntax: LEFTB( Text T [ ; ByteLength Length ] )
Semantics: As LEFT, but using a byte position.
See also LEFT 6.20.12, RIGHT 6.20.19, RIGHTB 6.7.7
Summary: Returns the length of given text in units compatible with byte positions
Semantics: As LEN, but compatible with byte position values.
See also LEN 6.20.13, LEFTB 6.7.3, RIGHTB 6.7.7
Syntax: MIDB( Text T ; BytePosition Start ; ByteLength Length )
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
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 )
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
Summary: Returns a selected number of text characters from the right, using byte position.
Syntax: RIGHTB( Text T [ ; ByteLength Length ] )
Semantics: As RIGHT, but using byte positions.
See also RIGHT 6.20.19, LEFTB 6.7.3
Summary: Returns the starting position of a given text, using byte positions.
Syntax: SEARCHB( Text Search ; Text T [ ; BytePosition Start ] )
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
Functions for complex numbers.
Summary: Creates a complex number from a given real coefficient and imaginary coefficient.
Syntax: COMPLEX( Number Real ; Number Imaginary [ ; Text Suffix ] )
Summary: Returns the absolute value of a complex number
Summary: Returns the imaginary coefficient of a complex number
Syntax: IMAGINARY( Complex X )
Semantics: If X=a+bi or X=a+bj, then the imaginary coefficient is b.
Summary: Returns the complex argument of a complex number
Syntax: IMARGUMENT( Complex X )
Summary: Returns the complex conjugate of a complex number
Syntax: IMCONJUGATE( Complex X )
Semantics: If X=a+bi, then the complex conjugate is abi.
Summary: Returns the cosine of a complex number
Semantics: If X=a+bi, then cos(X)=cos(a)cosh(b)sin(a)sinh(b)i.
Summary: Returns the cotangent of a complex number
Semantics: Equivalent to the following (except N is computed only once):
Summary: Returns the cosecant of a complex number
Semantics: Equivalent to the following:
Summary: Returns the hyperbolic cosecant of a complex number
Semantics: Computes the hyperbolic cosecant. This is equivalent to:
Summary: Divides the second number into the first.
Syntax: IMDIV( Complex X ; Complex Y )
Semantics: Given X=a+bi and Y=c+di, return the quotient
Division by zero returns an Error.
Summary: Returns the exponent of e and a complex number.
Summary: Returns the natural logarithm of a complex number.
See also IMEXP 6.8.12 , IMLOG10 6.8.14
Summary: Returns the common logarithm of a complex number.
See also IMLN 6.8.13 , IMPOWER 6.8.16
Summary: Returns the binary logarithm of a complex number.
See also IMLN 6.8.13 , IMPOWER 6.8.16
Summary: Returns the power of N and a complex number.
Syntax: IMPOWER( Complex X ; Number n )
Summary: Returns the product of complex numbers.
Syntax: IMPRODUCT( { ComplexSequence N }+ )
Summary: Returns the real coefficient of a complex number
Semantics: If N=a+bi or N=a+bj, then the real coefficient is a.
Summary: Returns the sine of a complex number
Semantics: If N=a+bi, then sin(N)=sin(a)cosh(b)cos(a)sinh(b)i.
Summary: Returns the secant of a complex number
Semantics: Equivalent to the following:
Summary: Returns the hyperbolic secant of a complex number
Semantics: Computes the hyperbolic secant. This is equivalent to:
Summary: Returns the square root of a complex number
Semantics: If N=r(cosφ + isinφ), φ is expressed by radians, then the square root of N is returned.
Summary: Subtracts the second complex number from the first.
Syntax: IMSUB( Complex X ; Complex Y )
Semantics: Subtract complex number Y from X.
Summary: Sums (add) a set of complex numbers, including all numbers in ranges
Syntax: IMSUM( { ComplexSequence N }+ )
Summary: Returns the tangent of a complex number
Semantics: Equivalent to the following (except N is computed only once):
See also IMSIN, IMCOS, IMCOT 6.8.23
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 HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties change. 3.4
Syntax: DAVERAGE( Database D ; Field F ; Criteria C )
Semantics: Perform AVERAGE on data records in database D field F that match criteria C.
See also AVERAGE 6.18.3, COUNT 6.13.6, DSUM 6.9.11, DCOUNT 6.9.3, SUM 6.16.61
Syntax: DCOUNT( Database D ; Field F ; Criteria C )
Semantics: Perform COUNT on data records in database D field F that match criteria C.
See also COUNT 6.13.6, COUNTA 6.13.7, DCOUNTA 6.9.4, DSUM 6.9.11
Syntax: DCOUNTA( Database D ; Field F ; Criteria C )
Semantics: Perform COUNTA on data records in database D field F that match criteria C.
See also COUNT 6.13.6, COUNTA 6.13.7, DCOUNT 6.9.3, DSUM 6.9.11
Syntax: DGET( Database D ; Field F ; Criteria C )
See also DMAX 6.9.6, DMIN 6.9.7, DSUM 6.9.11
Syntax: DMAX( Database D ; Field F ; Criteria C )
Semantics: Perform MAX on only the data records in database D field F that match criteria C.
See also MAX 6.18.45, DMIN 6.9.7, MIN 6.18.48
Syntax: DMIN( Database D ; Field F ; Criteria C )
Semantics: Perform MIN on only the data records in database D field F that match criteria C.
See also MIN 6.18.48, DMAX 6.9.6, MAX 6.18.45
Syntax: DPRODUCT( Database D ; Field F ; Criteria C )
Semantics: Multiply together only the data records in database D field F that match criteria C.
See also SUM 6.16.61, DSUM 6.9.11
Syntax: DSTDEV( Database D ; Field F ; Criteria C )
Semantics: Perform STDEV on only the data records in database D field F that match criteria C.
See also STDEV 6.18.72, DSTDEVP 6.9.10
Syntax: DSTDEVP( Database D ; Field F ; Criteria C )
Semantics: Perform STDEVP on only the data records in database D field F that match criteria C.
See also STDEVP 6.18.74, DSTDEV 6.9.9
Syntax: DSUM( Database D ; Field F ; Criteria C )
Semantics: Perform SUM on only the data records in database D field F that match criteria C.
See also SUM 6.16.61, DMIN 6.9.7, DMAX 6.9.6
Syntax: DVAR( Database D ; Field F ; Criteria C )
Semantics: Perform VAR on only the data records in database D field F that match criteria C.
See also VAR 6.18.82, DVARP 6.9.13
Syntax: DVARP( Database D ; Field F ; Criteria C )
Semantics: Perform VARP on only the data records in database D field F that match criteria C.
See also VARP 6.18.84, DVAR 6.9.12
Summary: Constructs a date from year, month, and day of month.
Syntax: DATE( Integer Year ; Integer Month ; Integer Day )
See also TIME 6.10.17, DATEVALUE 6.10.4
Summary: Returns the difference in years, months, or days of two date numbers.
Syntax: DATEDIF( DateParam StartDate ; DateParam EndDate ; Text Format )
Semantics: Compute difference of StartDate and EndDate, in the units given by Format.
format 
Returns the number of 
y 
Years 
m 
Months. If there is not a complete month between the dates, 0 will be returned. 
d 
Days 
md 
Days, ignoring months and years 
ym 
Months, ignoring years 
yd 
Days, ignoring years 
See also DAYS360 6.10.7, DAYS 6.10.6, Infix Operator “” 6.4.3
Summary: Returns the date serial number from given text.
See also TIME 6.10.17, DATE 6.10.2, TIMEVALUE 6.10.18, VALUE 6.13.34
Summary: Returns the day from a date.
Semantics: Returns the day portion of a date.
See also MONTH 6.10.13, YEAR 6.10.23
Summary: Returns the number of days between two dates
Syntax: DAYS( DateParam EndDate ; DateParam StartDate )
See also DATEDIF 6.10.3, DAYS360 6.10.7, MONTH 6.10.13, YEAR 6.10.23, Infix Operator “” 6.4.3
Summary: Returns the number of days between two dates using the 360day year
Syntax: DAYS360( DateParam StartDate ; DateParam EndDate [ ; Integer Method = 0 ] )
The US/NASD Method (30US/360):
3.Otherwise, if StartDate's dayofmonth is the last day of February, it is changed to 30.
Note: This calculation is slightly different from Basis 0 (4.11.6 Basis). Dates are never swapped.
The European Method (30E/360):
1.Truncate date values, set sign=1.
2.If StartDate is after EndDate then swap dates and set sign=1.
3.If StartDate's dayofmonth is 31, it is changed to 30.
4.If EndDate's dayofmonth is 31, it is changed to 30.
Note: Days in February are never changed.
Note: This calculation is identical to Basis 4 (4.11.6 Basis)
For both methods the value then returned is
sign * ((EndDate.year*360 + EndDate.month*30 + EndDate.day)  (StartDate.year*360 + StartDate.month*30 + StartDate.day))
See also DAYS 6.10.6, DATEDIF 6.10.3
Summary: Returns the serial number of a given date when MonthAdd months is added
Syntax: EDATE( DateParam StartDate ; Number MonthAdd )
See also DAYS 6.10.6, DATEDIF 6.10.3, EOMONTH 6.10.9
Summary: Returns the serial number of the end of a month, given date plus MonthAdd months
Syntax: EOMONTH( DateParam StartDate ; Integer MonthAdd )
Summary: Extracts the hour (0 through 23) from a time.
Semantics: Extract from T the hour value, 0 through 23, as per a 24hour clock. This is equal to:
See also MONTH 6.10.13, DAY 6.10.5, MINUTE 6.10.12, SECOND 6.10.16
Summary: Determines the ISO week number of the year for a given date.
Syntax: ISOWEEKNUM( DateParam Date [ ; Integer Mode = 2 ] )
Mode determines the starting day of the week:
Mode 
Week starts on 
1 
Sunday 
2 
Monday 
See also DAY 6.10.5, MONTH 6.10.13, YEAR 6.10.23, WEEKDAY 6.10.20, WEEKNUM 6.10.21
Summary: Extracts the minute (0 through 59) from a time.
Semantics: Extract from T the minute value, 0 through 59, as per a clock. This is equal to:
HourFraction=(DayFraction*24INT(DayFraction*24))
See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, SECOND 6.10.16
Summary: Extracts the month from a date.
Syntax: MONTH( DateParam Date )
Semantics: Takes a date and returns the month portion.
See also YEAR 6.10.23, DAY 6.10.5
Summary: Returns the whole number of work days between two dates.
Semantics: Returns the whole number of days between two dates, ignoring weekends.
Summary: Returns the serial number of the current date and time.
Semantics: This returns the current day and time serial number, using the current locale. If you want only the serial number of the current day, use TODAY 6.10.19.
See also DATE 6.10.2, TIME 6.10.17, TODAY 6.10.19
HourFraction=(DayFraction*24INT(DayFraction*24))
MinuteFraction=(HourFraction*60INT(HourFraction*60))
Second=ROUND(MinuteFraction*60)
See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, MINUTE 6.10.12
Summary: Constructs a time value from hours, minutes, and seconds.
Syntax: TIME( Number hours ; Number minutes ; Number seconds )
Semantics: Returns the fraction of the day consumed by the given time, i.e.:
((hours*60*60)+(minutes*60)+seconds)/(24*60*60)
Time is a subtype of number, where a time value of 1 = 1 day = 24 hours.
Summary: Returns a time serial number from given text.
See also TIME 6.10.17, DATE 6.10.2, DATEVALUE 6.10.4, VALUE 6.13.34
Summary: Returns the serial number of today.
Semantics: This returns the current day's serial number, using current locale. This only returns the date, not the datetime value. For the specific time of day as well, use NOW 6.10.15.
See also TIME 6.10.17, NOW 6.10.15
Syntax: WEEKDAY( DateParam Date [ ; Integer Type = 1 ] )
1.When Type is 1, Sunday is the first day of the week, with value 1; Saturday has value 7.
2.When Type is 2, Monday is the first day of the week, with value 1; Sunday has value 7.
3.When Type is 3, Monday is the first day of the week, with value 0; Sunday has value 6.
Day of Week 
Type=1 Result 
Type=2 Result 
Type=3 Result 
Sunday 
1 
7 
6 
Monday 
2 
1 
0 
Tuesday 
3 
2 
1 
Wednesday 
4 
3 
2 
Thursday 
5 
4 
3 
Friday 
6 
5 
4 
Saturday 
7 
6 
5 
See also DAY 6.10.5, MONTH 6.10.13, YEAR 6.10.23
Summary: Determines the week number of the year for a given date.
Syntax: WEEKNUM( DateParam Date [ ; Number Mode = 1 ] )
Constraints: 1 ≤ Mode ≤ 2, or 11 ≤ Mode ≤ 17, or Mode = 21, or Mode = 150
Semantics: Returns the number of the week in the year for the given date.
See also DAY 6.10.5, MONTH 6.10.13, YEAR 6.10.23, WEEKDAY 6.10.20, ISOWEEKNUM 6.10.11
Summary: Extracts the year from a date given in the current locale of the evaluator.
See also MONTH 6.10.13, DAY 6.10.5, VALUE 6.13.34
Summary: Extracts the number of years (including fractional part) between two dates
Syntax: YEARFRAC( DateParam StartDate ; DateParam EndDate [ ; Basis Basis = 0 ] )
Note: The Basis default is not the actual number of days in a month or year. 4.11.6
See also DATEDIF 6.10.3
OpenFormula defines two functions, DDE and HYPERLINK, for accessing external data.
Summary: Returns data from a DDE request
Syntax: DDE( Text server ; Text topic ; Text item [ ; Integer Mode = 0 ] )
Mode is an optional parameter that determines how the results are returned:
Mode 
Effect 
0 or missing 
Data converted to number using VALUE in the number style's locale of the default table cell style 
1 
Data converted to number using VALUE in the EnglishUS (en_US) locale 
2 
Data retrieved as text (not converted to number) 
Summary: Returns a Text or Number result and adds a string to the IRI part of a hyperlink cell.
Syntax: HYPERLINK( Text IRI [ ; TextNumber FunctionResult ] )
The financial functions are defined for use in financial calculations.
An annuity is a recurring series of payments. A "simple annuity" is one where equal payments are made at equal intervals, and the compounding of interest occurs at those same intervals. The time between payments is called the "payment interval". Where payments are made at the end of the payment interval, it is called an "ordinary annuity". Where payments are made at the beginning of the payment interval, it is called an "annuity due". Periods are numbered starting at 1.
Summary: Calculates the accrued interest for securities with periodic interest payments.
Constraints: issue < first < settlement ; coupon > 0; par > 0
frequency is one of the following values:
frequency 
Frequency of coupon payments 
1 
Annual 
2 
Semiannual 
4 
Quarterly 
12 
Monthly 
issue The security's issue or dated date.
first The security's first interest date.
settlement The security's settlement date.
coupon The security's annual coupon rate.
par The security's par value, that is, the principal to be paid at maturity.
frequency The number of coupon payments per year.
basis The type of daycount basis to use; see section 4.11.6
calc_methodA logical value that specifies how to treat the case where settlement>first.
See also ACCRINTM 6.12.3
Summary: Calculates the accrued interest for securities that pay at maturity.
Constraints: coupon > 0; par > 0
Semantics: Calculates the accrued interest for securities that pay at maturity.
issue The security's issue or dated date.
settlement The security's maturity date.
coupon The security's annual coupon rate.
par The security's par value, that is, the principal to be paid at maturity.
basis The type of daycount basis to use; see section 4.11.6
See also ACCRINT 6.12.2
Constraints: cost > 0; purchaseDate <= firstPeriodEndDate; salvage >= 0; period >= 0; rate > 0
cost The value of the asset at the date of aquisition.
purchaseDate The date of aquisition.
firstPeriodEndDateThe end date of the first depreciation period.
salvage The value of the asset at the end of the depreciation life time.
period The period of interest.
rate The rate of depreciation.
basis The type of daycount basis to use; see section 4.11.6
Table 10  AMORDEGRC
Asset life time ( ) 
Depreciation factor ( ) 

1.0 

1.5 

2.0 

2.5 
See also AMORLINC 6.12.5 , DB 6.12.14, DDB 6.12.15, YEARFRAC 6.10.24
Constraints: cost > 0; purchaseDate <= firstPeriodEndDate; salvage >= 0; period >= 0; rate > 0
cost The value of the asset at the date of aquisition.
purchaseDate The date of aquisition.
firstPeriodEndDate The end date of the first depreciation period.
salvage The value of the asset at the end of the depreciation life time.
period Which period the depreciation should be calculated for.
rate The rate of depreciation.
basis Indicates the day count basis to use; see section 4.11.6
When period = 0:
For full periods, where period > 0, the depreciation is cost * rate
For the last period, possibly a partial period, the depreciation = costsalvageaccumulateddepreciation, where accumulateddepreciation is the sum of the depreciation in period 0 plus any full period depreciations.
When period > depreciated life of the asset, i.e., when period > (costsalvage)/(cost*rate) then the depreciation is 0.
Note: The behavior of this function is implementationdefined in cases where purchaseDate = firstPeriodEndDate.
See also AMORDEGRC 6.12.4, DB 6.12.14, DDB 6.12.15, YEARFRAC 6.10.24
Constraints: settlement < maturity
frequency is one of the following values:
frequency 
Frequency of coupon payments 
1 
Annual 
2 
Semiannual 
4 
Quarterly 
settlementThe settlement date.
maturityThe maturity date.
frequencyThe number of coupon payments per year.
basisThe type of daycount basis; see section 4.11.6
See also COUPDAYS 6.12.7 , COUPDAYSNC 6.12.8 , COUPNCD 6.12.8 , COUPNUM 6.12.10 , COUPPCD 6.12.11
Summary: Calculates the number of days in a coupon period that contains a settlement date.
Constraints: settlement < maturity
frequency is one of the following values:
frequency 
Frequency of coupon payments 
1 
Annual 
2 
Semiannual 
4 
Quarterly 
Semantics: Calculates the number of days in the coupon period containing the settlement date.
settlementThe settlement date.
maturityThe maturity date.
frequencyThe number of coupon payments per year.
basisThe type of daycount basis; see section 4.11.6
See also COUPDAYBS 6.12.6 , COUPDAYSNC 6.12.8 , COUPNCD 6.12.8 , COUPNUM 6.12.10 , COUPPCD 6.12.11
Summary: Calculates the number of days between a settlement date and the next coupon date.
Constraints: settlement < maturity
frequency is one of the following values:
frequency 
Frequency of coupon payments 
1 
Annual 
2 
Semiannual 
4 
Quarterly 
Semantics: Calculates the number of days between the settlement date and the next coupon date.
settlementThe settlement date.
maturityThe maturity date.
frequencyThe number of coupon payments per year.
basisThe type of daycount basis; see section 4.11.6
See also COUPDAYBS 6.12.6 , COUPDAYS 6.12.7 , COUPNCD 6.12.8 , COUPNUM 6.12.10 , COUPPCD 6.12.11
Summary: Calculates the next coupon date following a settlement.
Syntax: COUPNCD( DateParam settlement ; DateParam maturity ; Integer frequency [ ; Basis basis = 0 ] )
Returns: Date
Constraints: settlement < maturity
frequency is the number of coupon payments per year. frequency is one of the following values:
Table 14  COUPNCD
frequency 
Frequency of coupon payments 
1 
Annual 
2 
Semiannual 
4 
Quarterly 
Summary: Calculates the number of outstanding coupons between settlement and maturity dates.
frequency 
Frequency of coupon payments 
1 
Annual 
2 
Semiannual 
4 
Quarterly 
See also COUPDAYBS 6.12.6, COUPDAYS 6.12.7, COUPDAYSNC 6.12.8, COUPNCD 6.12.8, COUPPCD 6.12.11
Summary: Calculates the next coupon date prior a settlement.
Constraints: settlement < maturity
frequency is the number of coupon payments per year. frequency is one of the following values:
frequency 
Frequency of coupon payments 
1 
Annual 
2 
Semiannual 
4 
Quarterly 
See also COUPDAYBS 6.12.6, COUPDAYS 6.12.7, COUPDAYSNC 6.12.8, COUPNCD 6.12.8, COUPNUM 6.12.10
Summary: Calculates a cumulative interest payment.
Constraints: rate > 0; value > 0; 1 <= start <= end <= periods
type is one of the following values:
type 
Maturity date 
0 
due at the end 
1 
due at the beginning 
Semantics: Calculates the cumulative interest payment.
See also IPMT 6.12.24, CUMPRINC 6.12.13
Summary: Calculates a cumulative principal payment.
Constraints: type is one of the following values:
type 
Maturity date 
0 
due at the end 
1 
due at the beginning 
Semantics: Calculates the cumulative principal payment.
See also PPMT 6.12.38 , CUMIPMT 6.12.12
Summary: Compute the depreciation allowance of an asset.
Constraints: cost > 0, salvage >= 0, lifetime >0; period > 0; 0 < month < 13
The rate is calculated as follows:
For the first period the residual value is
For all periods, where period <= lifeTime, the residual value is calculated by
If month was specified, the residual value for the period after lifeTime becomes
The depreciation allowance for the first period is
For all other periods the allowance is calculated by
For all periods, where period > lifeTime + 1 – INT(month/12), the depreciation allowance is zero.
See also DDB 6.12.15, SLN 6.12.46
Summary: Compute the amount of depreciation at a given period of time.
●lifeTime: the number of periods that the depreciation will occur over.
●period: the period for which a depreciation value is specified.
The depreciation each period is calculated as
To allow also noninteger period values this algorithm may be used:
See also SLN 6.12.46, VDB 6.12.51
Summary: Returns the discount rate of a security.
Constraints: settlement < maturity
Semantics: Calculates the discount rate of a security.
settlementThe settlement date of the security.
maturityThe maturity date.
priceThe price of the security.
redemptionThe redemption value of the security.
basisThe daycount basis; see section 4.11.6
See also YEARFRAC 6.10.24
Summary: Converts a fractional dollar representation into a decimal representation.
Syntax: DOLLARDE( Number fractional ; Integer denominator )
Semantics: Converts a fractional dollar representation into a decimal representation.
fractional Decimal fraction.
denominator Denominator of the fraction.
See also DOLLARFR 6.12.18 , TRUNC 6.17.9
Summary: Converts a decimal dollar representation into a fractional representation.
Syntax: DOLLARFR( Number decimal ; Integer denominator )
Semantics: Converts a decimal dollar representation into a fractional representation.
decimal Decimal number.
denominator Denominator of the fraction.
See also DOLLARDE 6.12.17, TRUNC 6.17.9
Summary: Returns the Macaulay duration of a fixed interest security in years
Constraints: Yield >=0, Coupon >= 0, Settlement <= Maturity; Frequency = 1, 2, 4
Semantics: Computes the Macaulay duration, given:
See also MDURATION 6.12.27
Summary: Returns the net annual interest rate for a nominal interest rate.
Syntax: EFFECT( Number rate ; Integer payments )
Constraints: rate >= 0; payments > 0
rateThe interest rate per period.
paymentsThe number of payments per period.
Summary: Compute the future value (FV) of an investment.
Semantics: Computes the future value of an investment. The parameters are:
See PV 6.12.42 for the equation this solves.
See also PV 6.12.42, NPER 6.12.30, PMT 6.12.37, RATE 6.12.43
Summary: Returns the accumulated value given starting capital and a series of interest rates.
Syntax: FVSCHEDULE( Number Principal ; NumberSequence Schedule )
See also PV 6.12.42, NPER 6.12.30, PMT 6.12.37, RATE 6.12.43
Summary: Computes the interest rate of a fully vested security.
Constraints: Settlement < Maturity
Settlement: the date of purchase of the security.
Maturity: the date on which the security is sold.
Investment: the purchase price.
Redemption: the selling price.
Basis: [optional] indicates the day count basis to use; see section 4.11.6.
The return value for this function is:
See also RECEIVED 6.12.44, YEARFRAC 6.10.24
Summary: Returns the amount of an annuity payment going towards interest.
Rate: The periodic interest rate.
Period: The period for which the interest payment is computed.
Nper: The total number of periods for which the payments are made
PV: The present value (e.g. The initial loan amount).
FV: The future value (optional) at the end of the periods. Zero if omitted.
See also PPMT 6.12.38, PMT 6.12.37
Summary: Compute the internal rate of return for a series of cash flows.
Syntax: IRR( NumberSequence Values [ ; Number Guess = 0.1 ] )
Semantics: Compute the internal rate of return for a series of cash flows.
The result of IRR is the rate at which the NPV() function will return zero with the given values.
See also NPV 6.12.31, RATE 6.12.43
Summary: Compute the interest payment of an amortized loan for a given period.
Syntax: ISPMT( Number Rate ; Number Period ; Number Nper ; Number Pv )
See also PV 6.12.42, FV 6.12.21, NPER 6.12.30, PMT 6.12.37, RATE 6.12.43
Summary: Returns the modified Macaulay duration of a fixed interest security in years
Constraints: Yield >= 0, Coupon >= 0, Settlement <= Maturity; Frequency = 1, 2, 4
Semantics: Computes the modified Macaulay duration, given:
The modified duration is computed as follows:
See also DURATION 6.12.19
Summary: Returns the modified internal rate of return (IRR) of a series of periodic investments
Syntax: MIRR( Array Values ; Number Investment ; Number ReinvestRate )
Constraints: Values shall contain at least one positive value and at least one negative value.
Computes the modified internal rate of return, which is:
where N is the number of incomes and payments in Values (total).
Summary: Compute the annual nominal interest rate.
Syntax: NOMINAL( Number EffectiveRate ; Integer CompoundingPeriods )
Constraints: EffectiveRate >0 , CompoundingPeriods > 0
The mapping between nominal rate and effective rate is
Summary: Compute the number of payment periods for an investment.
Syntax: NPER( Number Rate ; Number Payment ; Number Pv [ ; [ Number Fv ] [ ; Number PayType ] ] )
Semantics: Computes the number of payment periods for an investment. The parameters are:
If Rate is 0, then NPER solves this equation:
If Rate is nonzero, then NPER solves this equation:
See also FV 6.12.21, RATE 6.12.43, PMT 6.12.37, PV 6.12.42
Summary: Compute the net present value (NPV) for a series of periodic cash flows.
Syntax: NPV( Number Rate ; { NumberSequenceList Value }+ )
If n is the number of values in the Values, the formula for NPV is:
See also FV 6.12.21, IRR 6.12.25, NPER 6.12.30, PMT 6.12.37, PV 6.12.42, XNPV 6.12.53
Constraints: Rate, Yield, and Redemption should be greater than 0.
●Redemption: the redemption value per 100 currency units face value
●Frequency: the number of interest payments per year. 1=annual; 2=halfyearly; 4=quarterly.
●Basis: the type which indicates how the year is to be calculated by days; see section 4.11.6.
See also ODDLPRICE 6.12.34 , ODDFYIELD 6.12.33
●Redemption: the redemption value per 100 currency units face value
●Frequency: the number of interest payments per year. 1=annual; 2=halfyearly; 4=quarterly.
●Basis: the type which indicates how the year is to be calculated by days; see section 4.11.6
See also ODDLYIELD 6.12.35 , ODDFPRICE 6.12.32
●Redemption: the redemption value per 100 currency units face value
●Frequency: the number of interest payments per year. 1=annual; 2=halfyearly; 4=quarterly
●Basis: the type which indicates how the year is to be calculated by days; see section 4.11.6
See also ODDFPRICE 6.12.32
Summary: Compute the yield of a security which has an irregular last interest date.
Constraints: Rate, Price, and Redemption should be greater than 0.
●Redemption: the redemption value per 100 currency units face value
●Frequency: the number of interest payments per year. 1=annual; 2=halfyearly; 4=quarterly.
●Basis: the type which indicates how the year is to be calculated by days; see section 4.11.6.
See also ODDLPRICE 6.12.34 , ODDFYIELD 6.12.33
Summary: Returns the number of periods required by an investment to realize a specified value.
Syntax: PDURATION( Number rate ; Number currentValue ; Number specifiedValue )
Constraints: rate > 0; currentValue > 0; specifiedValue > 0
Summary: Compute the payment made each period for an investment.
Semantics: Computes the payment made each period for an investment. The parameters are:
If Rate is 0, the following equation is solved:
If Rate is nonzero, then PMT solves this equation:
See also FV 6.12.21, NPER 6.12.30, PV 6.12.42, RATE 6.12.43
Constraints: Rate and Present should be greater than 0. 0<Period <nPer.
Constraints: Rate, AnnualYield, and Redemption should be greater than 0; Frequency = 1, 2 or 4.
●AnnualYield: a measure of the annual yield of a security (compounded at each interest payment)
●Redemption: the redemption value per 100 currency units face value
●Frequency: the number of interest payments per year. 1=annual; 2=halfyearly; 4=quarterly.
See also PRICEDISC 6.12.40, PRICEMAT 6.12.41
Summary: Calculate the price of a security with a discount per 100 currency units of face value.
Constraints: Discount and Redemption should be greater than 0.
●Redemption: the redemption value per 100 currency units face value
●Basis: the type which indicates how the year is to be calculated by days; see section 4.11.6.
See also PRICE 6.12.39, PRICEMAT 6.12.41, YIELDDISC 6.12.55
Constraints: Settlement < Maturity, Rate >= 0, AnnualYield >= 0
If both, Rate and AnnualYield, are 0, the return value is 100.
See also PRICEDISC 6.12.40, PRICEMAT 6.12.41
Summary: Compute the present value (PV) of an investment.
Semantics: Computes the present value of an investment. The parameters are:
If Rate is nonzero, then PV solves this equation:
See also FV 6.12.21, NPER 6.12.30, PMT 6.12.37, RATE 6.12.43
Summary: Compute the interest rate per period of an investment.
Constraints: If Nper is 0 or less than 0, the result is an Error.
Semantics: Computes the interest rate of an investment. The parameters are:
See also FV 6.12.21, NPER 6.12.30, PMT 6.12.37, PV 6.12.42
Summary: Calculates the amount received at maturity for a zero coupon bond.
Constraints: Investment and Discount should be greater than 0.
The return value is:
See also YEARFRAC 6.10.24
Summary: Returns an equivalent interest rate when an investment increases in value.
Syntax: RRI( Number N ; Number Pv ; Number Fv )
See also FV 6.12.21, NPER 6.12.30, PMT 6.12.37, PV 6.12.42, RATE 6.12.43
Syntax: DDB( Number Cost ; Number Salvage ; Number LifeTime )
●Salvage: the salvage value at the end of the LifeTime (often 0)
●LifeTime: the number of periods that the depreciation will occur over. A positive integer.
For alternative methods to compute depreciation, see DDB 6.12.15.
See also DDB 6.12.15
Syntax: SYD( Number Cost ; Number Salvage ; Number LifeTime ; Number Period )
●Salvage: the salvage value at the end of the LifeTime (often 0)
●LifeTime: the number of periods that the depreciation will occur over. A positive integer.
●Period: the period for which the depreciation value is specified.
For other methods of computing depreciation, see DDB 6.12.15.
See also SLN 6.12.46, DDB 6.12.15
Summary: Compute the bondequivalent yield for a treasury bill.
Syntax: TBILLEQ( DateParam Settlement ; DateParam Maturity ; Number Discount )
Semantics: The parameters are defined as,
where DSM is the number of days between settlement and maturity computed according to the 360 days per year basis (basis 2, 4.11.6)
See also TBILLPRICE 6.12.49, TBILLYIELD 6.12.50
Summary: Compute the price per 100 face value for a treasury bill.
Syntax: TBILLPRICE( DateParam Settlement ; DateParam Maturity ; Number Discount )
Semantics: The parameters are:
See also TBILLEQ 6.12.48, TBILLYIELD 6.12.50
Summary: Compute the yield for a treasury bill.
Syntax: TBILLYIELD( DateParam Settlement ; DateParam Maturity ; Number Price )
Semantics: The parameters are:
See also TBILLEQ 6.12.48, TBILLPRICE 6.12.49
Semantics: cost is the amount paid for the asset. cost can be any value greater than salvage.
salvage is the value of the asset at the end of its life. salvage can be any value.
See also DDB 6.12.15, SLN 6.12.46
Summary: Compute the internal rate of return for a nonperiodic series of cash flows.
Syntax: XIRR( NumberSequence Values ; DateSequence Dates [ ; Number Guess = 0.1 ] )
Summary: Compute the net present value of a series of cash flows.
Syntax: XNPV( Number Rate ; ReferenceArray Values ; Reference Array Dates )
Number of elements in Values equals number of elements in Dates.
All elements of Values are of type Number.
All elements of Dates are of type Number.
All elements of Dates >= Dates[1]
With N being the number of elements in Values and Dates each, the formula is:
Summary: Calculate the yield of a bond.
Constraints: Rate, Price, and Redemption should be greater than 0.
●Price: the price of the bond per 100 currency units face value
●Redemption: the redemption value of the bond per 100 currency units face value
●Frequency: the number of interest payments per year. 1=annual; 2=halfyearly; 4=quarterly
See also PRICE 6.12.39, YIELDDISC 6.12.55, YIELDMAT 6.12.56
Summary: Calculate the yield of a discounted security per 100 currency units of face value.
Constraints: Price and Redemption should be greater than 0.
●Price: the price of the security per 100 currency units face value
●Redemption: the redemption value per 100 currency units face value
●Basis: the day count basis for this calculation; see section 4.11.6
The return value is
See also PRICEDISC 6.12.40, YEARFRAC 6.10.24
Summary: Calculate the yield of the security that pays interest on the maturity date.
Constraints: Rate and Price should be greater than 0.
●Price: the price of the security per 100 currency units face value
●Basis: the type which indicates how the year is to be calculated by days; see section 4.11.6
See also PRICE 6.12.39, YIELD 6.12.54, YIELDDISC 6.12.55
Information functions provide information about a data value, the spreadsheet, or underlying environment, including special functions for converting between data types.
Summary: Returns the number of areas in a given reference
Syntax: AREAS( ReferenceList R )
Semantics: Returns the number of areas in the reference list.
See also Infix Operator Reference Concatenation 6.4.13, INDEX 6.14.6
Summary: Returns information about position, formatting or contents in a reference.
Syntax: CELL( Text Info_Type [ ; Reference R ] )
Returns: Information about position, formatting properties or content
Info_Type 
Comment 
COL 
Returns the column number of the cell. 
ROW 
Returns the row number of the cell. 
SHEET 
Returns the sheet number of the cell. 
ADDRESS 
Returns the absolute address of the cell. The sheet name is included if given in the reference. For an external reference a Source as specified in the syntax rules for References 5.8 is included. 
FILENAME 
Returns the file name of the file that contains the cell as an IRI. If the file is newly created and has not yet been saved, the file name is empty text “”. 
CONTENTS 
Returns the contents of the cell, without formatting properties. 
COLOR 
Returns 1 if color formatting is set for negative value in this cell; otherwise returns 0 
FORMAT 
Returns a text string which shows the number format of the cell. ,(comma) = number with thousands separator F = number without thousands separator C = currency format S = exponential representation P = percentage To indicate the number of decimal places after the decimal separator, a number is given right after the above characters. D1 = MMMDYY, MMDYY and similar formats D2 = DDMM D3 = MMYY D4 = DDMMYYYY HH:MM:SS D5 = MMDD D6 = HH:MM:SS AM/PM D7 = HH:MM AM/PM D8 = HH:MM:SS D9 = HH:MM G = All other formats  (Minus) at the end = negative numbers in the cell have color setting () (brackets) at the end = this cell has the format settings with parentheses for positive or all values 
TYPE 
Returns the text value corresponding to the type of content in the cell: “b” : blank or empty cell content “l” : label or text cell content “v” : number value cell content 
WIDTH 
Returns the column width of the cell. The unit is the width of one zero (0) character in default font size. 
PROTECT 
Returns the protection status of the cell: 1 = cell is protected 0 = cell is unprotected 
PARENTHESES 
Returns 1 if the cell has the format settings with parentheses for positive or all values, otherwise returns 0 
PREFIX 
Returns single character text strings corresponding to the alignment of the cell. “'” (APOSTROPHE, U+0027) = left alignment '"' (QUOTATION MARK, U+0022) = right alignment ^(caret) = centered alignment \(back slash) = filled alignment otherwise, returns empty string "". 
Summary: Returns the column number(s) of a reference
Syntax: COLUMN( [ Reference R ] )
See also ROW 6.13.29, SHEET 6.13.31
Summary: Returns the number of columns in a given range
Syntax: COLUMNS( ReferenceArray R )
Summary: Count the number of Numbers provided
Syntax: COUNT( { NumberSequenceList N }+ )
Constraints: One or more parameters.
Summary: Count the number of nonempty values
See also COUNT 6.13.6, ISBLANK 6.13.14
Summary: Count the number of blank values
Syntax: COUNTBLANK( ReferenceList R )
See also COUNT 6.13.6, COUNTA 6.13.7, COUNTIF 6.13.9, ISBLANK 6.13.14
Summary: Count the number of cells in a range that meet a criteria.
Syntax: COUNTIF( ReferenceList R ; Criterion C )
Constraints: Does not accept constant values as the reference parameter.
Semantics: Counts the number of cells in the reference range R that meet the Criterion C (4.11.7).
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also COUNT 6.13.6, COUNTA 6.13.7, COUNTBLANK 6.13.8, COUNTIFS 6.13.10, SUMIF 6.16.62, Infix Operator "=" 6.4.7, Infix Operator "<>" 6.4.8, Infix Operator Ordered Comparison ("<", "<=", ">", ">=") 6.4.9
Summary: Count the number of cells that meet multiple criteria in multiple ranges.
Syntax: COUNTIFS( Reference R1 ; Criterion C1 [ ; Reference R2 ; Criterion C2 ]... )
Constraints: Does not accept constant values as the reference parameter.
Semantics: Counts the number of cells that meet the Criterion C1 in the reference range R1 and the Criterion C2 in the reference range R2, and so on (4.11.7). All reference ranges shall have the same dimension and size, else an Error is returned. A logical AND is applied between each array result of each selection; an entry is counted only if the same position in each array is the result of a Criterion match.
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also COUNT 6.13.6, COUNTA 6.13.7, COUNTBLANK 6.13.8, COUNTIF 6.13.9, SUMIF 6.16.62, Infix Operator "=" 6.4.7, Infix Operator "<>" 6.4.8, Infix Operator Ordered Comparison ("<", "<=", ">", ">=") 6.4.9
Summary: Returns Number representing the specific Error type.
Summary: Returns formula at given reference as text
Syntax: FORMULA( Reference X )
Constraints: Reference X shall contain a formula
Summary: Returns information about the environment
Constraints: Category shall be valid
Semantics: Returns information about the environment in the given category.
Evaluators shall support at least the following categories:
Category 
Meaning 
Type 
"directory" 
Current directory. This shall be formatted so file names can be appended to the result (e.g., on POSIX and Windows systems it shall end with the separator “/” or “\” respectively). 
Text 
"memavail" 
Amount of memory “available”, in bytes. On many modern (virtual memory) systems this value is not really available, but a system should return 0 if it is known that there is no more memory available, and greater than 0 otherwise 
Number 
"memused" 
Amount of memory used, in bytes, by the data 
Number 
"numfile" 
Number of active worksheets in files 
Number 
"osversion" 
Operating system version 
Text 
"origin" 
The top leftmost visible cell's absolute reference prefixed with “$A:”. In locales where cells are ordered righttoleft, the top rightmost visible cell is used instead. 
Text 
"recalc" 
Current recalculation mode. If the locale is English, this is either "Automatic" or "Manual" (the exact text depends on the locale) 
Text 
"release" 
The version of the implementation. 
Text 
"system" 
The type of the operating system. 
Text 
"totmem" 
Total memory available in bytes, including the memory already used. 
Number 
Evaluators may support other categories.
Summary: Return TRUE if the referenced cell is blank, else return FALSE
See also ISNUMBER 6.13.22, ISTEXT 6.13.25
Summary: Return True if the parameter has type Error and is not NA, else return False.
Semantics: If X is of type Error, and ISNA(X) is not true, returns TRUE. Otherwise it returns FALSE. Note that this function returns False if given NA(); if this is not desired, use ISERROR 6.13.16. Note that this function does not propagate Error values.
ISERR(X) is the same as:
IF(ISNA(X),FALSE(),ISERROR(X))
See also ERROR.TYPE 6.13.11, ISERROR 6.13.16, ISNUMBER 6.13.22, ISTEXT 6.13.25, NA 6.13.27
Summary: Return TRUE if the parameter has type Error, else return FALSE
Semantics: If X is of type Error, returns TRUE, else returns FALSE. Note that this function returns True if given NA(); if this is not desired, use ISERR 6.13.15. Note that this function does not propagate Error values.
See also ERROR.TYPE 6.13.11, ISERR 6.13.15, ISNA 6.13.20, ISNUMBER 6.13.22, ISTEXT 6.13.25, NA 6.13.27
Summary: Return TRUE if the value is even, else return FALSE
Semantics: First, compute X1=TRUNC(X). Then, if X1 is even (a division by 2 has a remainder of 0), return True, else return False. The result is implementationdefined if given a Logical value; an evaluator may return either an Error or the result of converting the Logical value to a Number (per Conversion to Number 6.3.5 ).
See also ISODD 6.13.23
Summary: Return TRUE if the reference refers to a formula, else return FALSE
Syntax: ISFORMULA( Reference X )
See also ISTEXT 6.13.25, ISNUMBER 6.13.22
Summary: Return TRUE if the parameter has type Logical, else return FALSE
See also ISTEXT 6.13.25, ISNUMBER 6.13.22
Summary: Return True if the parameter is of type NA, else return False.
See also ERROR.TYPE 6.13.11, ISERROR 6.13.16, ISERR 6.13.15, ISNUMBER 6.13.22, ISTEXT 6.13.25, NA 6.13.27
Summary: Return TRUE if the parameter does not have type Text, else return FALSE
Semantics: If X is of type Text, returns FALSE, else TRUE. If X is a reference, examines what X references. References to empty cells are NOT considered Text, so a reference to an empty cell will return TRUE. Empty Cell 4.7
ISNONTEXT(X) is equivalent to NOT(ISTEXT(X))
See also ISNUMBER 6.13.22, ISLOGICAL 6.13.19, ISTEXT 6.13.25
Summary: Return TRUE if the parameter has type Number, else return FALSE
See also ISTEXT 6.13.25, ISLOGICAL 6.13.19
Summary: Return TRUE if the value is even, else return FALSE
Semantics: First, compute X1=TRUNC(X). Then, if X1 is odd (a division by 2 has a remainder of 1), return True, else return False. The result is implementationdefined if given a Logical value; an evaluator may return either an Error or the result of converting the Logical value to a Number (per Conversion to Number 6.3.5 ).
See also ISEVEN 6.13.17
Summary: Return True if the parameter is of type reference, else return False.
See also ISNUMBER 6.13.22, ISTEXT 6.13.25
Summary: Return TRUE if the parameter has type Text, else return FALSE. If X is a reference, examines what X references. References to empty cells are NOT considered Text, so a reference to a empty cell will return FALSE. Empty Cell 4.7
ISTEXT(X) is equivalent to NOT(ISNONTEXT(X)).
Syntax: ISTEXT( Scalar X )
Returns: Logical
Constraints: None
Semantics: If X is of type Text, returns TRUE, else FALSE. References to blank cells are NOT considered Text.
See also ISNONTEXT 6.13.21, ISNUMBER 6.13.22, ISLOGICAL 6.13.19
Summary: Return the number of a value.
See also T 6.20.22, VALUE 6.13.34
Summary: Return the constant Error value #N/A.
Constraints: Shall have 0 parameters
Semantics: This function takes no arguments and returns the Error NA
See also ERROR.TYPE 6.13.11, ISERROR 6.13.16
Summary: Convert text to number, in a localeindependent way
Syntax: NUMBERVALUE( Text X [ ; Text DecimalSeparator [ ; Text GroupSeparator ] ] )
Constraints: LEN(DecimalSeparator) = 1, DecimalSeparator shall not appear in GroupSeparator
Semantics: Converts given Text value X into Number. If X is a Reference, it is first dereferenced.
If the supplied text X cannot be converted into a Number, an Error is returned.
[+]?([09]+(,[09])*)?(\.[09]+)?(([eE][+]?[09]+)%)?
See also N 6.13.26, T 6.20.22, DATEVALUE 6.10.4, TIMEVALUE 6.10.18, VALUE 6.13.34
Summary: Returns the row number(s) of a reference
Syntax: ROW( [ Reference R ] )
See also COLUMN 6.13.4, SHEET 6.13.31
Summary: Returns the number of rows in a given range
Syntax: ROWS( ReferenceArray R )
Summary: Returns the sheet number of the reference or the string representing a sheet name.
Syntax: SHEET( [ TextReference R ] )
Constraints: R shall not contain a Source Location (5.8 References)
Semantics: Returns the 1 based sheet number of the given reference or sheet name.
Hidden sheets are not excluded from the sheet count.
If no parameter is given, the result is the sheet number of the sheet containing the formula.
If a Reference is given it is not dereferenced.
If the reference encompasses more than one sheet, the result is the number of the first sheet in the range.
If a reference does not contain a sheet reference, the result is the sheet number of the sheet containing the formula.
See also COLUMN 6.13.4, ROW 6.13.29, SHEETS 6.13.32
Summary: Returns the number of sheets in a reference or current document
Syntax: SHEETS( [ Reference R ] )
Constraints: R shall not contain a Source Location (5.8 References)
Semantics: Returns the number of sheets in the given reference.
If no parameter is given, the number of sheets in the document is returned.
Hidden sheets are not excluded from the sheet count.
See also COLUMNS 6.13.5, ROWS 6.13.30, SHEET 6.13.31
Summary: Returns a number indicating the type of the provided value.
Semantics: Returns a number indicating the type of the value given:
Value's Type 
TYPE Return 
Number 
1 
Text 
2 
Logical 
4 
Error 
16 
Array 
64 
If a Reference is provided, the reference is first dereferenced, and any formulas are evaluated.
Summary: Convert text to number
If the supplied text X cannot be converted into a Number, an Error is returned.
[+]? [09]+([eE][+]?[09]+)?)%?
[+]?\$?([09]+(,[09]{3})*)?(\.[09]+)?(([eE][+]?[09]+)%)?
Evaluators shall accept accept fractional values matching the regular expression:
[+]? [09]+ \ [09]+/[19][09]?
Format 
Example 
Comment 
MM/DD/YYYY 
5/21/2006 
LOCALEDEPENDENT; Long year format with slashes. 
MM/DD/YY 
5/21/06 
LOCALEDEPENDENT; Short year format with slashes 
MMDDYYYY 
5212006 
LOCALEDEPENDENT; Long year format with dashes (short year may be supported, but it may also be used for years less than 100 . 
mmm DD, YYYY 
Oct 29, 2006 
LOCALEDEPENDENT; Short alphabetic month day, year. Note: mmm depends on the locale's language. 
DD mmm YYYY 
29 Oct 2006 
LOCALEDEPENDENT; Short alphabetic day month year 
mmmmm DD, YYYY 
October 29, 2006 
LOCALEDEPENDENT; Long alphabetic month day, year 
DD mmmmm YYYY 
29 October 2006 
LOCALEDEPENDENT; Long alphabetic day month year 
See also N 6.13.26, T 6.20.22, DATEVALUE 6.10.4, TIMEVALUE 6.10.18, NUMBERVALUE 6.13.28
These functions look up information. Note that IF() can be considered a trivial lookup function, but it is listed as a logical function instead.
Summary: Returns a cell address (reference) as text
Abs 
Meaning 
A1 = TRUE() 
A1 = FALSE() 
1 
fully absolute 
$A$1 
R1C1 
2 
row absolute, column relative 
A$1 
R1C[1] 
3 
row relative, column absolute 
$A1 
R[1]C1 
4 
fully relative 
A1 
R[1]C[1] 
Note that the INDIRECT function accepts this format.
Summary: Uses an index to return a value from a list of values.
Syntax: CHOOSE( Integer Index ; { Any Value }+ )
Summary: Return a value from a data pilot table.
Syntax: GETPIVOTDATA( Text DataField ; Reference Table { ; Text Field ; Scalar Member }* )
Semantics: Returns a single result from the calculation of a data pilot table.
Alternative syntax: GETPIVOTDATA( Reference Table ; Text Constraints )
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also INDEX 6.14.6, MATCH 6.14.9, OFFSET 6.14.11, VLOOKUP 6.14.12
Summary: Returns a value using a row and column index value (and optionally an area index).
See also AREAS 6.13.2, CHOOSE 6.14.3
Summary: Return a reference given a string representation of a reference
Syntax: INDIRECT( Text Ref [ ; Logical A1 = TRUE() ] )
Constraints: Ref is valid reference
Semantics: Given text for a reference (such as “A3”), returns a reference. If A1 is False, it is interpreted as an R1C1 reference style. For interoperability, if the Ref text includes a sheet name, evaluators should be able to parse both, the “.” dot and the “!” exclamation mark, as the sheet name separator. If evaluators support the A1=FALSE() case of the ADDRESS 6.14.2 function and include the “!” exclamation mark as the sheet name separator, evaluators shall correctly parse that in the A1=FALSE() case of this INDIRECT function. Evaluators shall correctly parse the “.” dot as the sheet name separator in the A1=TRUE() case.
See also ADDRESS 6.14.2
Summary: Look for criterion in an alreadysorted array, and return a corresponding result
Note: Interoperability is improved by use of HLOOKUP or VLOOKUP in expressions over LOOKUP.
When given two parameters, Searched is first examined:
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also HLOOKUP 6.14.5, INDEX 6.14.6, MATCH 6.14.9, OFFSET 6.14.11, VLOOKUP 6.14.12
Summary: Finds a Search item in a sequence, and returns its position (starting from 1).
Syntax: MATCH( Scalar Search ; ReferenceArray SearchRegion [ ; Integer MatchType = 1 ] )
SearchRegion shall be a vector (a single row or column)
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also HLOOKUP 6.14.5, OFFSET 6.14.11, VLOOKUP 6.14.12
Summary: Executes a formula expression while substituting a row reference and a column reference.
•.FormulaCell reference to the cell that contains the formula expression to calculate.
•.RowCell reference that is to be replaced by RowReplacement.
•.ColumnCell reference that is to be replaced by ColumnReplacement.
Note: The function may be used to create tables of expressions that depend on two input parameters.
Example: FormulaCell is B5, RowCell is B3, ColumnCell is B2
Table 24  MULTIPLE.OPERATIONS

col_B 
col_C 
col_D 
col_E 
col_F 
row_2 
1 

1 
2 
3 
row_3 
1 
1 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C3;$B$2;D$2) 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C3;$B$2;E$2) 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C3;$B$2;F$2) 
row_4 
=B2+B3 
2 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C4;$B$2;D$2) 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C4;$B$2;E$2) 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C4;$B$2;F$2) 
row_5 
=B2*B3+B4 
3 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C5;$B$2;D$2) 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C5;$B$2;E$2) 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C5;$B$2;F$2) 


4 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C6;$B$2;D$2) 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C6;$B$2;E$2) 
=MULTIPLE.OPERATIONS($B$5;$B$3;$C6;$B$2;F$2) 
Table 25  MULTIPLE.OPERATIONS

col_B 
col_C 
col_D 
col_E 
col_F 
row_2 
1 

1 
2 
3 
row_3 
1 
1 
3 
5 
7 
row_4 
2 
2 
5 
8 
11 
row_5 
3 
3 
7 
11 
15 


4 
9 
14 
19 
Summary: Modifies a reference's position and dimension.
See also COLUMN 6.13.4, COLUMNS 6.13.5, ROW 6.13.29, ROWS 6.13.30
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also HLOOKUP 6.14.5, INDEX 6.14.6, MATCH 6.14.9, OFFSET 6.14.11
The logical functions are the constants TRUE() and FALSE(), the functions that compute Logical values NOT(), AND(), and OR(), and the conditional function IF(). The OpenDocument specification mentions "logical operators"; these are another name for the logical functions.
Note that because of Error values, any logical function that accepts parameters can actually produce TRUE, FALSE, or an Error value, instead of TRUE or FALSE.
These are not bitwise operations, e.g., AND(12;10) produces TRUE(), not 8. See the bit operation functions for bitwise operations.
Summary: Compute logical AND of all parameters.
Syntax: AND( { LogicalNumberSequenceList L }+ )
Constraints: Shall have 1 or more parameters
See also OR 6.15.8, IF 6.15.4
Summary: Returns constant FALSE
Constraints: Shall have 0 parameters
Semantics: Returns logical constant FALSE. This may be a Number or a distinct type.
See also TRUE 6.15.9, IF 6.15.4
Summary: Return one of two values, depending on a condition
Syntax: IF( Logical Condition [ ; [ Any IfTrue ] [ ; [ Any IfFalse ] ] ] )
See also AND 6.15.2, OR 6.15.8
Summary: Return X unless it is an Error, in which case return an alternative value
Syntax: IFERROR( Any X ; Any Alternative )
Summary: Return X unless it is an NA, in which case return an alternative value
Syntax: IFNA( Any X ; Any Alternative )
Constraints: Shall have 1 parameter
Semantics: Computes the logical NOT. If given TRUE, returns FALSE; if given FALSE, returns TRUE.
See also AND 6.15.2, IF 6.15.4
Summary: Compute logical OR of all parameters.
Syntax: OR( { LogicalNumberSequenceList L }+ )
Constraints: Shall have 1 or more parameters
See also AND 6.15.2, IF 6.15.4
Summary: Returns constant TRUE
Constraints: Shall have 0 parameters
See also FALSE 6.15.3, IF 6.15.4
Summary: Compute a logical XOR of all parameters.
Constraints: Shall have 1 or more parameters.
See also AND 6.15.2, OR 6.15.8
This section describes functions for various mathematical functions, including trigonometric functions like SIN 6.16.55). Note that the constraint text presumes that a value of type Number is a real number (no imaginary component). Unless noted otherwise, all angle measurements are in radians.
Summary: Return the absolute (nonnegative) value.
Semantics: If N < 0, returns N, otherwise returns N.
See also Prefix Operator “” 6.4.16
Constraints: 1.0 <= N <= 1.0.
Semantics: Computes the arc cosine of a number, in radians.
Returns a principal value 0 ≤ result ≤ PI.
See also COS 6.16.19, RADIANS 6.16.49, DEGREES 6.16.25
Summary: Return the principal value of the inverse hyperbolic cosine
Semantics: Computes the principal value of the inverse hyperbolic cosine.
See also COSH 6.16.20, ASINH 6.16.8
Semantics: Computes the arc cotangent of a number, in radians.
Returns a principal value 0 < result < PI.
See also COT 6.16.21, ATAN 6.16.9, TAN 6.16.69, RADIANS 6.16.49, DEGREES 6.16.25
Summary: Return the hyperbolic arc cotangent
See also COSH 6.16.20, ASINH 6.16.8
Summary: Return the principal value of the arc sine of a number. The angle is returned in radians.
Semantics: Computes the arc sine of a number, in radians.
Returns a principal value PI/2 ≤ result ≤ PI/2.
See also SIN 6.16.55, RADIANS 6.16.49, DEGREES 6.16.25
Summary: Return the principal value of the inverse hyperbolic sine
Semantics: Computes the principal value of the inverse hyperbolic sine.
See also SINH 6.16.56, ACOSH 6.16.4
Semantics: Computes the arc tangent of a number, in radians.
Returns a principal value PI/2 < result < PI/2.
See also ATAN2 6.16.10, TAN 6.16.69, RADIANS 6.16.49, DEGREES 6.16.25
Summary: Returns the principal value of the arc tangent given a coordinate of two numbers.
The angle is returned in radians.
Syntax: ATAN2( Number x ; Number y )
Returns a principal value PI < result ≤ PI.
See also ATAN 6.16.9, TAN 6.16.69, RADIANS 6.16.49, DEGREES 6.16.25
Summary: Return the principal value of the inverse hyperbolic tangent
Semantics: Computes the principal value of the inverse hyperbolic tangent.
See also COSH 6.16.20, SINH 6.16.56, ASINH 6.16.8, ACOSH 6.16.4, ATAN 6.16.9, ATAN2 6.16.10, FISHER 6.18.26
Summary: Returns the modified Bessel function of integer order In(x).
Syntax: BESSELI( Integer X ; Number N )
See also BESSELJ 6.16.13, BESSELK 6.16.14, BESSELY 6.16.15
Summary: Returns the Bessel function of integer order Jn(x) (cylinder function)
Syntax: BESSELJ( Integer X ; Number N )
Semantics: Computes the Bessel function of integer order Jn(x). N is also known as the order.
See also BESSELI 6.16.12, BESSELK 6.16.14, BESSELY 6.16.15
Summary: Returns the modified Bessel function of integer order Kn(x).
Syntax: BESSELK( Integer X ; Number N )
Semantics: Computes the Bessel function of integer order Kn(x). N is also known as the order.
See also BESSELI 6.16.12, BESSELJ 6.16.13, BESSELY 6.16.15
Summary: Returns the Bessel function of integer order Yn(x), also known as the Neumann function.
Syntax: BESSELY( Integer X ; Number N )
See also BESSELI 6.16.12, BESSELJ 6.16.13, BESSELK 6.16.14
Summary: Returns the number of different Rlength sets that can be selected from N items.
Syntax: COMBIN( Integer N ; Integer R )
Constraints: N >= 0, R >= 0, R <= N
Note that if order is important, use PERMUT instead.
Summary: Returns the number of combinations with repetitions.
Syntax: COMBINA( Integer N ; Integer M )
Summary: Returns a number converted from one unit system into another
Syntax: CONVERT( Number N ; Text From ; Text Into )
Constraints: From and Into shall be legal units, and shall be in the same unit group.
Unit group 
Unit symbol 
Description  
Area 
 


 
Distance (Length) 
 


 
Energy 
 


 
Force (Weight) 
 


 
Information 
 


 
Magnetic Flux Density 
 


 
Mass 
 


 
Power 
 


 
Pressure 
 


 
Speed 
 


 
Temperature 
 


 
Time 
 


 
Volume 
 



For purposes of this function, a year is exactly 365.25 days long.
Table 27  Decimal Prefixes for use in CONVERT
Unit Prefix 
Description 
Prefix Value 
"Y" 
yotta 
1E+24 
"Z" 
zetta 
1E+21 
"E" 
exa 
1E+18 
"P" 
peta 
1E+15 
"T" 
tera 
1E+12 
"G" 
giga 
1E+09 
"M" 
mega 
1E+06 
"k" 
kilo 
1E+03 
"h" 
hecto 
1E+02 
“da” or "e" 
deka (note: “e” is not a standard SI prefix) 
1E+01 
"d" 
deci 
1E01 
"c" 
centi 
1E02 
"m" 
milli 
1E03 
"u" 
micro (note: this is “u”, not the standard SI µ) 
1E06 
"n" 
nano 
1E09 
"p" 
pico 
1E12 
"f" 
femto 
1E15 
"a" 
atto 
1E18 
"z" 
zepto 
1E21 
"y" 
yocto 
1E24 
Table 28  Binary prefixes for use in CONVERT
Binary Unit Prefix 
Description 
Prefix Value 
Derived from 
"Yi" 
yobi 
2^80 = 1 208 925 819 614 629 174 706 176 
yotta 
"Zi" 
zebi 
2^70 = 1 180 591 620 717 411 303 424 
zetta 
"Ei" 
exbi 
2^60 = 1 152 921 504 606 846 976 
exa 
"Pi" 
pebi 
2^50 = 1 125 899 906 842 624 
peta 
"Ti" 
tebi 
2^40 = 1 099 511 627 776 
tera 
"Gi" 
gibi 
2^30 = 1 073 741 824 
giga 
"Mi" 
mebi 
2^20 = 1 048 576 
mega 
"ki" 
kibi 
2^10 = 1024 
kilo 
Summary: Return the cosine of an angle specified in radians.
Semantics: Computes the cosine of an angle specified in radians.
See also ACOS 6.16.3, RADIANS 6.16.49, DEGREES 6.16.25
Summary: Return the hyperbolic cosine of the given hyperbolic angle
Semantics: Computes the hyperbolic cosine of a hyperbolic angle. The hyperbolic cosine is an analog of the ordinary (circular) cosine. The points (cosh t, sinh t) define the right half of the equilateral hyperbola, just as the points (cos t, sin t) define the points of a circle.
See also ACOSH 6.16.4, SINH 6.16.56, TANH 6.16.70
Summary: Return the cotangent of an angle specified in radians
Semantics: Computes the cotangent of an angle specified in radians.
See also ACOT 6.16.5, TAN 6.16.69, RADIANS 6.16.49, DEGREES 6.16.25, SIN 6.16.55, COS 6.16.19
Summary: Return the hyperbolic cotangent of the given hyperbolic angle
See also ACOSH 6.16.4, SINH 6.16.56, TANH 6.16.70
Summary: Return the cosecant of an angle specified in radians.
Semantics: Computes the cosecant cosine of an angle specified in radians. Equivalent to:
Summary: Return the hyperbolic cosecant of the given angle specified in radians
Semantics: Computes the hyperbolic cosecant of a hyperbolic angle. This is equivalent to:
See also SINH 6.16.56, CSCH
Summary: Convert radians to degrees.
Semantics: Converts a number in radians into a number in degrees. DEGREES(N) is equal to N*180/PI().
See also RADIANS 6.16.49, PI 6.16.45
Summary: Report if two numbers are equal, returns 1 if they are equal.
Syntax: DELTA( Number X [ ; Number Y = 0 ] )
Semantics: If X and Y are equal, return 1, else 0. Y is set to 0 if omitted.
See also Infix operator “=” 6.4.7
Summary: Calculates the error function.
Syntax: ERF( Number z0 [ ; Number z1 ] )
Semantics: With a single argument, returns the error function of z0:
Summary: Calculates the complementary error function.
Semantics: returns the complementary error function of z: ERFC(z) = 1 – ERF(z)
The function shall use the rates of exchange as set by the European Commission, as follows:
From 
To 
Rate 
Currency 
Decimals 
"EUR" 
"ATS" 
13.7603 
Austrian Schilling 
2 
"EUR" 
"BEF" 
40.3399 
Belgian Franc 
0 
"EUR" 
"DEM" 
1.95583 
German Mark 
2 
"EUR" 
"ESP" 
166.386 
Spanish Peseta 
0 
"EUR" 
"FIM" 
5.94573 
Finnish Markka 
2 
"EUR" 
"FRF" 
6.55957 
French Franc 
2 
"EUR" 
"IEP" 
0.787564 
Irish Pound 
2 
"EUR" 
"ITL" 
1936.27 
Italian Lira 
0 
"EUR" 
"LUF" 
40.3399 
Luxembourg Franc 
0 
"EUR" 
"NLG" 
2.20371 
Dutch Guilder 
2 
"EUR" 
"PTE" 
200.482 
Portuguese Escudo 
2 
"EUR" 
"GRD" 
340.750 
Greek Drachma 
2 
"EUR" 
"SIT" 
239.640 
Slovenian Tolar 
2 
“EUR” 
“MTL” 
0.429300 
Maltese Lira 
2 
“EUR” 
“CYP” 
0.585274 
Cypriot Pound 
2 
"EUR" 
"SKK" 
30.1260 
Slovak Koruna 
2 
Note:
The European Commission's Euro entry page is http://ec.europa.eu/euro/
The conversion rates and triangulation rules are available at http://ec.europa.eu/economy_finance/euro/adoption/conversion/index_en.htm with links to the European Council Regulation legal documents at the http://eurlex.europa.eu/ European Union law database server.
If FullPrecision is omitted or False, the result is rounded according to the decimals of the To currency. If FullPrecision is True the result is not rounded.
If TriangulationPrecision is given and >=3, the intermediate result of a triangular conversion (currency1,EUR,currency2) is rounded to that precision. If TriangulationPrecision is omitted, the intermediate result is not rounded. Also if To currency is “EUR”, TriangulationPrecision precision is used as if triangulation was needed and conversion from EUR to EUR was applied.
See also CONVERT
Summary: Rounds a number up to the nearest even integer. Rounding is away from zero.
Summary: Returns e raised by the given number.
See also LOG 6.16.40, LN 6.16.39
Summary: Return factorial (!).
Semantics: Return the factorial
See also Infix Operator "*" 6.4.4, GAMMA 6.16.34
Summary: Returns double factorial (!!).
Syntax: FACTDOUBLE( Integer F )
See also Infix Operator "*" 6.4.4, GAMMA 6.16.34, FACT 6.16.32
Summary: Return gamma function value.
Constraints: N<>0 and N not a negative integer.
Summary: Returns the natural logarithm of the GAMMA function.
Constraints: For each X, X > 0
Semantics: Returns the same value as =LN(GAMMA(X))
See also GAMMA 6.16.34, FACT 6.16.32
Summary: Returns the greatest common divisor (GCD)
Syntax: GCD( { NumberSequenceList X }+ )
Constraints: For all a in X: INT(a) >= 0 and for at least one a in X: INT(a)>0
Semantics: Return the largest integer N such that for every a in X: INT(a) is a multiple of N.
Summary: Returns 1 if a number is greater than or equal to another number, else returns 0.
Syntax: GESTEP( Number X [ ; Number Step = 0 ] )
Summary: Returns the least common multiplier
Syntax: LCM( { NumberSequenceList X }+ )
Constraints: For all in X: INT(X)=X, X >= 0
Summary: Return the natural logarithm of a number.
Semantics: Computes the natural logarithm (base e) of the given number.
See also LOG 6.16.40, LOG10 6.16.41, POWER 6.16.46, EXP 6.16.31
Summary: Return the logarithm of a number in a specified base.
Syntax: LOG( Number N [ ; Number Base = 10 ] )
See also LOG10 6.16.41, LN 6.16.39, POWER 6.16.46, EXP 6.16.31
Summary: Return the base 10 logarithm of a number.
Semantics: Computes the base 10 logarithm of a number.
See also LOG 6.16.40, LN 6.16.39, POWER 6.16.46, EXP 6.16.31
Summary: Return the remainder when one number is divided by another number.
Syntax: MOD( Number a ; Number b )
Semantics: Computes the remainder of a/b. The remainder has the same sign as b.
See also Infix Operator "/" 6.4.5, QUOTIENT 6.16.48
Summary: Returns the multinomial for the given values.
Syntax: MULTINOMIAL( { NumberSequence A }+ )
Summary: Rounds a number up to the nearest odd integer, where "up" means "away from 0".
Summary: Return the approximate value of Pi.
See also SIN 6.16.55, COS 6.16.19
Summary: Return the value of one number raised to the power of another number.
Syntax: POWER( Number a ; Number b )
Semantics: Computes a raised to the power b.
•.POWER(0,0) is implementationdefined, but shall be one of 0,1, or an Error.
•.POWER(a,b), where a<=0 and INT(b)!=b, is implementationdefined.
See also LOG 6.16.40, LOG10 6.16.41, LN 6.16.39, EXP 6.16.31
Summary: Multiply the set of numbers, including all numbers inside ranges
Syntax: PRODUCT( { NumberSequence N }+ )
Summary: Return the integer portion of a division.
Syntax: QUOTIENT( Number A ; Number B )
Semantics: Return the integer portion of a division.
Summary: Convert degrees to radians.
Semantics: Converts a number in degrees into a number in radians. RADIANS(N) is equal to N*PI()/180.
See also DEGREES 6.16.25, PI 6.16.45
Summary: Return a random number between 0 (inclusive) and 1 (exclusive).
Summary: Return a random integer number between A and B.
Syntax: RANDBETWEEN( Integer A ; Integer B )
Summary: Return the secant of an angle specified in radians.
Semantics: Computes the secant cosine of an angle specified in radians. Equivalent to:
Summary: Returns the sum of a power series.
Syntax: SERIESSUM( Number X ; Number N ; Number M ; Array Coefficients )
●M: the increment by which to increase N for each term in the series.
●Coefficients: a set of coefficients by which each successive power of the variable X is multiplied.
All elements of Coefficients are of type Number.
X < > 0 if any of the exponents, which are generated from N and M, are negative.
Semantics: Returns a sum of powers of the number X.
With C being the number of coefficients the function is computed as:
Summary: Return the sign of a number
Semantics: If N < 0, returns 1; if N > 0, returns +1; if N == 0, returns 0.
Summary: Return the sine of an angle specified in radians
Semantics: Computes the sine of an angle specified in radians.
See also ASIN 6.16.7, RADIANS 6.16.49, DEGREES 6.16.25
Summary: Return the hyperbolic sine of the given hyperbolic angle
Semantics: Computes the hyperbolic sine of a hyperbolic angle. The hyperbolic sine is an analog of the ordinary (circular) sine. The points (cosh t, sinh t) define the right half of the equilateral hyperbola, just as the points (cos t, sin t) define the points of a circle.
See also ASINH 6.16.8, COSH 6.16.20, TANH 6.16.70
Summary: Return the hyperbolic secant of the given angle specified in radians
Semantics: Computes the hyperbolic secant of a hyperbolic angle. This is equivalent to:
See also SINH 6.16.56, CSCH
Summary: Return the square root of a number
See also POWER 6.16.46, IMSQRT 6.8.22, SQRTPI 6.16.59
Summary: Return the square root of a number multiplied by pi.
See also POWER 6.16.46, SQRT 6.16.58, PI 6.16.45, IMSQRT 6.8.22
Summary: Evaluates a function on a range.
Syntax: SUBTOTAL( Integer function ; NumberSequence sequence )
Function 
Exclude hidden by filter 
Exclude hidden by filter or collapsed 
AVERAGE 
1 
101 
COUNT 
2 
102 
COUNTA 
3 
103 
MAX 
4 
104 
MIN 
5 
105 
PRODUCT 
6 
106 
STDEV 
7 
107 
STDEVP 
8 
108 
SUM 
9 
109 
VAR 
10 
110 
VARP 
11 
111 
See also SUM 6.16.61, AVERAGE 6.18.3
Summary: Sum (add) the set of numbers, including all numbers in ranges
Syntax: SUM( { NumberSequenceList N }+ )
Constraints: N != {}; Evaluators may evaluate expressions that do not meet this constraint.
Semantics: Adds Numbers (and only Numbers) together (see the text on conversions).
Summary: Sum the values of cells in a range that meet a criteria.
Syntax: SUMIF( ReferenceListReference R ; Criterion C [ ; Reference S ] )
Constraints: Does not accept constant values as the range parameter.
Semantics: Sums the values of type Number in the range R or S that meet the Criterion C (4.11.7).
If S is not given, R may be a reference list. If S is given, R shall not be a reference list with more than 1 references and an Error be generated if it was.
If the optional range S is included, then the values of S starting from the top left cell and matching the geometry of R (same number of rows and columns) are summed if the corresponding value in R meets the Criterion. The actual range S is not considered. If the resulting range exceeds the sheet bounds, column numbers larger than the maximum column and row numbers larger than the maximum row are silently ignored, no Error is generated for this case.
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also COUNTIF 6.13.9, SUM 6.16.61, Infix Operator "=" 6.4.7, Infix Operator "<>" 6.4.8, Infix Operator Ordered Comparison ("<", "<=", ">", ">=") 6.4.9
Summary: Sum the values of cells in a range that meet multiple criteria in multiple ranges.
Syntax: SUMIFS( Reference R ; Reference R1 ; Criterion C1 [ ; Reference R2 ; Any C2 ]... )
Constraints: Does not accept constant values as the reference parameter.
Semantics: Sums the value of cells in range R that meet the Criterion C1 in the reference range R1 and the Criterion C2 in the reference range R2, and so on (4.11.7). All reference ranges shall have the same dimension and size, else an Error is returned. A logical AND is applied between each array result of each selection; an entry is counted only if the same position in each array is the result of a criteria match.
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also COUNTIFS 6.13.10, SUMIF 6.16.62, Infix Operator "=" 6.4.7, Infix Operator "<>" 6.4.8, Infix Operator Ordered Comparison ("<", "<=", ">", ">=") 6.4.9
Summary: Returns the sum of the products of the matrix elements.
Syntax: SUMPRODUCT( { ForceArray Array A }+ )
Constraints: All matrices shall have the same dimensions.
Semantics: Multiplies the corresponding elements of all matrices and returns the sum of them.
Summary: Sum (add) the set of squares of numbers, including all numbers in ranges
Syntax: SUMSQ( { NumberSequence N }+ )
Constraints: N != {}; Evaluators may evaluate expressions that do not meet this constraint.
Semantics: Adds squares of Numbers (and only Numbers) together. See the text on conversions.
Summary: Returns the sum of the difference between the squares of the matrices A and B.
Syntax: SUMX2MY2( ForceArray Array A ; ForceArray Array B )
Constraints: Both matrices shall have the same dimensions.
Semantics: Sums up the differences of the corresponding elements squares for two matrices.
Summary: Returns the total sum of the squares of the matrices A and B.
Syntax: SUMX2PY2( ForceArray Array A ; ForceArray Array B )
Constraints: Both matrices shall have the same dimensions.
Semantics: Sums up the squares of each element of the two matrices.
Summary: Returns the sum of the squares of the differences between matrix A and B.
Syntax: SUMXMY2( ForceArray Array A ; ForceArray Array B )
Constraints: Both matrices shall have the same dimensions.
Semantics: Sums up the squares of the differences of the corresponding elements for two matrices.
Summary: Return the tangent of an angle specified in radians
Semantics: Computes the tangent of an angle specified in radians.
See also ATAN 6.16.9, ATAN2 6.16.10, RADIANS 6.16.49, DEGREES 6.16.25, SIN 6.16.55, COS 6.16.19, COT 6.16.21
Summary: Return the hyperbolic tangent of the given hyperbolic angle
Semantics: Computes the hyperbolic tangent of a hyperbolic angle. The hyperbolic tangent is an analog of the ordinary (circular) tangent. The points (cosh t, sinh t) define the right half of the equilateral hyperbola, just as the points (cos t, sin t) define the points of a circle.
See also ATANH 6.16.11, SINH 6.16.56, COSH 6.16.20, FISHERINV 6.18.27
Rounding functions convert an arbitrary Number into an Integer.
Summary: Round a number N up to the nearest multiple of the second parameter, significance.
Syntax: CEILING( Number N [ ; [ Number significance ] [ ; Number mode ] ] )
Constraints: Both N and significance shall be numeric and have the same sign if not 0.
See also FLOOR 6.17.4, INT 6.17.3
Summary: Rounds a number down to the nearest integer.
See also ROUND 6.17.6, TRUNC 6.17.9
Summary: Round a number N down to the nearest multiple of the second parameter, significance.
Syntax: FLOOR( Number N [ ; [ Number significance ] [ ; Number mode ] ] )
Constraints: Both N and significance shall be numeric and have the same sign.
See also CEILING 6.17.2, INT 6.17.3
Summary: Rounds the number to given multiple.
Syntax: MROUND( Number a ; Number b )
Summary: Rounds the value X to the nearest multiple of the power of 10 specified by Digits.
Syntax: ROUND( Number X [ ; Number Digits = 0 ] )
See also TRUNC 6.17.9, INT 6.17.3
Summary: Rounds the value X towards zero to the number of digits specified by Digits
Syntax: ROUNDDOWN( Number X [ ; Integer Digits = 0 ] )
See also TRUNC 6.17.9, INT 6.17.3, ROUND 6.17.6, ROUNDUP 6.17.8
Summary: Rounds the value X away from zero to the number of digits specified by Digits
Syntax: ROUNDUP( Number X [ ; Integer Digits = 0 ] )
See also TRUNC 6.17.9, INT 6.17.3, ROUND 6.17.6, ROUNDDOWN 6.17.7
Summary: Truncate a number to a specified number of digits.
Syntax: TRUNC( Number a ; Number b )
See also ROUND 6.17.6, INT 6.17.3
The following are statistical functions (functions that report information on a set of numbers). Some functions that could also be considered statistical functions, such as SUM, are listed elsewhere.
Summary: Calculates the average of the absolute deviations of the values in list.
Syntax: AVEDEV( { NumberSequenceList N }+ )
Summary: Average the set of numbers
Syntax: AVERAGE( { NumberSequence N }+ )
Constraints: At least one Number included. Returns an Error if no Numbers provided.
Semantics: Computes SUM(List) / COUNT(List).
See also SUM 6.16.61, COUNT 6.13.6
Summary: Average values, including values of type Text and Logical.
Syntax: AVERAGEA( { Any N }+ )
Constraints: At least one value included. Returns an Error if no value provided.
Summary: Average the values of cells in a range that meet a criteria.
Syntax: AVERAGEIF( Reference R ; Criterion C [ ; Reference A ] )
Constraints: Does not accept constant values as reference parameters.
Semantics: If reference A is omitted, averages the values of cells in the reference range R that meet the Criterion C (4.11.7). If reference A is given, averages the values of cells of a range that is constructed using the top left cell of reference A and applying the dimensions, shape and size, of reference R. If no cell in range R matches the Criterion C, an Error is returned. If no Numbers are in the range to be averaged, an Error is returned.
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also AVERAGEIFS 6.18.6, COUNTIF 6.13.9, SUMIF 6.16.62, Infix Operator "=" 6.4.7, Infix Operator "<>" 6.4.8, Infix Operator Ordered Comparison ("<", "<=", ">", ">=") 6.4.9
Summary: Average the values of cells that meet multiple criteria in multiple ranges.
Syntax: AVERAGEIFS( Reference A ; Reference R1 ; Criterion C1 [ ; Reference R2 ; Criterion C2 ]... )
Constraints: Does not accept constant values as reference parameters.
Semantics: Averages the values of cells in the reference range A that meet the Criterion C1 in the reference range R1 and the Criterion C2 in the reference range R2, and so on (4.11.7). All reference ranges shall have the same dimension and size, else an Error is returned. A logical AND is applied between each array result of each selection; a cell of reference range A is evaluated only if the same position in each array is the result of a Criterion match. If no numbers are in the result set to be averaged, an Error is returned.
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS properties. 3.4
See also AVERAGEIF 6.18.5, COUNTIFS 6.13.10, Infix Operator "=" 6.4.7, Infix Operator "<>" 6.4.8, Infix Operator Ordered Comparison ("<", "<=", ">", ">=") 6.4.9
Semantics: If Cumulative is FALSE(), BETADIST returns 0 if x < a or x > b and the value
If Cumulative is TRUE(), BETADIST returns 0 if x < a, 1 if x > b, and the value
Summary: returns the inverse of BETADIST(x;a;b;a;b;TRUE()).
Syntax: BETAINV( Number p ; Number a ; Number b [ ; Number a = 0 [ ; Number b = 1 ] ] )
Summary: Returns the probability of a trial result using binomial distribution.
Syntax: BINOM.DIST.RANGE( Integer N ; Number P ; Integer S [ ; Integer S2 ] )
Constraints: 0<=P<=1, 0<=S<=S2<=N
This function is computed as follows:
If S2 is not given, let S2:=S. Then the function returns the value of
Note: This describes how the function works in OOo. KSpread has a similar function, called BINO, which takes the arguments in a different order, and doesn't have the 4th argument. Gnumeric doesn't have this function. Excel doesn't have this function.
Summary: Returns the binomial distribution.
Syntax: BINOMDIST( Integer S ; Integer N ; Number P ; Logical Cumulative )
Constraints: 0 <= P <= 1; 0 <= S <= N
See also BINOM.DIST.RANGE 6.18.9
Summary: returns the righttail probability for the c2distribution.
Syntax: LEGACY.CHIDIST( Number x ; Number DegreesOfFreedom )
Constraints: DegreesOfFreedom is a positive integer.
See also CHISQDIST 6.18.12, LEGACY.CHITEST 6.18.15
Syntax: CHISQDIST( Number x ; Number DegreesOfFreedom [ ; Logical Cumulative = TRUE() ] )
Constraints: DegreesOfFreedom is a positive integer.
Semantics: In the following n is DegreesOfFreedom.
See also LEGACY.CHIDIST 6.18.11
Summary: returns the inverse of LEGACY.CHIDIST(x; DegreesOfFreedom).
Syntax: LEGACY.CHIINV( Number p ; Number DegreesOfFreedom )
See also LEGACY.CHIDIST 6.18.11
Summary: returns the inverse of CHISQDIST(x; DegreesOfFreedom; TRUE()).
Syntax: CHISQINV( Number p ; Number DegreesOfFreedom )
Summary: Returns some Chi square goodnessforfit test.
Syntax: LEGACY.CHITEST( ForceArray Array A ; ForceArray Array E )
Constraints:
ROWS(A) == ROWS(E)
COLUMNS(A) == COLUMNS(E)
COLUMNS(A) * ROWS(A) > 1
First a Chi square statistic is calculated:
Then LEGACY.CHIDIST is called with the Chisquare value and a degree of freedom (df):
See also LEGACY.CHIDIST 6.18.11
Summary: Returns the confidence interval for a population mean.
Syntax: CONFIDENCE( Number alpha ; Number stddev ; Number size )
Constraints: 0 < alpha < 1; stddev > 0, size >= 1
Summary: Calculates the correlation coefficient of values in N1 and N2.
Syntax: CORREL( ForceArray Array N1 ; ForceArray Array N2 )
Summary: Calculates covariance of two cell ranges.
Syntax: COVAR( ForceArray Array n1 ; ForceArray Array n2 )
Syntax: CRITBINOM( Number Trials ; Number SP ; Number Alpha )
Constraints: Trials >=0, 0 <= SP <= 1, Alpha >= 1
Summary: Calculates sum of squares of deviations.
Syntax: DEVSQ( { NumberSequence n }+ )
where a is the result of calling AVERAGE(n).
Syntax: EXPONDIST( Number x ; Number l [; Logical Cumulative = TRUE() ] )
Semantics: If Cumulative is FALSE(), EXPONDIST returns 0 if x < 0 and the value
If Cumulative is TRUE(), EXPONDIST returns 0 if x < 0 and the value
Syntax: FDIST( Number x ; Number r1 ; Number r2 [ ; Logical Cumulative = TRUE() ] )
Constraints: r1 and r2 are positive integers
If Cumulative is TRUE(), FDIST returns 0 if x < 0 and the value
Syntax: LEGACY.FDIST( Number x ; Number r1 ; Number r2 )
Constraints: r1 and r2 are positive integers
LEGACY.FDIST returns Error if x < 0 and the value
Note that the latter is (1FDIST(x; r1; r2;TRUE())).
Summary: returns the inverse of FDIST(x;r1;r2;TRUE()).
Syntax: FINV( Number p ; Number r1 ; Number r2 )
Semantics: FINV returns the unique nonnegative number x such that FDIST(x;r1;r2) = p.
See also FDIST 6.18.22, LEGACY.FDIST 6.18.23, LEGACY.FINV 6.18.25
Summary: returns the inverse of LEGACY.FDIST(x;r1;r2).
Syntax: LEGACY.FINV( Number p ; Number r1 ; Number r2 )
Semantics: LEGACY.FINV returns the unique nonnegative number x such that LEGACY.FDIST(x;r1;r2) = p.
See also FDIST 6.18.22, LEGACY.FDIST 6.18.23, FINV 6.18.24
Summary: returns the Fisher transformation.
Semantics: Returns the Fisher transformation with a sample correlation r. This function computes