Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format
Committee
Specification Draft 01 /
Public Review Draft 01
26 August 2019
This stage:
https://docs.oasisopen.org/office/OpenDocument/v1.3/csprd01/part4formula/OpenDocumentv1.3csprd01part4formula.odt (Authoritative)
Previous stage:
N/A
Latest stage:
https://docs.oasisopen.org/office/OpenDocument/v1.3/OpenDocumentv1.3part4formula.odt (Authoritative)
https://docs.oasisopen.org/office/OpenDocument/v1.3/OpenDocumentv1.3part4formula.html
https://docs.oasisopen.org/office/OpenDocument/v1.3/OpenDocumentv1.3part4formula.pdf
Technical Committee:
OASIS Open Document Format for Office Applications (OpenDocument) TC
Chairs:
Jos van den Oever (jos.vanden.oever@logius.nl), Logius
Patrick Durusau (patrick@durusau.net), Individual
Editor:
Patrick Durusau (patrick@durusau.net), Individual
This prose specification is one component of a Work Product which includes:
Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 1: Introduction. https://docs.oasisopen.org/office/OpenDocument/v1.3/csprd01/part1introduction/OpenDocumentv1.3csprd01part1introduction.html.
Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 2: Packages. https://docs.oasisopen.org/office/OpenDocument/v1.3/csprd01/part2packages/OpenDocumentv1.3csprd01part2packages.html.
Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 3: OpenDocument Schema. https://docs.oasisopen.org/office/OpenDocument/v1.3/csprd01/part3schema/OpenDocumentv1.3csprd01part3schema.html.
Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format (this part). https://docs.oasisopen.org/office/OpenDocument/v1.3/csprd01/part4formula/OpenDocumentv1.3csprd01part4formula.html.
XML schemas and ontologies. https://docs.oasisopen.org/office/OpenDocument/v1.3/csprd01/schemas/.
Related work:
This specification replaces or supersedes:
OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2. 29 September 2011. OASIS Standard. http://docs.oasisopen.org/office/v1.2/os/OpenDocumentv1.2os.html.
Abstract:
This document is Part 4 of the Open Document Format for Office Applications (OpenDocument) Version 1.3 specification.
Status:
This document was last revised or approved by the OASIS Open Document Format for Office Applications (OpenDocument) TC on the above date. The level of approval is also listed above. Check the “Latest stage” location noted above for possible later revisions of this document. Any other numbered Versions and other technical work produced by the Technical Committee (TC) are listed at https://www.oasisopen.org/committees/tc_home.php?wg_abbrev=office#technical.
TC members should send comments on this specification to the TC’s email list. Others should send comments to the TC’s public comment list, after subscribing to it by following the instructions at the “Send A Comment” button on the TC’s web page at https://www.oasisopen.org/committees/office/.
This specification is provided under the RF on Limited Terms Model of the OASIS IPR Policy, the mode chosen when the Technical Committee was established. 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 TC’s web page (https://www.oasisopen.org/committees/office/ipr.php).
Note that any machinereadable content (Computer Language Definitions) declared Normative for this Work Product is provided in separate plain text files. In the event of a discrepancy between any such plain text file and display content in the Work Product's prose narrative document(s), the content in the separate plain text file prevails.
Citation format:
When referencing this specification the following citation format should be used:
[OpenDocumentv1.3part4]
Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format. Edited by Patrick Durusau. 26 August 2019. OASIS Committee Specification Draft 01 / Public Review Draft 01. https://docs.oasisopen.org/office/OpenDocument/v1.3/csprd01/part4formula/OpenDocumentv1.3csprd01part4formula.html. Latest stage: https://docs.oasisopen.org/office/OpenDocument/v1.3/OpenDocumentv1.3part4formula.html.
Notices
Copyright © OASIS Open 2019. 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 name "OASIS" is a trademark 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 https://www.oasisopen.org/policiesguidelines/trademark for above guidance.
1.5 NonNormative References20
2 Expressions and Evaluators21
2.2 OpenDocument Formula Expression21
2.3.1 OpenDocument Formula Evaluator21
2.3.2 OpenDocument Formula Small Group Evaluator21
2.3.3 OpenDocument Formula Medium Group Evaluator22
2.3.4 OpenDocument Formula Large Group Evaluator23
2.4 Variances (Implementationdefined, Unspecified, and Behavioral Changes)23
3.2.2 Expression Calculation25
3.2.3 Operator and Function Evaluation25
3.3 NonScalar Evaluation (aka 'Array expressions')26
3.5 When recalculation occurs28
4 Types30
5.6 Functions and Function Parameters40
5.7 Nonstandard Function Names40
5.10.2 Lookup of Defined Labels42
5.10.3 Automatic Lookup of Labels42
5.10.4 Implicit Intersection43
5.10.6 Automatic Intersection44
6 Standard Operators and Functions47
6.2 Common Template for Functions and Operators47
6.3 Implicit Conversion Operators48
6.3.4 Force to array context (ForceArray)48
6.3.7 Conversion to NumberSequence49
6.3.8 Conversion to NumberSequenceList49
6.3.9 Conversion to DateSequence49
6.3.10 Conversion to Complex Number49
6.3.11 Conversion to ComplexSequence50
6.3.12 Conversion to Logical50
6.3.13 Conversion to LogicalSequence50
6.3.15 Conversion to DateParam51
6.3.16 Conversion to TimeParam51
6.4.9 Infix Operator Ordered Comparison ("<", "<=", ">", ">=")53
6.4.11 Infix Operator Reference Range (":")53
6.4.12 Infix Operator Reference Intersection ("!")54
6.4.13 Infix Operator Reference Concatenation ("~") (aka Union)54
6.7 Byteposition text functions58
6.8 Complex Number Functions60
6.10 Date and Time Functions68
6.11 External Access Functions76
6.12.51 XIRR100
6.12.52 XNPV100
6.13.2 AREAS102
6.13.3 CELL102
6.13.6 COUNT104
6.13.13 INFO106
6.13.20 ISNA109
6.13.26 N110
6.13.27 NA110
6.13.29 ROW111
6.13.30 ROWS111
6.13.33 TYPE112
6.14.6 INDEX117
6.14.9 MATCH118
6.14.10 MULTIPLE.OPERATIONS119
6.15.2 AND121
6.15.3 FALSE122
6.15.4 IF122
6.15.6 IFNA123
6.15.7 NOT123
6.15.8 OR123
6.15.9 TRUE124
6.15.10 XOR124
6.16 Mathematical Functions124
6.16.2 ABS124
6.16.3 ACOS124
6.16.4 ACOSH125
6.16.5 ACOT125
6.16.6 ACOTH125
6.16.7 ASIN125
6.16.8 ASINH126
6.16.9 ATAN126
6.16.19 COS135
6.16.20 COSH136
6.16.21 COT136
6.16.22 COTH136
6.16.23 CSC136
6.16.24 CSCH137
6.16.27 ERF137
6.16.28 ERFC138
6.16.30 EVEN139
6.16.31 EXP139
6.16.32 FACT139
6.16.36 GCD140
6.16.38 LCM141
6.16.39 LN141
6.16.40 LOG141
6.16.42 MOD142
6.16.44 ODD142
6.16.45 PI142
6.16.50 RAND144
6.16.52 SEC144
6.16.54 SIGN145
6.16.55 SIN145
6.16.56 SINH145
6.16.57 SECH145
6.16.58 SQRT146
6.16.61 SUM147
6.16.69 TAN149
6.16.70 TANH149
6.17.2 INT150
6.17.3 FLOOR150
6.17.5 ROUND151
6.17.8 TRUNC151
6.18.24 FINV159
6.18.39 KURT164
6.18.45 MAX170
6.18.46 MAXA170
6.18.48 MIN171
6.18.49 MINA171
6.18.50 MODE171
6.18.61 PHI176
6.18.63 PROB177
6.18.65 RANK178
6.18.66 RSQ178
6.18.67 SKEW179
6.18.78 TINV183
6.18.82 VAR186
6.18.83 VARA186
6.18.84 VARP187
6.19 Number Representation Conversion Functions188
6.19.3 BASE189
6.20.2 ASC195
6.20.3 CHAR197
6.20.4 CLEAN198
6.20.5 CODE198
6.20.8 EXACT198
6.20.9 FIND199
6.20.11 JIS199
6.20.12 LEFT201
6.20.13 LEN201
6.20.15 MID202
6.20.18 REPT203
6.20.22 T204
6.20.23 TEXT204
6.20.24 TRIM205
7.1 General207
7.3 Inline nonconstant arrays207
8.1 General208
Appendix A. Changes From Previous Specification Versions (Non Normative)209
A.1. Changes from “Open Document Format for Office Applications (OpenDocument) v1.2”209
This document is part of the Open Document Format for Office Applications (OpenDocument) Version 1.3 specification. It defines a formula language for OpenDocument documents, which is also called OpenFormula.
OpenFormula is a specification of an open format for exchanging recalculated formulas between office applications, in particular, formulas in spreadsheet documents. OpenFormula defines data types, syntax, and semantics for recalculated formulas, including predefined functions and operations.
OpenFormula is intended to be a supporting document to the Open Document Format for Office Applications (OpenDocument) format, particularly for defining its attributes table:formula and text:formula. It can also be used in other circumstances where a simple, easytoread infix text notation is desired for exchanging recalculated formulas.
Note: Using OpenFormula allows document creators to change the office application they use, exchange formulas with others (who may use a different application), and access formulas far in the future, with confidence that the recalculated formulas in their documents will produce equivalent results if given equivalent inputs.
All text is normative unless otherwise labeled.
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:
data types
syntax
semantics
for recalculated formulas. 3.5
OpenFormula also defines functions.
OpenFormula does not define:
a user interface
a general notation for mathematical expressions
[CharModel] Martin J. Dürst, et. al., Character Model for the World Wide Web 1.0: Fundamentals, http://www.w3.org/TR/2005/RECcharmod20050215/, W3C, 2005.
[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.
[ISO4217] ISO 4217:2008 Codes for the representation of currencies and funds, International Organization for Standardization and International Electrotechnical Commission, 2008.
[ISO8601] ISO 8601:2004 Data elements and interchange formats  Information interchange  Representation of dates and times, International Organization for Standardization and International Electrotechnical Commission, 2004.
[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/).
[UTR15] Mark Davis, Martin Dürst, Unicode Normalization Forms, Unicode Technical Report #15, http://www.unicode.org/reports/tr15/tr1525.html, 2005.
[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] The Unicode Consortium., JIS X 0201 (1976) to Unicode 1.1 Table, 1994, http://www.unicode.org/Public/MAPPINGS/OBSOLETE/EASTASIA/JIS/JIS0201.TXT.
[JISX0208] The Unicode Consortium., 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.
The OpenDocument specification defines conformance for formula expressions and evaluators. For evaluators, there are three groups of features that an evaluator may support. This chapter defines the basic requirements for the individual conformance targets.
An OpenDocument formula 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:
It may implement subsets or supersets of this specification.
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
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.
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 1: An expression may reference a function not defined by this specification 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 2: 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:
It shall implement at least the limits defined in the “Basic Limits” section. 3.7
It shall implement the syntax defined in these sections on syntax: Criteria 4.11.10; Basic Expressions 5.2; Constant Numbers 5.3; Constant Strings 5.4; Operators 5.5; Functions and Function Parameters 5.6; Nonstandard Function Names 5.7; References 5.8; Simple Named Expressions ; Errors 5.12; Whitespace 5.14.
It shall implement all implicit conversions for the types it implements, at least Text 6.3.14, Conversion to Number 6.3.5, Reference , Conversion to Logical 6.3.12, and when an expression returns an Error.
It shall implement the following operators (which are all the operators except reference union (~)): Infix Operator Ordered Comparison ("<", "<=", ">", ">=") 6.3.5; Infix Operator "&” 6.4.10; Infix Operator "+” 6.4.2; Infix Operator "” 6.4.3; Infix Operator "*” 6.4.4; Infix Operator "/” 6.4.5; Infix Operator "^” 6.4.6; Infix Operator "=” 6.4.7; Infix Operator "<>” 6.4.8; Postfix Operator “%” 6.4.14; Prefix Operator “+” 6.4.15; Prefix Operator “” 6.4.16; Infix Operator Reference Intersection ("!") 6.4.12; Infix Operator Range (":") 6.4.11.
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.14 ; 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.20 ; HLOOKUP 6.14.5 ; HOUR 6.10.10 ; IF 6.15.4 ; INDEX 6.14.6 ; INT 6.17.2 ; IRR 6.12.24 ; 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.29 ; NPV 6.12.30 ; ODD 6.16.44 ; OR 6.15.8 ; PI 6.16.45 ; PMT 6.12.36 ; POWER 6.16.46 ; PRODUCT 6.16.47 ; PROPER 6.20.16 ; PV 6.12.41 ; RADIANS 6.16.49 ; RATE 6.12.42 ; REPLACE 6.20.17 ; REPT 6.20.18 ; RIGHT 6.20.19 ; ROUND 6.17.5 ; ROWS 6.13.30 ; SECOND 6.10.16 ; SIN 6.16.55 ; SLN 6.12.45 ; 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.46 ; 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.8 ; 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
It need not evaluate references that contain more than one area.
It need not implement inline arrays 5.13, complex numbers 4.4, and the reference union operator 6.4.13.
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:
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.1 ; 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.5 ; COUPDAYS 6.12.6 ; COUPDAYSNC 6.12.7 ; COUPNCD 6.12.7 ; COUPNUM 6.12.9 ; COUPPCD 6.12.10 ; COVAR 6.18.18 ; CRITBINOM 6.18.19 ; CUMIPMT 6.12.11 ; CUMPRINC 6.12.12 ; DATEVALUE 6.10.4 ; DAYS360 6.10.7 ; DB 6.12.13 ; DEVSQ 6.18.20 ; DISC 6.12.15 ; DOLLARDE 6.12.16 ; DOLLARFR 6.12.17 ; DURATION 6.12.18 ; EFFECT 6.12.19 ; 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.3 ; 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.22 ; 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.26 ; MEDIAN 6.18.47 ; MINVERSE 6.5.3 ; MIRR 6.12.27 ; MMULT 6.5.4 ; MODE 6.18.50 ; MROUND 6.17.4 ; MULTINOMIAL 6.16.43 ; NEGBINOMDIST 6.18.51 ; NETWORKDAYS 6.10.14 ; NOMINAL 6.12.28 ; ODDFPRICE 6.12.31 ; ODDFYIELD 6.12.32 ; ODDLPRICE 6.12.33 ; ODDLYIELD 6.12.34 ; 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.38 ; PRICEMAT 6.12.40 ; 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.43 ; ROMAN 6.19.17 ; ROUNDDOWN 6.17.6 ; ROUNDUP 6.17.7 ; 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.47 ; TBILLPRICE 6.12.48 ; TBILLYIELD 6.12.49 ; 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.50 ; WEEKNUM 6.10.21 ; WEIBULL 6.18.86 ; WORKDAY 6.10.22 ; XIRR 6.12.51 ; XNPV 6.12.52 ; YEARFRAC 6.10.24 ; YIELD 6.12.53 ; YIELDDISC 6.12.54 ; YIELDMAT 6.12.55 ; ZTEST 6.18.87
It shall implement the Infix Operator Reference Union ("~") 6.4.13
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:
It shall implement the syntax defined in these sections on syntax: Inline Arrays 5.13; Automatic Intersection 5.10.6; External Named Expressions 5.11.
It shall implement the complex number type as discussed in the section on Complex Number 4.4, array formulas, and Sheetlocal Named Expressions.
It shall implement the following functions as defined in this specification: AMORLINC 6.12.4 ; 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.21 ; 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.9 ; IMCSC 6.8.10 ; IMCSCH 6.8.11 ; IMDIV 6.8.12 ; IMEXP 6.8.13 ; IMLN 6.8.14 ; IMLOG10 6.8.15 ; IMLOG2 6.8.16 ; IMPOWER 6.8.17 ; IMPRODUCT 6.8.18 ; IMREAL 6.8.19 ; IMSEC 6.8.22 ; IMSECH 6.8.23 ; IMSIN 6.8.20 ; IMSQRT 6.8.24 ; IMSUB 6.8.25 ; IMSUM 6.8.26 ; IMTAN 6.8.27; INDIRECT 6.14.7 ; INFO 6.13.13 ; IPMT 6.12.23 ; ISFORMULA 6.13.18 ; ISPMT 6.12.25 ; 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.35 ; PERMUTATIONA 6.18.60 ; PHI 6.18.61 ; PPMT 6.12.37 ; PRICEDISC 6.12.39 ; REPLACEB 6.7.6 ; RIGHTB 6.7.7 ; RRI 6.12.44 ; 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
Applications should document all implementationdefined variances from this standard in a manner that enables application users to obtain the information.
In a few cases a specific approach is required (e.g., string indexes begin at one), which may vary in the user interfaces of different implementations.
Note 1: In practice, for nearly all documents the differences are irrelevant. The primary variances and differences from OpenFormula and some existing applications are:
Some conversions between types are not required to be automatic. In particular, applications may, but need not, perform automatic conversion of text in a cell when it is to be used as a number.
There need not be a distinguishable Logical type. Applications may have a Logical type distinct from Number and Text (see Distinct Logical 8.2), but Logical values may also be represented by the Number type using the values 1 (TRUE) and 0 (FALSE). This means that functions that take number sequences (such as SUM) may but need not include true and false values in the sequence.
Applications vary on the set of Errors they support. In this specification the only distinguished Error is #N/A; all other errors are simply errors, allowing applications to choose the Error set that best meets their needs.
In this specification, string index positions start from 1. Users of applications with string index positions starting from 0 shall add and subtract 1 on import/export of this format, as appropriate.
Database criteria match patterns (such as the pattern matching language for text) have historically varied: Some support glob syntax (e.g., a*b is a, followed by 0 or more characters, followed by b), while others support traditional regular expression syntax (e.g., a*b is zero or more a’s, followed by b). This specification supports both pattern languages.
Note 2: Interoperability is improved by the use of the DATE 6.10.2 and TIME 6.10.17 functions or the textual [ISO8601] date representation because dates in that format do not rely upon epoch or localespecific settings.
In an OpenDocument file, calculation settings impact formula recalculation, which can be the same or different from a particular application's defaults. These include whether or not text comparisons are casesensitive, or if search criteria apply to the whole cell.
This section describes the basic formula processing model: how expressions are calculated, when recalculation occurs, and limits on formulas.
OpenFormula defines rules for the evaluation of expressions as well as the functions and operators that appear in expressions.
Expressions in OpenFormula shall be evaluated by application of the following rules:
If an expression consists of a constant Number (5.3), a constant String (5.4), a Reference (5.8), constant Error (per section 5.12), the value of that type is returned.
If an expression consists of one or more operations, apply the operators in order of precedence and associativity as defined by Table 1 in 5.5 (Operators). Precedence of operators may be altered by the use of "(" (LEFT PARENTHESES, U+0028) and ")" (RIGHT PARENTHESES, U+0029) to group operators. Evaluate the operator as described in Operator and Function Evaluation, 3.2.3.
If an expression consists of a function call (5.6, 5.7), evaluate the function as described in Operator and Function Evaluation, 3.2.3.
If an expression consists of a named expression (5.11), the result of evaluating the named expression is returned.
If an expression consists of a QuotedLabel (5.10), AutomaticIntersection (5.10.6), or Array (5.13), its value is returned. Expression Syntax 5
Once evaluation has completed:
If the result is a Reference and a single nonreference value is needed, it is converted to the referenced value, using the rules of NonScalar Evaluation, 3.3, 1.2.
If the result an Array, for the display area, apply the rules of NonScalar Evaluation, 3.3, 1.1.
Operators and functions in OpenFormula shall be evaluated according to their definitions by applying the following rules:
The value of all expression arguments are computed. Exceptions to computation of all arguments are noted in a function's specification.
Note: The practice of computing all argument expressions is known as "eager" evaluation. The IF function is an example of a function that does not require computation of all arguments.
If an argument expression evaluates to Error, calculation of the operator or function may shortcircuit and return the Error if the function does not suppress error propagation as noted in the function's specification.
If an operator or function is passed a value of incorrect type, call the appropriate implicit conversion function to convert the value to the correct type. If conversion is not possible, generate an Error.
The function or operation is called with its argument expressions' results, and the result of the function or operation is the evaluation of the expression.
Nonscalar values passed as arguments to functions are evaluated by intersection or iteration.
Evaluation as an implicit intersection of the argument with the expression's evaluation position.
Inline Arrays
Element (0;0) of the
array is used in place of the array.
Note
1:
=ABS({3;4}) => ABS(3) // row
vector
=ABS({34}) => ABS(3) // column
vector
=ABS({3;46;8}) => ABS(3) //
matrix
={1;2;34;5;6} => 1 // simple display
References
If the target reference is a rowvector (Nx1), use the value at the intersection of the evaluation position's column and the reference's row.
Note
2:
in cell B2 : =ABS(A1:C1) => ABS(B1)
If
there is no intersection the result is #N/A or a more specific
Error value.
Note 3: in cell D4 : =ABS(A1:C1) => #N/A or a more specific Error value.
If the target reference is a columnvector (1xM), the value at the intersection of the evaluation position's row and the reference's column.
Note
4:
in cell B2 : =ABS(A1:A3) => ABS(A2)
in
cell D4 : =ABS(A1:A3) => #N/A or a more specific Error value.
Matrix evaluation.
If an expression is being evaluated in a cell flagged as a being part of a 'Matrix' (OpenDocument Part 3, 19.683 table:numbermatrixcolumnsspanned):
The portion of a nonscalar result to be displayed need not be coextensive with a specified display area. The portion of the nonscalar result to be displayed is determined by:
If the position to be displayed exists in the result, display that position.
If the nonscalar result is 1 column wide,
subsequent columns in the display area display the value in the
first column. This applies to
 scalars '3'
 singletons
'{3}'
 column vectors '{123}'
If the nonscalar result is 1 row high,
subsequent rows in the display area use the value of the first
row. This applies to
 scalars '3'
 singletons '{3}'

row vectors '{1;2;3}'
If none of the other rules apply #N/A
Note
5:
in matrix A1:B3 with ={1;23;45;6} : cell B2
contains 4. [Rule 2.1.1]
in matrix A1:B3 with ={135} :
cell B2 contains 3. [Rule 2.1.1 for row, and Rule 2.1.2 column]
in
matrix A1:B3 with ={2;4} : cell B2 contains 4. [Rule 2.1.3 for
row, and Rule 2.1.1 column]
in matrix A1:C4 with
={1;23;45;6} : cell C1,A4 contain #N/A. [Rule 2.1.4]
Note
6: if a value is not requested it is not displayed
in
matrix A1:B2 with ={1;23;45;6} : the value '6' is not displayed
because B3 is not part of the display matrix.
Calculations with nonscalar inputs are a generalization of (2.1).
When evaluating a formula in 'matrix'
mode, and a nonscalar value is passed to a function argument that
expects a scalar, the function is evaluated multiple times,
iterating over the nonscalar input(s) and putting the function
result into a matrix at the position corresponding to the input.
Unary/Binary operators, other than range and union, follow the
rules for scalar functions when passed nonscalar values.
Inline arrays and references are interchangeable.
Functions returning arrays are not eligible for implicit iteration. When evaluated in 'matrix' mode the {0;0}th element is used.
Note
7:
e.g. =SUM(INDIRECT({"A1";"A2"))
would produce the value in A1 when evaluated in array mode.
The result matrix is rectangular, sized with the maximum number of rows and columns from all nonscalar arguments.
Note
8:
={1;2}+{3;4;5} => {4;6;#N/A}
={1}+{1;2} =>
{2;3}
The result matrix is populated by extracting the corresponding value from each of the nonscalar arguments based on the following rules, and evaluating the function with that set of arguments.
If the argument data is a singleton array or a scalar the value is repeated for each evaluation.
Note
9:
= 1 + {1;2;34;5;6} => {2;3;45;6;7}
=
{1} + {1;2;34;5;6} => {2;3;45;6;7}
If the argument data is 1 column wide the value in the corresponding row is used to evaluate all columns in the result matrix.
Note
10:
= {12} + {10;2030;40} => {11;2132;42}
If the argument data is 1 row height the value in the corresponding column is used to evaluate all rows in the result matrix.
Note
11:
= {1;2} + {10;2030;40} => {11;2231;42}
If one argument data is 1 column wide and another argument data is 1 row height the value of the corresponding row respectively column is used to evaluate all elements in the result matrix.
Note
12:
={1;2} + {1020} => {11;1221;22}
If an argument is a 2d matrix the argument value in the position corresponding to the current output position is used if it is within range of the supplied argument, otherwise #N/A is used in the calculation.
Note
13:
=MID("abcd";{1;2};{1;2;3}) =>
{"a";"bc";#N/A}
A Formula Evaluator operates in an execution environment (a "host"). The behavior of the Formula Evaluator is parametrized by hostdefined properties and functions.
The following properties are hostdefined:
HOSTCASESENSITIVE: if true, text comparisons are casesensitive. This influences the operators =, <>, <, <=, >, and >=, as well as database query functions that use them. Note that the EXACT function is always casesensitive, regardless of this calculation setting.
HOSTPRECISIONASSHOWN: If true, calculations are performed using rounded values of those displayed; otherwise, calculations are performed using the precision of the underlying numeric representation.
Note: This does not impose a particular numeric model. Since implementations may use binary representations, this rounding may be inexact for decimal values.
HOSTSEARCHCRITERIAMUSTAPPLYTOWHOLECELL If true, the specified search criteria shall apply to the entire cell contents if it is a text match using = or <>; if not, only a subpart of the cell content needs to match the text.
HOSTAUTOMATICFINDLABELS: if true, row and column labels are automatically found.
HOSTUSEREGULAREXPRESSIONS: If true, regular expressions are used for character string comparisons and when searching.
HOSTUSEWILDCARDS: If true, wildcards question mark '?' and asterisk '*' are used for characterstring comparisons and when searching. Wildcards may be escaped with a tilde '~' character.
HOSTNULLYEAR: This defines how to convert a twodigit year into a fourdigit year. Each twodigit year value is interpreted as a year that equals or follows this year.
HOSTNULLDATE: Defines the beginning of the epoch; a numeric date of 0 equals this date.
HOSTLOCALE: The locale to be used for localedependent operations, such as conversion of text to dates, or text to numbers.
HOSTITERATIONSTATUS: If enabled, iterative calculations of cyclic references are performed.
HOSTITERATIONMAXIMUMDIFFERENCE: If iterative calculations of cyclic references are enabled, the maximum absolute difference between calculation steps that all involved formula cells must yield for the iteration to end and yield a result.
HOSTITERATIONSTEPS: If iterative calculations of cyclic references are enabled, the maximum number of steps iterations that are performed if the results are not within HOSTITERATIONMAXIMUMDIFFERENCE.
The function HOSTREFERENCERESOLVER(Reference) is implementation defined. This function takes as input a Unicode string containing a Reference according to section 4.8 and returns a resolved value.
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.
Evaluators which claim to support “basic limits” shall support at least the following limits:
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.
at least 30 parameters per function when the function prototype permits a list of parameters.
permit strings of ASCII characters of up to 32,767 (2^151) characters.
support at least 7 nesting levels of functions.
All values defined by OpenFormula have a type. OpenFormula defines Text 4.2, Number 4.3, Complex Number 4.4, Logical 4.5, Error 4.6, Reference 4.8, ReferenceList 4.9 and Array 4.10 types.
A Text value (also called a string value) is a Character string as specified in [CharModel].
A text value of length zero is termed the empty string.
Index positions in a text value begin at 1.
Whether or not Unicode Normalization [UTR15] is performed on formulas, formula results or user inputs is implementationdefined. Some functions defined in OpenFormula are labeled as "normalizationsensitive", meaning that the results of the formula evaluation may differ depending on whether normalization occurs, and which normalization form is used. Mixing operands of different normalization forms in the same calculation is undefined.
A number is a numeric value.
Numbers shall be able to represent fractional values (they shall not be limited to only integers). Evaluators may implement Number with a fixed or with a variable bit length. A cell with a constant numeric value has the Number type.
Implementations typically support many subtypes of Number, including Date, Time, DateTime, Percentage, 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.
Note: This specification does not require that specific subtypes be distinguishable from each other, or that the subtype be tracked, but in practice most implementations do such tracking because requiring users to manually format every cell appropriately becomes tedious very quickly. Automatically determining the most likely subtype is especially important for a good user interface when generating OpenDocument format, since some subtypes (such as date, time, and currency) are stored in a different manner depending on their subtype. Thus, this specification identifies some common subtypes and identifies those subtypes where relevant in some function definitions, as an aid to implementing good user interfaces. Many applications vary in the subtype produced when combining subtypes (e.g., what is the result when percentages are multiplied together), so unless otherwise noted these are unspecified. Typical implementations try to heuristically determine the "right" format for a cell when a formula is first created, based on the operations in the formula. Users can then override this format, so as a result the heuristics are not important for data exchange (and thus outside the scope of this specification).
All Number subtypes shall yield TRUE for the ISNUMBER function.
Time is a subtype of Number.
Time is represented as a fraction of a day.
Date is a subtype of Number.
Date is represented by an integer value.
A serial date is the expression of a date as the number of days elapsed from a start date called the epoch.
Evaluators shall support all dates from 19040101 through 99991231 (inclusive) in calculations, should support dates from 18991230 through 99991231 (inclusive) and may support a wider date range.
Note 1: Using expressions that assume serial numbers are based on a particular epoch may cause interoperability issues.
Evaluators shall support positive serial numbers. Evaluators may support negative serial numbers to represent dates before an epoch.
Note 2: It is implementationdefined if the year 1900 is treated as a leap year.
Note 3: Evaluators that treat 1900 as a nonleap year can use the epoch date 18991230 to compensate for serial numbers that originate from evaluators that treat 1900 as a leap year and use 18991231 as an epoch date.
DateTime is a subtype of Number. It is a Date plus Time.
A percentage is a subtype of Number that may be displayed by multiplying the number by 100 and adding the sign “%” or with other formatting depending upon the number format assigned to the cell where it appears.
A currency is a subtype of Number that may appear with or without a currency symbol or with other formatting depending upon the number format assigned to the cell where it appears.
Applications may have a Logical type distinct from both Number and Text (see 4.5 Logical (Boolean)), but Logical values may also be represented by the Number type using the values 1 (True) and 0 (False). (see 8.2 Distinct Logical for details)
A complex number (sometimes also called an imaginary number) is a pair of real numbers including a real part and an imaginary part. In mathematics, complex numbers are often written as x + iy, where x (the real part) and y (the imaginary part) are real numbers and i is . A complex number can also be written as re^{i}^{θ} = rcos(θ) + irsin(θ), where r is the modulus of the complex number (a real number) and θ is the argument or phase (a real number representing an angle in radians).
A complex number may, but need not be, represented as a Number or Text. The results of the functions ISNUMBER() 6.13.22 and ISTEXT() 6.13.25 are implementationdefined when applied to a complex number.
Functions and operators that accept complex numbers shall accept Text values as complex numbers (Conversion to Complex Number 6.3.10), as well as Numbers that are not complex numbers.
Note 1: IMSUM("3i";4) will produce the same result as COMPLEX(4;3).
Note 2: 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).
Applications may have a Logical type distinct from both Number and Text, but Logical values may also be represented by the Number type using the values 1 (True) and 0 (False) (see 4.3.7 Logical (Number)). (see 8.2 Distinct Logical for details)
An Error is one of a set of possible error values. Implementations may have many different error values (see 5.12), but one error value in particular is distinct: #N/A, the result of the NA() function. Users may choose to enter some data values as #N/A, so that this error value propagates to any other formula that uses it, and may test for this using the function ISNA().
Functions and operators that receive one or more error values as an input shall produce one of those input error values as their result, except when the formula or operator is specifically defined to do otherwise.
In an OpenDocument document, if an error value is
the result of a cell computation it shall
be stored as if it was a string. That is, the office:valuetype
(OpenDocument Part 3, 19.389) of an error value is string
;
if the computed value is stored, it is stored in the attribute
office:stringvalue
(OpenDocument Part 3, 19.383).
Note: This does not change an Error into a string type (since the Error will be restored on recalculation); this enables applications which cannot recalculate values to display the error information.
An empty cell is neither zero nor the empty string, and an empty cell can be distinguished from cells containing values (including zero and the empty string). An empty cell is not the same as an Error, in particular, it is distinguishable from the Error #N/A (not available).
A cell position is the location of a single cell at the intersection of a column and a row.
A cell strip consists of cell positions in the same row and in one or more contiguous columns.
A cell rectangle consists of cell positions in the same cell strips of one or more contiguous rows.
A cell cuboid consists of cell positions in the same cell rectangles of one or more contiguous sheets.
A reference is the smallest cuboid that (1) contains specificallyidentified cell positions and/or specificallyidentified complete columns/rows such that (2) removal of any cell positions either violates condition (1) or does not leave a cuboid.
Cell positions in a cell cuboid/rectangle/strip can resolve to empty cells (section 4.7).
The definitions of specific operations and functions that allow references as operands and parameters stipulate any particular limitations there are on forms of references and how empty cells, when permitted, are interpreted.
A reference list contains 1 or more references, in order. A reference list can be passed as an argument to functions where passing one reference results in an identical computation as an arbitrary sequence of single references occupying the identical cell range.
Note 1: For example, SUM([.A1:.B2]) is identical to SUM([.A1]~[.B2]~[.A2]~[.B1]), but COLUMNS([.A1:.B2]), resulting in 2 columns, is not identical to COLUMNS([.A1]~[.B2]~[.A2]~[.B1]), where iterating over the reference list would result in 4 columns.
A reference list cannot be converted to an array.
Note 2: For example, in array context {ABS([.A1]~[.B2]~[.A2]~[.B1])} is an invalid expression, whereas {ABS([.A1:.B2])} is not.
Passing a reference list where a function does not expect one shall generate an Error. Passing a reference list in array iteration context to a function expecting a scalar value shall generate an Error.
An array is a set of rows each with the same number of columns that contain one or more values. There is a maximum of one value per intersection of row and column. The intersection of a row and column may contain no value.
Many functions require a type or a set of types with special properties, and/or process them specially. For example, a "Database" requires headers that are the field names. These specialized types are called pseudotypes.
A Scalar value is a value that has a single value. A reference to more than one cell is not a scalar (by itself), and is converted to one as described below. An array with more than one element is not a scalar. The types Number (including a complex number), Logical, and Text are scalars.
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
An integer is a subtype of Number that has no fractional value. An integer X is equal to INT(X). Division of one integer by another integer may produce a noninteger.
TextOrNumber is a value that is either a Number or Text.
A basis is a subtype of Integer that specifies the daycount convention to be used in a calculation.
This standard defines five daycount conventions, corresponding to widely used current and historical accounting conventions. Each of these five bases defines two things:
How to calculate the number of days between two dates, date1 and date2.
How to calculate the number of days in each year between two dates, date1 and date2.
Historically daycount bases used the naming convention x/y, which indicated that the convention assumed x days per month and y days per year. These names are given for reference purposes.
Date Basis 
Historical Name 
Day Count 
Days in Year 

0 
US (NASD) 30/360 
Procedure A, 4.11.7.3 
Procedure D, 4.11.7.6 
1 
Actual/Actual 
Procedure B, 4.11.7.4 
Procedure E, 4.11.7.7 
2 
Actual/360 
Procedure B, 4.11.7.4 
Procedure D, 4.11.7.6 
3 
Actual/365 
Procedure B, 4.11.7.4 
Procedure F, 4.11.7.8 
4 
European 30/360 
Procedure C, 4.11.7.5 
Procedure D, 4.11.7.6 
The daycount procedures are expressed using notations defined as:
day(date) returns the day of the month for the given date value, an integer from 1 to 31
month(date) returns the month of a given date value, an integer from 112
year(date) returns the year of the given date value
truncate(date) truncates any fractional (hours, minutes, seconds) of a date value and returns the whole date portion.
Binary comparison operators date1>date2 and date1 == date2
isleapyear(year) returns true if year is a leap year, otherwise false.
Note: Some of the day count procedures use intermediate results that contain counterfactual dates, such as February 30^{th}. This is not an error. The above functions work on such dates as well, e.g., day(February 30^{th}) == 30.
truncate(date1), truncate(date2)
If date1==date2 return 0
If date1> date2, then swap the values of date1 and date2.
If day(date1)==31 then subtract 1 day from date1
If day(date1)==30 and day(date2)==31 then subtract 1 day from date2
If both date1 and date2 are the last day of February, change date2 to the 30th of the month.
If date1 is the last day of February, change it to the 30th of the month.
Return (year(date2)*360 + month(date2)*30 + day(date2))  (year(date1)*360 + month(date1)*30 + day(date1)).
truncate(date1), truncate(date2)
If date1> date2, then swap the values of date1 and date2.
Return the actual numbers of days between date1 and date2, inclusive of date1, but not inclusive of date2.
truncate(date1), truncate(date2)
If date1==date2 return 0
If date1> date2, then swap the values of date1 and date2.
If day(date1)==31 then subtract 1 from date1
If day(date2)==31 then subtract 1 from date 2
Return (year(date2)*360 + month(date2)*30 + day(date2))  (year(date1)*360 + month(date1)*30 + day(date1)).
Return 360
Evaluate A: year(date1) != year(date2)
Evaluate B: year(date2)!=year(date1)+1
Evaluate C: month(date1) < month(date2)
Evaluate D: month(date1) == month(date2)
Evaluate E: day(date1) < day(date2)
Evaluate F: (A and B) or (A and C) or (A and D and E)
If F is true then return the average of the number of days in each year between date1 and date2, inclusive.
Otherwise, if A and isleapyear(year(date1)) then return 366
Otherwise, if a February 29 occurs between date1 and date2 then return 366
Otherwise, if date2 is a February 29, then return 366
Otherwise return 365
Return 365
A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents.
A reference to an empty cell is interpreted as the numeric value 0.
A matching expression can be:
A 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. Empty cell 4.7, = 6.4.7
For <>, if the value is empty it matches nonempty cells. <> 6.4.8
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 hostdefined property HOSTSEARCHCRITERIAMUSTAPPLYTOWHOLECELL 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 the hostdefined property HOSTSEARCHCRITERIAMUSTAPPLYTOWHOLECELL is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria.
A database is a rectangular organized set of data. Any database has a set of one or more fields that determine the structure of the database. A database has a set of zero or more records with data, and each record contains data for every field (though that field may be empty).
Evaluators shall support the use of ranges as databases if they support any database functions. The first row of a range is interpreted as a set of field names.
Note: Field names of type Text and unique without regard to case enhance the interoperability of data. It is also a common expectation that rows following the first row of data are data records that correspond to field names in the first row.
A single cell containing text can be used as a database; if it is, it is a database with a single field and no data records.
A field is a value that selects a field in a database; it is also called a field selector. If the field selector is Text, it selects the field in the database with the same name.
Evaluators should match the database field name caseinsensitively.
If a field selector is a Number, it is a positive integer and used to select the fields. Fields are numbered from left to right beginning with the number 1.
All functions that accept a field parameter shall, when evaluated, return an Error if the selected field does not exist.
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 criteria row shall match.
A reference to an empty cell is interpreted as the numeric value 0.
Expressions are matched as per 4.11.7.8 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 6.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 shall be one of these two processing orders. If processing rowatatime, the sequence shall be produced by processing each row in turn, from smallest to largest column value (e.g., A1, B1, C1). If processing columnatatime, the sequence shall be produced by processing each column at a time, from the smallest to the largest row value (e.g., A1, A2, A3).
Any represents a value of any type defined in this standard, including Error values.
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 as a grammar in BNF notation, a formula is specified:
Formula ::= Intro? Expression
Intro ::= '=' ForceRecalc?
ForceRecalc ::= '='
The primary component of a formula is an Expression. Formulas are composed of Expressions, which may in turn be composed from other Expressions.
Expression ::=
(
Number 
String 
Array 
PrefixOp Expression 
Expression PostfixOp 
Expression InfixOp Expression 
'(' Expression ')' 
FunctionName Whitespace* '(' ParameterList ')' 
Reference 
QuotedLabel 
AutomaticIntersection 
NamedExpression 
Error
) Whitespace*
SingleQuoted ::= "'" ([^']  "''")+ "'"
Constant numbers are written using '.' (FULL STOP, U+002E) dot as the decimal separator. Optional "E" or "e" denotes scientific notation. Syntactically, negative numbers are positive numbers with a prefix "" (HYPHENMINUS, U+002D) operator. A constant number is of type Number.
Number ::= StandardNumber 
'.' [09]+ ([eE] [+]? [09]+)?
StandardNumber ::= [09]+ ('.' [09]+)? ([eE] [+]? [09]+)?
Evaluators should be able to read the Number format, which accepts a decimal fraction that starts with decimal point '.' (FULL STOP, U+002E), without a leading zero. Evaluators shall write numbers only using the StandardNumber format, which requires a leading digit, and shall not write numbers with a leading '.' (FULL STOP, U+002E).
Constant strings are surrounded by doublequote characters (QUOTATION MARK, U+0022); a literal doublequote character '"' (QUOTATION MARK, U+0022) as string content is escaped by duplicating it. A constant string is of type Text.
String ::= '"' ([^"#x00]  '""')* '"'
Operators are functions with one or more parameters.
PrefixOp ::= '+'  ''
PostfixOp ::= '%'
InfixOp ::= ArithmeticOp  ComparisonOp  StringOp  ReferenceOp
ArithmeticOp ::= '+'  ''  '*'  '/'  '^'
ComparisonOp ::= '='  '<>'  '<'  '>'  '<='  '>='
StringOp ::= '&'
There are three predefined reference operators: reference intersection, reference concatenation, and range. The result of these operators may be a 3dimensional range, with frontupperleft and backlowerright corners, or even a list of such ranges in the case of cell concatenation.
ReferenceOp ::= IntersectionOp  ReferenceConcatenationOp  RangeOp
IntersectionOp ::= '!'
ReferenceConcatenationOp ::= '~'
RangeOp ::= ':'
Table 1  Operators defines the associativity and precedence of operators, from highest 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. Note: 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]%). 
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  have 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 
Note 1: Prefix “” has a higher precedence than “^”, “^” is leftassociative, and reference intersection has a higher precedence than reference union.
Note 2: Prefix “+” and ““ are defined to be rightassociative. However, note that typical applications which implement at most the operators defined in this specification (as specified) may implement them as leftassociative, because the calculated results will be identical.
Note 3: Precedence can be overridden by using parentheses, so "=2+3*4" computes to 14 but "=(2+3)*4" computes 20. Implementations should retain "unnecessary" parentheses and white space, since these are added by people to improve readability.
Functions are called by name, followed by parentheses surrounding a list of parameters. Parameters are separated using the semicolon ';' (SEMICOLON, U+003B) character:
FunctionName ::= LetterXML (LetterXML  DigitXML 
'_'  '.'  CombiningCharXML)*
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 need 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 shall include a prefix in such function names to identify the original definer of the function's semantics. When the origin of a function cannot be determined, producers may omit a prefix. Producers may use the prefix to differentiate between different definition types. Evaluators that do not support a function should compute its result as some Error value other than #N/A.
Note: Examples of implementationdefined functions include extension functions included with an implementation, userdefined functions written by users, and 3rd party functions distributed in libraries.
Note: Examples of such names include COM.MICROSOFT.CUBEMEMBER, ORG.OPENOFFICE.STYLE, ORG.GNUMERIC.RANDRAYLEIGH, and COM.LOTUS.V98.FOO.
Evaluators should avoid defining 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 #N/A.
References refer to a specific cell or set of cells. The syntax for a constant reference:
Reference ::= '[' (Source? RangeAddress)  ReferenceError ']'
RangeAddress ::=
SheetLocatorOrEmpty '.' Column Row (':' '.' Column Row )? 
SheetLocatorOrEmpty '.' Column ':' '.' Column 
SheetLocatorOrEmpty '.' Row ':' '.' Row 
SheetLocator '.' Column Row ':' SheetLocator '.' Column Row 
SheetLocator '.' Column ':' SheetLocator '.' Column 
SheetLocator '.' Row ':' SheetLocator '.' Row
SheetLocatorOrEmpty ::= SheetLocator  /* empty */
SheetLocator ::= SheetName ('.' SubtableCell)*
SheetName ::= QuotedSheetName  '$'? [^\]\. #$']+
QuotedSheetName ::= '$'? SingleQuoted
SubtableCell ::= ( Column Row )  QuotedSheetName
ReferenceError ::= "#REF!"
Column ::= '$'? [AZ]+
Row ::= '$'? [19] [09]*
Source ::= "'" IRI "'" "#"
CellAddress ::= SheetLocatorOrEmpty '.' Column Row /* Not used directly */
References always begin with '[' (LEFT SQUARE BRACKET, U+005B); this disambiguates cell addresses from function names and named expressions. SheetNames include singlequote“'” (APOSTROPHE, U+0027) characters by doubling them and having the entire name surrounded by singlequotes. Column labels shall be in uppercase. The syntax supports wholerow and wholecolumn references. A reference is of type Reference.
A ReferenceError provides information that a formula evaluates to an Error because of a particular reference having been invalidated by actions that occurred after the formula was validly created.
Columns are named by a sequence of one or more uppercase letters AZ (U+0041 through U+005A). Columns are named A, B, C, ... X, Y, Z, AA, AB, AC, ... AY, AZ, BA, BB, BC, ... ZX, ZY, ZZ, AAA, AAB, AAC, AAZ, ABA, ABB, and so on.
If a RangeAddress does not contain a Column element or does not contain a Row element, it specifies a cell rectangle (4.8 Reference). If it contains Row elements, the cell rectangle starts on the first column and ends on the last column the evaluator supports. If it contains Column elements, the cell rectangle starts on the first row and ends on the last row the evaluator supports.
If in a RangeAddress the first part (left of ':' colon) contains a SheetLocator and the second part (right of ':' colon) does not contain a SheetLocator, the second part inherits the SheetLocator from the first part.
If a RangeAddress contains two different SheetLocators, it specifies a cell cuboid (4.8 Reference).
If a RangeAddress contains no SheetLocator, the current sheet local to the position where the expression is evaluated is referred.
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, evaluators should choose the leftmost pair.
The optional Source expresses that the reference is to sheets and/or cells in a different location (possibly in a samedocument fragment) from that for the formula in which the reference occurs. The optional Source is also used for locating Named Expressions (section 5.11).
The IRI portion of Source shall be an IRI reference [RFC3987] conforming to the general syntax IRIreference rule (section 2.2 of [RFC3987]) after each pair of consecutive singlequote characters (APOSTROPHE, U+0027) is replaced by one single singlequote character.
Note: The escaping of singlequotes as paired singlequotes is because the IRI is enclosed in single quote characters of the Source.
Resolution of the [RFC3987] IRI reference is hostdefined behavior. 3.4
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 (OpenDocument Part 3, 19.660) in the elements
<table:labelrange>
(OpenDocument Part 3, 9.4.9) with attribute table:orientation
(OpenDocument Part 3, 19.690.4) set to column
)
are searched for the string content of QuotedLabel
(without the quotes). If found, the corresponding column's range of
the data cell range of the table:datacellrangeaddress
attribute (OpenDocument Part 3, 19.612) is taken as a reference. If
not found, the cells defined in row label ranges (attribute
table:orientation
set to row
) are searched and if
found the corresponding row's range of the data cell range is taken.
Label ranges of the current formula's sheet take precedence over
label ranges of other sheets if a name occurs in both.
For a QuotedLabel where no defined label is found, an automatic lookup is performed on the sheet where the formula cell resides, if that document setting is enabled (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 square cells. For the direction, during the run two independent match positions are remembered each time Distance is smaller than a previous Distance: Match2 for positions right of and/or below the formula position (FormulaColumn < MatchColumn  FormulaRow < MatchRow), Match1 for all others (not right of and not below). Match1 also holds the very first match, in case there is only one match or all matches are somewhere below or right of the formula cell. After having found the smallest distances the conditions are:
If Match1 has the smallest distance, that match is taken.
Else, Match2 (right and/or below) has the smallest or an equal distance:
A match to the upper left (FormulaColumn >= Match1Column && FormulaRow >= Match1Row) takes precedence over matches to other directions.
Else, if there is no match to the upper left:
If Match1 is somewhere right of the formula cell (FormulaColumn < Match1Column) it was the first match found in columnwise lookup.
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.
Else Match2 is below the formula cell and Match1 is taken.
Else (Match1 not right of the formula cell => two matches below or below and right) the match with the smallest distance is taken.
If the resulting cell is below or above another cell containing Text a row label is assumed, else a column label is assumed.
Note: Use of automatically looked up column or row labels in expressions impairs interoperability.
For the reference resulting from a single QuotedLabel an implicit intersection is generated if the operator or function with which it is used expects a scalar value. The intersection is generated with the current formula's cell position, thus for a column label an intersection is generated with the formula cell's row, for a row label with the formula cell's column.
When passed as a nonscalar argument (e.g. Array or NumberSequence) to a function, an automaticallylookedup column or row label (not defined label range) is converted to an automatic range reference that is adjusted each time the formula is interpreted. The range is generated from the column below a column label, or the row to the right of a row label, constructed by encompassing contiguous nonempty cells. An empty cell interrupts contiguousness, one empty cell directly below a column label cell or to the right of a row label cell is ignored.
Table 2  Automatic Range
Row 
Data 
Expression 
Result 
Comment 

1 
Label 
=SUM('Label') 
3 
Empty cell in row 2 is skipped (two empty cells in row 2 and 3 would not be skipped and would stop the automatic range), empty cell in row 5 stops the automatic range. 
2 


3 
1 

4 
2 

5 


6 
8 

7 


8 
32 
Table 3  Automatic Range
Row 
Data 
Expression 
Result 
Comment 

1 
Label 
=SUM('Label') 
15 
Empty cell in row 2 is skipped, empty cell in row 7 stops the automatic range. 
2 


3 
1 

4 
2 

5 
4 

6 
8 

7 


8 
32 
An automatic intersection may be used to identify the intersection of two quoted labels. Note that this is different from the IntersectionOp, which takes two references instead of two labels:
AutomaticIntersection ::= QuotedLabel Whitespace* '!!' Whitespace* QuotedLabel
In an automatic intersection, one of the labels identifies a row, the other a column; they may be in either order. Each QuotedLabel is looked up as defined above under "Lookup of Defined Labels" and "Automatic Lookup of Labels". If two data cell ranges are found, the intersection of the column's data cell range and the row's data cell range is generated. If the intersection result is not exactly one cell, an Error is generated.
A NamedExpression references another expression, possibly in a completely different spreadsheet or any other type of document that can be imported into a spreadsheet.
NamedExpression ::= SimpleNamedExpression 
SheetLocalNamedExpression  ExternalNamedExpression
SimpleNamedExpression ::= Identifier 
'$$' (Identifier  SingleQuoted)
SheetLocalNamedExpression ::=
QuotedSheetName '.' SimpleNamedExpression
ExternalNamedExpression ::=
Source (SimpleNamedExpression  SheetLocalNamedExpression)
Evaluators supporting named expressions shall support Simple Named Expressions that are global to all the sheets in a (spreadsheet) document in the current document. This is a named expression without a Source, QuotedSheetName, or SubtableCell. The type of a named expression is the type of the value that the named expression returns.
Named expressions are caseconsistent, meaning that matching is done caseinsensitive and identifiers can not differ ONLY in their case. Evaluators should write identifiers with identical case in all locations.
Evaluators may support Sheetlocal Named Expressions that are local (attached) to individual sheets. In that case, a nonempty QuotedSheetName can be used to reference a sheetspecific named expression. The most specific named expression for a given expression is used. If the QuotedSheetName is empty, the search for the named expression begins with the current sheet, then up through the container(s) of the sheet (the same is true if the QuotedSheetName rule fragment is not included at all). If there is a nonempty QuotedSheetName, search begins with that named sheet, then up through its container(s) for the given name.
Note: There is no syntax for referencing a named expression without first looking at the current sheet's named expressions; where this is a problem, a user can define a blank sheet and reference that sheet as the starting location for finding the named expression.
If a sheetname is not empty, it shall be quoted using the singlequote character “'” (APOSTROPHE, U+0027). While both Source and QuotedSheetName can begin with the singlequote character, they are distinguished: after the closing singlequote character, a nonempty source shall have the '#' (NUMBER SIGN, U+0023) character as the next nonwhitespace character; a nonempty sheetname shall be followed by the '.' (FULL STOP, U+002E) character as the next nonwhitespace character.
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.
Identifier ::= ( LetterXML
(LetterXML  DigitXML  '_'  CombiningCharXML)* )
 ( [AZaz]+[09]+ )
 ([Tt][Rr][Uu][Ee])  ([Ff][Aa][Ll][Ss][Ee])
Evaluators shall support the Error named #N/A. Evaluators may support other Errors. Evaluators may allow entry of errors directly, parse them and recognize them as Errors. Functions shall propagate Errors unless stated otherwise.
Error names shall have the following syntax:
Error ::= '#' [AZ09]+ ([!?]  ('/' ([AZ]  ([09] [!?]))))
Specific Errors are indicated by their corresponding names.
Table 4 is a list of Errors that are used by several existing implementations.
Table 4  Possible Other Constant Error Names
Name 
Comments 

#DIV/0! 
Attempt to divide by zero, including division by an empty cell. ERROR.TYPE of 2 6.13.11 
#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. 
Evaluators may implement other Errors.
An unknown Error name shall be mapped into an Error supported by the evaluator when read (e.g., the application's equivalent of #NAME?), though an evaluator may warn the user if this has or will take place. It is desirable to preserve the original specific Error name when writing an Error back out, where possible, but for Errors other than #N/A evaluators may write a different Error for a formula than they did when reading it. Whitespace shall not be included in an Error name.
Evaluators should use a humancomprehensible name, not a numeric id, for Error names they write.
Inline arrays are enclosed with curly braces. Inside, they contain one or more rows, with each row separated by a row separator:
Array ::= '{' MatrixRow ( RowSeparator MatrixRow )* '}'
MatrixRow ::= Expression ( ';' Expression )*
RowSeparator ::= ''
Evaluators that support inline arrays shall accept a matrix with one or more rows, each with one or more columns, with the same number of columns in each row, with constant values for each expression. Evaluators that do not support inline arrays, or cannot support a particular use permitted by this syntax, should compute an Error value for such arrays. An inline array is of type Array.
Note: Expression authors should be aware that use of Expression other than constant Number or constant String may impair interoperability.
Whitespace ::= #x20  #x09  #x0a  #x0d
For calculation purposes, whitespace is ignored unless it is inside the contents of string constants or text surrounded by single quotes. Evaluators shall ignore any whitespace characters before and/or after any operators, constant numbers, constant strings, constant errors, inline arrays, parentheses used for controlling precedence, and the closing parenthesis of a function call. Whitespace shall be ignored following the initial equal sign(s). Whitespace shall be ignored just before a function name, but whitespace shall not separate a function name from its initial opening parenthesis. Whitespace shall not be used in the interior of a terminating grammar rule (a rule that references no other rule other than character sets, internally or externallydefined), unless specifically permitted by the terminating grammar rule, since these rules define the lexical properties of a component. Evaluators shall not write formulas with whitespace embedded in any unquoted identifier, constant Number, or constant Error. Evaluators shall treat SPACE (U+0020), CHARACTER TABULATION (U+0009), LINE FEED (U+000A), and CARRIAGE RETURN (U+000D) as whitespace characters.
An embedded line break shall be represented by a single LINE FEED character (U+000A), not by a carriage returnlinefeed pair. When embedded in an XML attribute the linefeed character is represented as “
”.
Evaluators should retain whitespace entered by the original formula creator and use it when saving or presenting the formula, and should not add additional whitespace unless directed to do so during the process of editing a formula.
OpenFormula defines commonly used operators and functions.
Function names ignore case. Evaluators should write function names in all uppercase letters when writing OpenFormula formulas.
Unless otherwise noted, if any value being provided is an Error, the result is an Error; if more than one Error is provided, one of them is returned (evaluators should return the leftmost Error result).
For every function or operator, the following are defined in this specification:
Name: The function/operator name.
Summary: One sentence briefly describing the function or operator.
Syntax:
Parameter names are shown in order, with each parameter prefixed by the type or pseudotype of that parameter. If the type has multiple names separated by “”, then any of those types are permitted.
A { ... } indicates a list of zero or more parameters, separated by the function parameter separator character.
A { ... } followed by a superscripted + indicates a list of one or more parameters, separated by the function parameter separator character.
Components surrounded by [ ... ] are optional. Optional components may be omitted.
An optional parameter followed by the = symbol has the default value given after the equal sign.
Parameters are separated with a semicolon (";"), as per the OpenFormula expression syntax 5.6.
When a function is given a value of a different type, the parameters are first converted using the implicit conversion rules before the function operates on its parameters.
Evaluators may extend functions by permitting fewer or additional parameters, which documents may use. Extended functions may result in a lack of interoperability.
Returns: Return type (e.g., Number, Text, Logical, Reference).
Constraints: A description of constraints, in addition to the constraints imposed by the parameter types. If there are no additional constraints beyond those imposed by the parameter types, this is "None". If a constraint is not met, the function/operator shall return an Error unless otherwise noted.
Semantics: This text describes what the function/operator does.
If a parameter is a pseudotype, but the provided value fails to meet the requirements for that type, the behavior is implementationdefined.
Note: Functions and operators are defined by mathematical formulas or by an OpenFormula formula. Formulas define the correct result, and not the algorithm for calculation. Since computing systems have limited precision and range of numbers, some functions cannot or should not be naively implemented as their formulas suggest. This specification defines the mathematically correct answer, and allows implementors to choose the best algorithm that will meet that definition.
Comment: Explanatory comment.
See also A list of related operators and functions.
The implicit conversion operators omit many of these items, e.g., the syntax (since there is none).
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:
Number, Logical, or Text, return the value.
reference to a single cell: obtain the value of the referenced cell, and return that value.
reference to more than one cell: do an implied intersection, 6.3.3, to determine which single cell to use, then handle as a reference to a single cell.
In some cases a reference to a single cell is needed, but a reference to multiple cells is provided. In this case an "implied intersection" is performed. To perform an implied intersection:
Compute the union of cells contained in the current row and current column of the formula being computed.
Intersect this with the provided reference to multiple cells
If a single cell is referenced; return it; otherwise, return an Error.
A ForceArray attribute forces calculation of the argument's expression into nonscalar array mode. This means that no implied intersection is performed, instead where a reference to a single cell is expected and multiple cells are provided, iteration over the multiple cells is performed and results are stored in an array that is passed on.
See also NonScalar Evaluation 3.3
If the expected type is Number, then if the value is of type:
Number, return it.
Logical, return 0 if FALSE, 1 if TRUE.
Text: The specific conversion is implementationdefined; an evaluator may return 0, an Error value, or the results of its attempt to convert the Text value to a Number (and fall back to 0 or Error if it fails to do so). Evaluators may apply VALUE 6.13.34 or some other function to do this conversion, should they choose to do so. Conversion depends on the actual locale the application runs in, especially if group or decimal separators are involved.
Reference: If the reference covers more than one cell, do an implied intersection to determine which cell to use. Then obtain the value of the single cell and perform the rules as above. If the calculation setting “precisionasshown” is true, then convert the number to the closest possible representation of the displayed number. If the cell is empty (blank), use 0 (zero) as the value. Evaluators may choose to convert references to Text in a different manner than they handle converting embedded Text to a Number.
If the expected type is 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 are 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 value is:
Number that is not complex, use the Number with 0 as the imaginary part.
Text, attempt to convert to complex number using VALUE 6.13.34. If it is a number that is not complex, use it. If the text matches one of these patterns, accept it:
([+]?Number
[+])?Number[ij]
[+]?Number[ij]
Logical, convert to Number and then handle as Number.
reference: Convert to Scalar 6.3.2, then use the rules above. If the reference is to an empty cell, consider it equal to 0.
If the expected type is ComplexSequence, then if value is of type:
Number, Text, or Logical, handle as Conversion to Complex Number (creating a sequence of length 1).
Reference, create a sequence of complex numbers from the values of the referenced cells that only includes the values of type Number, Text, and Error. Empty cells are not included in a complex number sequence. If the Logical type is a distinguished type from the Number type, it should not be included in the sequence of numbers; if the Logical type is not a distinguished type, then such values will (of course) be included in the number sequence.
If the expected type is Logical, then if value is of type:
Number, return TRUE for nonzero and FALSE for 0.
Text: The specific conversion is implementationdefined; an evaluator may return FALSE, an Error value, or the results of its attempt to convert the Text value (ignoring case) to a Logical value (and fall back to FALSE or Error if it fails to do so). Conversion depends on the actual locale the evaluator runs in.
Logical, return it.
Reference, convert to scalar and then perform as above. If the reference is to an empty cell, consider it FALSE.
If the expected type is LogicalSequence, then if value is of type:
Number or Logical, handle as Conversion to Logical (creating a sequence of length 1).
Reference, create a sequence of logical values from the values of the referenced cells that only includes the values of type Logical and Error. If the Logical type is not a distinguished type, then include values of type Number, converting each to a Logical value as described in Conversion to Logical. Empty cells are not included in a LogicalSequence.
If the expected type is Text, then if value is of type:
Number, transform into Text (with no whitespace).
Text, return it.
Logical, return "TRUE" if it is true and "FALSE" if it is false.
Reference: perform conversion to scalar. If the referenced cell is empty, treat as an empty string (a text value with length 0). Then perform as above.
If the expected type is the pseudotype DateParam, then if value is of type:
Number, return it.
Text, pass to DATEVALUE 6.10.4, and if nonError, return it. If DATEVALUE would return an Error, an evaluator may attempt to convert to a Number in other ways (such as by calling VALUE 6.13.34); this is implementationdefined. If the evaluator cannot convert to Number, it returns an Error.
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:
Number, return it.
Text, pass to TIMEVALUE 6.10.18, and if nonError, return it. If TIMEVALUE would return an Error, an evaluator may attempt to convert to a Number in other ways (such as by calling VALUE 6.13.34); this is implementationdefined. If the evaluator cannot convert to Number, it returns an Error.
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 by their frequency of use. That frequency of use has lead to the colloquial terminology, standard operators.
Summary: Add two numbers.
Syntax: Number Left + Number Right
Returns: Number
Constraints: None
Semantics: Adds numbers together.
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
Returns: Number
Constraints: None
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
Returns: Number
Constraints: None
Semantics: Multiplies numbers together.
See also Infix Operator "+" 6.4.2, Infix Operator "/" 6.4.5
Summary: Divide the first number by the second.
Syntax: Number Left / Number Right
Returns: Number
Constraints: None
Semantics: Divides numbers. Dividing by zero returns an Error.
See also Infix Operator "" 6.4.3, Infix Operator "*" 6.4.4
Summary: Exponentiation (Power).
Syntax: Number Left ^ Number Right
Returns: Number
Constraints: NOT(AND(Left=0; Right=0)); Evaluators may evaluate expressions where OR(Left != 0; Right != 0) evaluates to a nonError value.
Semantics: Returns POWER(Left, Right).
See also Infix Operator "*" 6.4.4, AND 6.15.2, NOT 6.15.7, POWER 6.16.46
Summary: Report if two values are equal
Syntax: Scalar Left = Scalar Right
Returns: Logical
Constraints: None
Semantics:
Returns TRUE if two values are equal. If the values differ in type,
return FALSE. If the values are both Number, return TRUE if they are
considered equal, else return FALSE. If they are both Text, return
TRUE if the two values match, else return FALSE. For Text values, if
the calculation setting HOSTCASESENSITIVE
is false
, text is compared but
characters differencing only in case are considered equal. If they
are both Logicals, return TRUE if they are identical, else return
FALSE. Error values cannot be compared to a constant Error
value to determine if that is the same Error value.
Evaluators may approximate and test equality of two numeric values with an accuracy of the magnitude of the given values scaled by the number of available bits in the mantissa, ignoring some least significant bits and thus providing compensation for not exactly representable values.
The result of “1=TRUE()” is FALSE for evaluators that implement a distinct Logical type and TRUE if they don't.
See also Infix Operator "<>" 6.4.8
Summary: Report if two values are not equal
Syntax: Any Left <> Any Right
Returns: Logical
Constraints: None
Semantics:
Returns NOT(Left
= Right)
if Left
and Right
are not Error. For Text values, if the calculation setting
HOSTCASESENSITIVE
is false
, text is compared but
characters differencing only in case are considered equal.
If either Left and Right are an Error, the result is an Error; this operator cannot be used to determine if two Errors are the same kind of Error.
Note: In some user interfaces the infix operator “<>” is displayed (or accepted) as “!=” or “≠”.
See also Infix Operator "=" 6.4.7, NOT 6.15.7
Summary: Report if two values have the given order
Syntax: Scalar Left op Scalar Right
where op is one of: "<", "<=", ">", or ">="
Returns: Logical
Constraints: None
Semantics:
Returns TRUE if the two values are less than, less than or equal,
greater than, or greater than or equal (respectively). If both Left
and Right
are Numbers, compare them as numbers. If both Left
and Right
are Text, compare them as text; if the calculation setting
HOSTCASESENSITIVE
is false
, text is compared but
characters are compared ignoring case. If the values are both
Logical, convert both to Number and then compare as Number.
These functions return one of TRUE, FALSE, or an Error if Left and Right have different types, but it is implementationdefined which of these results will be returned when the types differ.
See also Infix Operator "<>" 6.4.8, Infix Operator "=" 6.4.7
Summary: Concatenate two strings.
Syntax: Text Left & Text Right
Returns: Text
Constraints: None
Semantics: Concatenates two text (string) values.
Note: The infix operator “&” is equivalent to CONCATENATE(Left,Right).
See also Infix Operator "+" 6.4.2, CONCATENATE 6.20.6
Summary: Computes an inclusive range given two references
Syntax: Reference Left : Reference Right
Returns: Reference
Constraints: None
Semantics: Takes two references and computes the range, that is, a reference to the smallest 3dimensional cube of cells that include both Left and Right including the cells on sheets positioned between Left and Right. Left and Right need not be a single cell. For an expression such as [.B4:.B5]:[.C5] the resulting range is B4:C5. In case Left and/or Right involve a reference list (result of operator reference union), the range is computed and extended for each element of the list(s).
Note: For example, (a,b,c,d denoting one reference each) (a~b):(c~d) computes a:b:c:d determining the outermost fronttopleft and rearbottomright corners.
Left and Right may also be defined names or the result of a function returning a reference, such as INDIRECT.
See also Infix Operator Reference Union 6.4.13, Infix Operator Reference Intersection 6.4.12, INDIRECT 6.14.7
Summary: Compute the intersection of two references
Syntax: Reference Left ! Reference Right
Returns: Reference
Constraints: None
Semantics: Takes two references and computes the intersection  a reference to the intersection of cells in both Left and Right. If there are no cells in common, returns an Error.
If Left or Right are not of type Reference or ReferenceList, an Error shall be returned.
If Left and/or Right are reference lists (result of infix operator reference concatenation), the intersection is computed for each combination of Left and Right, producing a reference list of intersections.
Note
1: For example (a,b,c,d denoting one reference
each):
(a~b)!(c~d) will compute (a!c)~(a!d)~(b!c)~(b!d)
If for a resulting intersection there are no cells in common, the element is ignored and omitted from the result list. If for all intersections there are no cells in common and the result list is empty, an Error is returned.
Note 2: Intersection is notated as "!" in OpenFormula format, but as a space character in some user interfaces.
See also Infix Operator Reference Union 6.4.13
Summary: Concatenate two references
Syntax: Reference Left ~ Reference Right
Returns: ReferenceList
Constraints: None
Semantics: Takes two references and computes the "cell union", which is a concatenation of the reference Left followed by the reference Right. This is not the same as a union in set theory; duplicate references to cells are not removed. The resulting reference will have the number of areas, as reported by AREAS, as AREAS(Left)+AREAS(Right).
Note: Concatenation is notated as "~" in OpenFormula format, but as a comma or “+” in some user interfaces.
If Left or Right are not of type Reference or ReferenceList, an Error shall be returned.
Test Cases:
See also Infix Operator Reference Range 6.4.11, Infix Operator Reference Intersection 6.4.12, AREAS 6.13.2
Summary: Divide the operand by 100
Syntax: Number Left %
Returns: Number
Constraints: None
Semantics: Computes Left / 100.
See also Prefix Operator "" 6.4.16, Prefix Operator "+" 6.4.15
Summary: No operation; returns its one argument.
Syntax: + Any Right
Returns: Any
Constraints: None
Semantics: Returns the value given to it. Note that this does not convert a value to the Number type. In fact, it does no conversion at all of a Number, Logical, or Text value  it returns the same Number, Logical, or Text value (respectively). The "+" applied to a reference may return the reference, or an Error.
See also Infix Operator "+" 6.4.2
Summary: Negate its one argument.
Syntax:  Number Right
Returns: Number
Constraints: None
Semantics: Computes 0  Right.
See also Infix Operator "" 6.4.3
Matrix functions operate on matrices.
A matrix with M rows and N columns is defined by
The dimension subscript may be
omitted, if the context allows it, i.e.
.
Matrices are represented by uppercase letters. The elements of a
matrix are denoted by the corresponding lower case letter and
subscripts, which defines the row and column number.
Square
matrices have the same number of rows and columns, i.e.
.
Summary: Calculates the determinant of a matrix.
Syntax: MDETERM( ForceArray Array A )
Returns: Number
Constraints: Only square matrices are allowed.
Semantics: Returns the determinant of matrix A. The determinant is defined by
where P denotes a permutation of the numbers 1, 2, ..., n and is the sign of the permutation, which is +1 for an even amount of permutations (i.e., permutations that can be written as the composition of an even number of transpositions), 1 otherwise. A transposition on 1, ..., n is a permutation of 1, ..., n with exactly (n  2) numbers fixed.
See also MINVERSE 6.5.3
Summary: Returns the inverse of a matrix.
Syntax: MINVERSE( ForceArray Array A )
Returns: Array
Constraints: Only square matrices are allowed.
Semantics: Calculates the inverse of matrix A. The matrix A multiplied with its inverse results in the unity matrix of the same dimension as A:
Invertible matrices have a nonzero determinant. If the matrix is not invertible, this function should return an Error value.
See also MDETERM 6.5.2
Summary: Multiplies the matrices A and B.
Syntax: MMULT( ForceArray Array A ; ForceArray Array B )
Returns: Array
Constraints: COLUMNS(A) = ROWS(B)
Semantics: Returns the matrix product of the two matrices. The elements of the resulting matrix , are defined by:
See also COLUMNS 6.13.5, ROWS 6.13.30
Summary: Creates a unit matrix of a specified dimension N.
Syntax: MUNIT( Integer N )
Returns: Array
Constraints: The dimension has to be greater than zero.
Semantics: Creates the unit matrix (identity matrix) of dimension N.
Summary: Returns the transpose of a matrix.
Syntax: TRANSPOSE( Array A )
Returns: Array
Constraints: None
Semantics: Returns the transpose A^{T} of a matrix A, i.e. rows and columns of the matrix are exchanged.
Evaluators shall support unsigned integer values and results of at least 48 bits (values from 0 to 2^481 inclusive). Operations that receive or result in a value that cannot be represented within 48 bits are implementationdefined.
Summary: Returns bitwise “and” of its parameters
Syntax: BITAND( Integer X ; Integer Y )
Returns: Number
Constraints: X ≥ 0, Y ≥ 0
Semantics: Returns bitwise “and” of its parameters. In the result, each bit position is 1 if and only if all parameters' bits at that position are also 1; else it is 0.
See also BITOR 6.6.4, BITXOR 6.6.6, AND 6.15.2
Summary: Returns left shift of value X by N bits (“<<”)
Syntax: BITLSHIFT( Integer X ; Integer N )
Returns: Number
Constraints: X ≥ 0
Semantics: Returns left shift of value X by N bit positions:
If N < 0, return BITRSHIFT(X,N)
if N = 0, return X
if N > 0, return X * 2^N
See also BITAND 6.6.2, BITXOR 6.6.6, BITRSHIFT 6.6.5
Summary: Returns bitwise “or” of its parameters
Syntax: BITOR( Integer X ; Integer Y )
Returns: Number
Constraints: X ≥ 0, Y ≥ 0
Semantics: Returns bitwise “or” of its parameters. In the result, each bit position is 1 if any of its parameters' bits at that position are also 1; else it is 0.
See also BITAND 6.6.2, BITXOR 6.6.6, AND 6.15.2
Summary: Returns right shift of value X by N bits (“>>”)
Syntax: BITRSHIFT( Integer X ; Integer N )
Returns: Number
Constraints: X ≥ 0
Semantics: Returns right shift of value X by N bit positions:
If N < 0, return BITLSHIFT(X,N)
if N = 0, return X
if N > 0, return INT(X / 2^N)
See also BITAND 6.6.2, BITXOR 6.6.6, BITLSHIFT 6.6.3, INT 6.17.2
Summary: Returns bitwise “exclusive or” of its parameters
Syntax: BITXOR( Integer X ; Integer Y )
Returns: Number
Constraints: X ≥ 0, Y ≥ 0
Semantics: Returns bitwise “exclusive or” (xor) of its parameters. In the result, each bit position is 1 if one or the other parameters' bits at that position are 1; else it is 0.
See also BITAND 6.6.2, BITOR 6.6.4, OR 6.15.8
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 ] )
Returns: BytePosition
Semantics: The same as FIND, but using byte positions.
See also FIND 6.20.9 , LEFTB 6.7.3 , RIGHTB 6.7.7
Summary: Returns a selected number of text characters from the left, using a byte position.
Syntax: LEFTB( Text T [ ; ByteLength Length ] )
Returns: Text
Semantics: As LEFT, but using a byte position.
See also LEFT 6.20.12, RIGHT 6.20.19, RIGHTB 6.7.7
Summary: Returns the length of given text in units compatible with byte positions
Syntax: LENB( Text T )
Returns: ByteLength
Constraints: None.
Semantics: As LEN, but compatible with byte position values.
See also LEN 6.20.13, LEFTB 6.7.3, RIGHTB 6.7.7
Summary: Returns extracted text, given an original text, starting position using a byte position, and length.
Syntax: MIDB( Text T ; BytePosition Start ; ByteLength Length )
Returns: Text
Constraints: Length ≥ 0.
Semantics: As MID, but using byte positions.
See also MID 6.20.15, LEFTB 6.7.3, RIGHTB 6.7.7, REPLACEB 6.7.6
Summary: Returns text where an old text is replaced with a new text, using byte positions.
Syntax: REPLACEB( Text T ; BytePosition Start ; ByteLength Len ; Text New )
Returns: Text
Semantics: As REPLACE, but using byte positions.
See also REPLACE 6.20.17, LEFTB 6.7.3, RIGHTB 6.7.7, MIDB 6.7.5, SUBSTITUTE 6.20.21
Summary: Returns a selected number of text characters from the right, using byte position.
Syntax: RIGHTB( Text T [ ; ByteLength Length ] )
Returns: Text
Semantics: As RIGHT, but using byte positions.
See also RIGHT 6.20.19, LEFTB 6.7.3
Summary: Returns the starting position of a given text, using byte positions.
Syntax: SEARCHB( Text Search ; Text T [ ; BytePosition Start ] )
Returns: BytePosition
Semantics: As SEARCH, but using byte positions.
See also SEARCH 6.20.20, EXACT 6.20.8, FIND 6.20.9, FINDB 6.7.2
Functions for complex numbers.
Summary: Creates a complex number from a given real coefficient and imaginary coefficient.
Syntax: COMPLEX( Number Real ; Number Imaginary [ ; Text Suffix ] )
Returns: Complex
Constraints: None
Semantics: Constructs a complex number from the given coefficients. The third parameter Suffix is optional, and should be either “i” or “j”. Upper case “I” or “J” are not accepted for the suffix parameter.
Summary: Returns the absolute value of a complex number.
Syntax: IMABS( Complex X )
Returns: Number
Constraints: None
Semantics: If X = a + bi or X = a + bj, the absolute value = ; if X = r(cosφ + isinφ), the absolute value = r.
See also IMARGUMENT 6.8.5
Summary: Returns the imaginary coefficient of a complex number.
Syntax: IMAGINARY( Complex X )
Returns: Number
Constraints: None
Semantics: If X = a + bi or X = a + bj, then the imaginary coefficient is b.
See also IMREAL 6.8.19
Summary: Returns the complex argument of a complex number.
Syntax: IMARGUMENT( Complex X )
Returns: Number
Constraints: None
Semantics: If X = a + bi = r(cosφ + isinφ), a or b is not 0 and π < φ ≤ π, then the complex argument is φ. φ is expressed by radians. If X = 0, then IMARGUMENT(X) is implementationdefined and either 0 or an error.
See also IMABS 6.8.3
Summary: Returns the complex conjugate of a complex number.
Syntax: IMCONJUGATE( Complex X )
Returns: Complex
Constraints: None
Semantics: If X = a + bi, then the complex conjugate is a  bi.
Summary: Returns the cosine of a complex number.
Syntax: IMCOS( Complex X )
Returns: Complex
Constraints: None
Semantics: If X = a + bi, then cos(X) = cos(a)cosh(b)  sin(a)sinh(b)i.
See also IMSIN 6.8.20
Summary: Returns the hyperbolic cosine of a complex number.
Syntax: IMCOSH( Complex N )
Returns: Complex
Constraints: None
Semantics: If N = a + bi, then cosh(N) = cosh(a)cos(b) + sinh(a)sin(b)i.
Summary: Returns the cotangent of a complex number.
Syntax: IMCOT(Complex N)
Returns: Complex
Constraints: None
Semantics: Equivalent to the following (except N is computed only once):
IMDIV(IMCOS(N);IMSIN(N))
See also IMCOS 6.8.7, IMDIV 6.8.12, IMSIN 6.8.20, IMTAN 6.8.27
Summary: Returns the cosecant of a complex number.
Syntax: IMCSC(Complex N)
Returns: Complex
Constraints: None
Semantics: Equivalent to the following:
IMDIV(1;IMSIN(N))
See also IMDIV 6.8.12, IMSIN 6.8.20
Summary: Returns the hyperbolic cosecant of a complex number.
Syntax: IMCSCH( Complex N )
Returns: Complex
Constraints: None
Semantics: Computes the hyperbolic cosecant. This is equivalent to:
IMDIV(1;IMSINH(N))
See also IMSINH 6.8.21, CSCH 6.16.24
Summary: Divides the first number by the second.
Syntax: IMDIV( Complex X ; Complex Y )
Returns: Complex
Constraints: None
Semantics: Given X = a + bi and Y = c + di, return the quotient
Division by zero returns an Error.
See also IMDIV 6.8.12
Summary: Returns the exponent of e and a complex number.
Syntax: IMEXP( Complex X )
Returns: Complex
Constraints: None
Semantics: If X = a + bi, the result is .
See also IMLN 6.8.14
Summary: Returns the natural logarithm of a complex number.
Syntax: IMLN( Complex X )
Returns: Complex
Constraints: X ≠ 0
Semantics: COMPLEX(LN(IMABS(X)); IMARGUMENT(X)) .
See also COMPLEX 6.8.2, IMABS 6.8.3, IMARGUMENT 6.8.5, IMEXP 6.8.13 , IMLOG10 6.8.15, LN 6.16.39
Summary: Returns the common logarithm of a complex number.
Syntax: IMLOG10( Complex X )
Returns: Complex
Constraints: X ≠ 0
Semantics: IMLOG10(X) is IMDIV(IMLN(X);COMPLEX(LN(10);0)) .
See also COMPLEX 6.8.2, IMDIV 6.8.12, IMLN 6.8.14 , IMPOWER 6.8.17, LN 6.16.39
Summary: Returns the binary logarithm of a complex number.
Syntax: IMLOG2( Complex X )
Returns: Complex
Constraints: X ≠ 0
Semantics: IMLOG2(X) is IMDIV(IMLN(X);COMPLEX(LN(2);0)) .
See also COMPLEX 6.8.2, IMDIV 6.8.12, IMLN 6.8.14 , IMPOWER 6.8.17, LN 6.16.39
Summary: Returns the complex number X raised to the Yth power.
Syntax: IMPOWER( Complex X ; Complex Y ) or IMPOWER( Complex X ; Number Y)
Returns: Complex
Constraints: X ≠ 0
Semantics: IMPOWER(X;Y) is IMEXP(IMPRODUCT(Y; IMLN(X)))
An evaluator implementing this function shall permit any Number Y but may also allow any Complex Y.
See also IMEXP 6.8.13, IMLN 6.8.14, IMPOWER 6.8.17, IMPRODUCT 6.8.18
Summary: Returns the product of complex numbers.
Syntax: IMPRODUCT( { ComplexSequence N }^{+} )
Returns: Complex
Constraints: None
Semantics:
Multiply the complex numbers together. Given two complex numbers X =
a + bi and
Y = c + di, the product X * Y = (ac  bd) + (ad +
bc)i
See also IMDIV 6.8.12
Summary: Returns the real coefficient of a complex number.
Syntax: IMREAL( Complex N )
Returns: Number
Constraints: None
Semantics: If N = a + bi or N = a + bj, then the real coefficient is a.
See also IMAGINARY 6.8.4
Summary: Returns the sine of a complex number.
Syntax: IMSIN( Complex N )
Returns: Complex
Constraints: None
Semantics: If N = a + bi, then sin(N) = sin(a)cosh(b) + cos(a)sinh(b)i.
See also IMCOS 6.8.7
Summary: Returns the hyperbolic sine of a complex number.
Syntax: IMSINH( Complex N )
Returns: Complex
Constraints: None
Semantics: If N = a + bi, then sinh(N) = sinh(a)cos(b) + cosh(a)sin(b)i.
Summary: Returns the secant of a complex number.
Syntax: IMSEC(Complex N )
Returns: Complex
Constraints: None
Semantics: Equivalent to the following:
IMDIV(1;IMCOS(N))
See also IMCOS 6.8.7, IMDIV 6.8.12
Summary: Returns the hyperbolic secant of a complex number.
Syntax: IMSECH( Complex N )
Returns: Number
Constraints: None
Semantics: Computes the hyperbolic secant. This is equivalent to:
IMDIV(1;IMCOSH(N))
See also IMCOSH 6.8.8, IMDIV 6.8.12, SECH 6.16.57
Summary: Returns the square root of a complex number.
Syntax: IMSQRT( Complex N )
Returns: Complex
Constraints: None
Semantics:
If N
= 0 + 0i, then IMSQRT(N)
= 0. Otherwise IMSQRT(N)
is
SQRT(IMABS(N))
* sin(IMARGUMENT(N)
/ 2) + SQRT(IMABS(N))
* cos(IMARGUMENT(N)
/ 2)i.
See also IMABS 6.8.3, IMARGUMENT 6.8.5, IMPOWER 6.8.17, SQRT 6.16.58
Summary: Subtracts the second complex number from the first.
Syntax: IMSUB( Complex X ; Complex Y )
Returns: Complex
Constraints: None
Semantics: Subtract complex number Y from X.
See also IMSUM 6.8.26
Summary: Sums (add) a set of complex numbers, including all numbers in ranges.
Syntax: IMSUM( { ComplexSequence N }^{+} )
Returns: Complex
Constraints: None
Semantics: Adds complex numbers together. Text that cannot be converted to a complex number is ignored.
It is implementationdefined what happens if this function is given zero parameters; an evaluator may either produce an Error or the Number 0 if it is given zero parameters.
See also IMSUB 6.8.25
Summary: Returns the tangent of a complex number
Syntax: IMTAN(Complex N)
Returns: Complex
Constraints: None
Semantics: Equivalent to the following (except N is computed only once):
IMDIV(IMSIN(N);IMCOS(N))
See also IMDIV 6.8.12, IMSIN 6.8.20, IMCOS 6.8.7, IMCOT 6.8.25
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 or HOSTSEARCHCRITERIAMUSTAPPLYTOWHOLECELL properties change. 3.4
Summary: Finds the average of values in a given field from the records (rows) in a database that match a search criteria.
Syntax: DAVERAGE( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
Semantics: Perform AVERAGE on data records in database D field F that match criteria C.
See also AVERAGE 6.18.3, COUNT 6.13.6, DSUM 6.9.11, DCOUNT 6.9.3, SUM 6.16.61
Summary: Counts the number of records (rows) in a database that match a search criteria and contain numerical values.
Syntax: DCOUNT( Database D ; [ Field F ] ; Criteria C )
Returns: Number
Constraints: None
Semantics: Perform COUNT on data records in database D field F that match criteria C. If the Field argument is omitted, DCOUNT returns the count of all records that satisfy Criteria C.
See also COUNT 6.13.6, COUNTA 6.13.7, DCOUNTA 6.9.4, DSUM 6.9.11
Summary: Counts the number of records (rows) in a database that match a search criteria and contain values (as COUNTA).
Syntax: DCOUNTA( Database D ; [ Field F ] ; Criteria C )
Returns: Number
Constraints: None
Semantics: Perform COUNTA on data records in database D field F that match criteria C. If the Field argument is omitted, DCOUNTA returns the count of all records that satisfy criteria C.
See also COUNT 6.13.6, COUNTA 6.13.7, DCOUNT 6.9.3, DSUM 6.9.11
Summary: Gets the single value in the field from the single record (row) in a database that matches a search criteria.
Syntax: DGET( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
Semantics: Extracts the value in field F of the one data record in database D that matches criteria C. If no records match, or more than one matches, it returns an Error.
See also DMAX 6.9.6, DMIN 6.9.7, DSUM 6.9.11
Summary: Finds the maximum value in a given field from the records (rows) in a database that match a search criteria.
Syntax: DMAX( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
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
Summary: Finds the minimum value in a given field from the records (rows) in a database that match a search criteria.
Syntax: DMIN( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
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
Summary: Finds the product of values in a given field from the records (rows) in a database that match a search criteria.
Syntax: DPRODUCT( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
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
Summary: Finds the sample standard deviation in a given field from the records (rows) in a database that match a search criteria.
Syntax: DSTDEV( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
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
Summary: Finds the population standard deviation in a given field from the records (rows) in a database that match a search criteria.
Syntax: DSTDEVP( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
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
Summary: Finds the sum of values in a given field from the records (rows) in a database that match a search criteria.
Syntax: DSUM( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
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
Summary: Finds the sample variance in a given field from the records (rows) in a database that match a search criteria.
Syntax: DVAR( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
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
Summary: Finds the population variance in a given field from the records (rows) in a database that match a search criteria.
Syntax: DVARP( Database D ; Field F ; Criteria C )
Returns: Number
Constraints: None
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 )
Returns: Date
Constraints: 1904 ≤ Year ≤ 9956; 1 ≤ Month ≤ 12; 1 ≤ Day ≤ 31; Evaluators may evaluate expressions that do no meet this constraint.
Semantics: This computes the date's serial number given Year, Month, and Day of the Gregorian calendar. Fractional values are truncated. Month > 12 and Day > days of Month will roll over the date, computing the result by adding months and days as necessary. The value of the serial number depends on the current epoch.
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 )
Returns: Number
Constraints: None
Semantics: Compute difference of StartDate and EndDate, in the units given by Format.
The Format is a code from the following table, entered as text, that specifies the format you want the result of DATEDIF to have.
Table 5  DATEDIF
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.
Syntax: DATEVALUE( Text D )
Returns: Date
Constraints: None
Semantics: This computes the serial number of the text string D, using the current locale. This function shall accept ISO date format (YYYYMMDD), which is localeindependent. It is semantically equal to VALUE(Date), if Date has a date format, since text matching a date format is automatically converted to a serial number when used as a Number. If the text of D has a combined date and time format, e.g. YYYYMMDD HH:MM:SS, the integer part of the date serial number is returned. If the text of D does not have a date or time format, an evaluator may return an Error. See VALUE for more information on date formats. The value of the serial number depends on the current epoch.
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.
Syntax: DAY( DateParam D )
Returns: Number
Constraints: None
Semantics: Returns the day portion of D.
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 )
Returns: Number
Constraints: None
Semantics: Returns the number of days between two dates. If StartDate and EndDate are Numbers, this is EndDate – StartDate. If they are both Text, this is DATEVALUE(StartDate) – DATEVALUE(EndDate).
See also DATEDIF 6.10.3, DATEVALUE 6.10.4, 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 [ ; Logical Method = FALSE ] )
Returns: Number
Constraints: None
Semantics: If Method is FALSE, it uses the National Association of Securities Dealers (NASD) method, also known as the U.S. method. If Method is TRUE, the European method is used.
The US/NASD method (30US/360):
Truncate date values, set sign = 1.
If StartDate's dayofmonth is 31, it is changed to 30.
Otherwise, if StartDate's dayofmonth is the last day of February, it is changed to 30.
If EndDate's dayofmonth is 31 and StartDate's dayofmonth is 30 (after having applied a change for #2 or #3, if necessary), EndDate's dayofmonth is changed to 30.
Note 1: This calculation is slightly different from Basis 0 (4.11.7 Basis). Dates are never swapped.
The European method (30E/360):
Truncate date values, set sign = 1.
If StartDate is after EndDate then swap dates and set sign = 1.
If StartDate's dayofmonth is 31, it is changed to 30.
If EndDate's dayofmonth is 31, it is changed to 30.
Note 2: Days in February are never changed.
Note 3: This calculation is identical to Basis 4 (4.11.7 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 )
Returns: Number
Constraints: None
Semantics: First truncate StartDate and MonthAdd, then add MonthAdd number of months. MonthAdd can be positive, negative, or 0; if zero, the number representing StartDate (in the current epoch) is returned.
If after adding the given number of months, the day of month in the new month is larger than the number of days in the given month, the day of month is adjusted to the last day of the new month. Then the serial number of that date is returned.
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
Returns: Number
Constraints: None
Semantics: First truncate StartDate and MonthAdd, then add MonthAdd number of months. MonthAdd can be positive, negative, or 0. Then return the serial number representing the end of that month. Due to the semantics of this function, the value of DAY(StartDate) is irrelevant.
See also DAY 6.10.5, EDATE 6.10.8
Summary: Extracts the hour (0 through 23) from a time.
Syntax: HOUR( TimeParam T )
Returns: Number
Constraints: None
Semantics: Extract from T the hour value, 0 through 23, as per a 24hour clock. This is equal to:
DayFraction = (T  INT(T))
Hour = INT(DayFraction * 24)
See also MONTH 6.10.13, DAY 6.10.5, MINUTE 6.10.12, SECOND 6.10.16, INT 6.17.2
Summary: Determines the ISO week number of the year for a given date.
Syntax: ISOWEEKNUM( DateParam D )
Returns: Number
Constraints: None
Semantics: Returns the ordinal number of the [ISO8601] calendar week in the year for the given date D. ISO 8601 defines the calendar week as a time interval of seven calendar days starting with a Monday, and the first calendar week of a year as the one that includes the first Thursday of that year.
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.
Syntax: MINUTE( TimeParam T )
Returns: Number
Constraints: None
Semantics: Extract from T the minute value, 0 through 59, as per a clock. This is equal to:
DayFraction = (T  INT(T))
HourFraction = (DayFraction * 24  INT(DayFraction * 24))
Minute = INT(HourFraction * 60)
See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, SECOND 6.10.16, INT 6.17.2
Summary: Extracts the month from a date.
Syntax: MONTH( DateParam Date )
Returns: Number
Constraints: None
Semantics: Takes 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.
Syntax: NETWORKDAYS( DateParam Date1 ; DateParam Date2 [ ; [ DateSequence Holidays ] [ ; LogicalSequence Workdays ] ] )
Returns: Number
Constraints: None
Semantics: Returns the whole number of work days between two dates.
Work days are defined as nonweekend, nonholiday days. By default, weekends are Saturdays and Sundays and there are no holidays.
The optional 3^{rd} parameter Holidays can be used to specify a list of dates to be treated as holidays. Note that this parameter can be omitted as an empty parameter (two consecutive ;; semicolons) to be able to pass the set of Workdays without Holidays.
The optional 4th parameter Workdays can be used to specify a different definition for the standard work week by passing in a list of numbers which define which days of the week are workdays (indicated by 0) or not (indicated by nonzero) in order Sunday, Monday,...,Saturday. So, the default definition of the work week excludes Saturday and Sunday and is: {1;0;0;0;0;0;1}. To define the work week as excluding Friday and Saturday, the third parameter would be: {0;0;0;0;0;1;1}.
Summary: Returns the serial number of the current date and time.
Syntax: NOW()
Returns: DateTime
Constraints: None
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
Summary: Extracts the second (the integer 0 through 59) from a time. This function presumes that leap seconds never exist.
Syntax: SECOND( TimeParam T )
Returns: Number
Constraints: None
Semantics: Extract from T the second value, 0 through 59, as per a clock. Note that this returns an integer, without a fractional part. Note also that this rounds to the nearest second, instead of returning the integer part of the seconds. This is equal to:
DayFraction = (T  INT(T))
HourFraction = (DayFraction * 24  INT(DayFraction * 24))
MinuteFraction = (HourFraction * 60  INT(HourFraction * 60))
Second = ROUND(MinuteFraction * 60)
See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, MINUTE 6.10.12, INT 6.17.2
Summary: Constructs a time value from hours, minutes, and seconds.
Syntax: TIME( Number Hours ; Number Minutes ; Number Seconds )
Returns: Time
Constraints: None. Evaluators may first perform INT() on the hour, minute, and second before doing the calculation.
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.
Hours, Minutes, and Seconds may be any number (they shall not be limited to the ranges 0..24, 0..59, or 0..60 respectively).
See also DATE 6.10.2, INT 6.17.2
Summary: Returns a time serial number from given text.
Syntax: TIMEVALUE( Text T )
Returns: Time
Constraints: None
Semantics: This computes the serial number of the text string T, which is a time, using the current locale. This function shall accept ISO time format (HH:MM:SS), which is localeindependent. If the text of T has a combined date and time format, e.g. YYYYMMDD HH:MM:SS, the fractional part of the date serial number is returned. If the text of T does not have a time format, an evaluator may attempt to convert the number another way (e.g., using VALUE), or it may return an Error (this is implementationdependent).
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.
Syntax: TODAY()
Returns: Date
Constraints: None
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
Summary: Extracts the day of the week from a date; if text, uses current locale to convert to a date.
Syntax: WEEKDAY( DateParam D [ ; Integer Type = 1 ] )
Returns: Number
Constraints: None
Semantics: Returns the day of the week from a date D, as a number from 0 through 7. The exact meaning depends on the value of Type:
When Type is 1, Sunday is the first day of the week, with value 1; Saturday has value 7.
When Type is 2, Monday is the first day of the week, with value 1; Sunday has value 7.
When Type is 3, Monday is the first day of the week, with value 0; Sunday has value 6.
When Type is 11, Monday is the first day of the week, with value 1; Sunday has value 7.
When Type is 12, Tuesday is the first day of the week, with value 1; Monday has value 7.
When Type is 13, Wednesday is the first day of the week, with value 1; Tuesday has value 7.
When Type is 14, Thursday is the first day of the week, with value 1; Wednesday has value 7.
When Type is 15, Friday is the first day of the week, with value 1; Thursday has value 7.
When Type is 16, Saturday is the first day of the week, with value 1; Friday has value 7.
When Type is 17, Sunday is the first day of the week, with value 1; Saturday has value 7.
Table 6  WEEKDAY
Weekday Type 
1 
2 
3 
11 
12 
13 
14 
15 
16 
17 

Sunday 
1 
7 
6 
7 
6 
5 
4 
3 
2 
1 
Monday 
2 
1 
0 
1 
7 
6 
5 
4 
3 
2 
Tuesday 
3 
2 
1 
2 
1 
7 
6 
5 
4 
3 
Wednesday 
4 
3 
2 
3 
2 
1 
7 
6 
5 
4 
Thursday 
5 
4 
3 
4 
3 
2 
1 
7 
6 
5 
Friday 
6 
5 
4 
5 
4 
3 
2 
1 
7 
6 
Saturday 
7 
6 
5 
6 
5 
4 
3 
2 
1 
7 
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 D [ ; Number Mode = 1 ] )
Returns: Number
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.
For Mode = {1, 2, 11, 12, ..., 17} the week containing January 1 is the first week of the year, and is numbered week 1. The week starts on {Sunday, Monday, Monday, Tuesday, ..., Sunday}.
Mode 21 and Mode 150 are both [ISO8601], the week starts on Monday and the week containing the first Thursday of the year is the first week of the year, and is numbered week 1.
See also DAY 6.10.5, MONTH 6.10.13, YEAR 6.10.23, WEEKDAY 6.10.20, ISOWEEKNUM 6.10.11
Summary: Returns the date serial number which is a specified number of work days before or after an input date.
Syntax: WORKDAY( DateParam Date ; Number Offset [ ; [ DateSequence Holidays ] [ ; LogicalSequence Workdays ] ] )
Returns: DateTime
Constraints: None
Semantics: Returns the date serial number for the day that is offset from the input Date parameter by the number of work days specified in the Offset parameter. If Offset is negative, the offset will return a date prior to Date. If Offset is positive, a date later Date is returned. If Offset is zero, then Date is returned.
Work days are defined as nonweekend, nonholiday days. By default, weekends are Saturdays and Sundays and there are no holidays.
The optional 3^{rd} parameter Holidays can be used to specify a list of dates to be treated as holidays. Note that this parameter can be omitted as an empty parameter (two consecutive ;; semicolons) to be able to pass the set of Workdays without Holidays.
The optional 4th parameter Workdays can be used to specify a different definition for the standard work week by passing in a list of numbers which define which days of the week are workdays (indicated by 0) or not (indicated by nonzero) in order Sunday, Monday,...,Saturday. If all seven numbers in Workdays are nonzero and Offset is also nonzero, WORKDAY returns an error.
Note: The default definition of the work week that excludes Saturday and Sunday and is: {1;0;0;0;0;0;1}. To define the workweek as excluding Friday and Saturday, the third parameter would be: {0;0;0;0;0;1;1}.
Summary: Extracts the year from a date given in the current locale of the evaluator.
Syntax: YEAR( DateParam D )
Returns: Number
Constraints: None
Semantics: Parses a dateformatted string in the current locale's format and returns the year portion.
If a year is given as a twodigit number, as in "052115", then the year returned is either 1915 or 2015, depending upon the break point in the calculation context. In an OpenDocument document, this break point is determined by HOSTNULLYEAR.
Evaluators shall support extracting the year from a date beginning in 1900. Threedigit year numbers precede adoption of the Gregorian calendar, and may return either an Error or the year number. Fourdigit year numbers preceding 1582 (inception of the Gregorian Calendar) may return either an Error or the year number. Fourdigit year numbers following 1582 should return the year number.
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 B = 0 ] )
Returns: Number
Constraints: None
Semantics: Computes the fraction of the number of years between a StartDate and EndDate.
B indicates the daycount convention to use in the calculation. 4.11.7
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 ] )
Returns: NumberText
Constraints: None
Semantics: Performs a DDE request and returns its result. The request invokes the service Server on the topic named as Topic, requesting that it reply with the information on Item.
Evaluators may choose to not perform this function on every recalculation, but instead cache an answer and require a separate action to reperform these requests. Evaluators shall perform this request on initial load when their security policies permit it.
Mode is an optional parameter that determines how the results are returned:
Table 7  DDE
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) 
In an OpenDocument spreadsheet document the default table cell style is specified with table:defaultcellstylename. Its number:numberstyle specified by style:datastylename specifies the locale to use in the conversion.
The DDE function is nonportable because it depends on availability of external programs (server parameter) and their interpretation of the topic and item parameters.
Summary: Creation of a hyperlink involving an evaluated expression.
Syntax: HYPERLINK( Text IRI [ ; TextNumber FunctionResult ] )
Returns: Text or Number
Constraints: None
Semantics: The default for the second argument is the value of the first argument. The second argument value is returned.
In addition, hosting environments may interpret expressions containing HYPERLINK function calls as calling for an implementationdependent creation of a hypertext link based on the expression containing the HYPERLINK function calls.
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.
Financial functions defined in this standard use a cash flow sign convention where outgoing cash flows are negative and incoming cash flows are positive.
Summary: Calculates the accrued interest for securities with periodic interest payments.
Syntax: ACCRINT( DateParam Issue ; DateParam First ; DateParam Settlement ; Number Coupon ; Number Par ; Integer Frequency [ ; Basis B = 0 [ ; Logical CalcMethod = TRUE ] ] )
Returns: Currency
Constraints: Issue < First < Settlement ; Coupon > 0; Par > 0
Frequency is one of the following values:
Table 8  ACCRINT
Frequency 
Frequency of coupon payments 

1 
Annual 
2 
Semiannual 
4 
Quarterly 
12 
Monthly 
Semantics: Calculates the accrued interest for securities with periodic interest payments. ACCRINT supports short, standard, and long Coupon periods.
If CalcMethod is TRUE (the default) then ACCRINT returns the sum of the accrued interest in each coupon period from issue date until settlement date. If CalcMethod is FALSE then ACCRINT returns the sum of the accrued interest in each coupon period from first interest date until settlement date. For each coupon period, the interest is Par * Coupon * YEARFRAC(startofperiod;endofperiod; B)
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.
B: Indicates the daycount convention to use in the calculation. 4.11.7
CalcMethod: A logical value that specifies how to treat the case where Settlement > First.
See also ACCRINTM 6.12.3, YEARFRAC 6.10.24
Summary: Calculates the accrued interest for securities that pay at maturity.
Syntax: ACCRINT( DateParam Issue ; DateParam Settlement ; Number Coupon ; Number Par [ ; Basis B = 0 ] )
Returns: Currency
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.
B: Indicates the daycount convention to use in the calculation. 4.11.7
See also ACCRINT 6.12.2
Summary: Calculates the amortization value for the French accounting system using linear depreciation (l'amortissement linéaire comptable) .
Syntax: AMORLINC( Number Cost ; DateParam PurchaseDate ; DateParam FirstPeriodEndDate ; Number Salvage ; Integer Period ; Number Rate [ ; Basis B = 0 ] )
Returns: Currency
Constraints: Cost > 0; PurchaseDate ≤ FirstPeriodEndDate; Salvage ≥ 0; Period ≥ 0; Rate > 0
Semantics: Calculates the amortization value for the French accounting system using linear depreciation.
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.
B: Indicates the daycount convention to use in the calculation. 4.11.7
When period = 0:
For full periods, where Period > 0, the depreciation is Cost * Rate
For the last period, possibly a partial period:
the depreciation = Cost  Salvage  accumulateddepreciation,
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 > (Cost  Salvage) / (Cost * Rate) then the depreciation is 0.
Note: The behavior of this function is implementationdefined in cases where PurchaseDate = FirstPeriodEndDate.
See also DB 6.12.13, DDB 6.12.14, YEARFRAC 6.10.24
Summary: Calculates the number of days between the beginning of the coupon period that contains the settlement date and the settlement date.
Syntax: COUPDAYBS( DateParam Settlement ; DateParam Maturity ; Integer Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Settlement < Maturity
Frequency is one of the following values:
Table 9  COUPDAYBS
Frequency 
Frequency of coupon payments 

1 
Annual 
2 
Semiannual 
4 
Quarterly 
Semantics:
Calculate the number of days from the beginning of the coupon period
to the settlement date.
Settlement: The settlement date.
Maturity: The maturity date.
Frequency: The number of coupon payments per year.
B: Indicates the daycount convention to use in the calculation. 4.11.7
See also COUPDAYS 6.12.6 , COUPDAYSNC 6.12.7 , COUPNCD 6.12.7 , COUPNUM 6.12.9 , COUPPCD 6.12.10
Summary: Calculates the number of days in a coupon period that contains the settlement date.
Syntax: COUPDAYS( DateParam Settlement ; DateParam Maturity ; Integer Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Settlement < Maturity
Frequency is one of the following values:
Table 10  COUPDAYS
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.
Settlement: The settlement date.
Maturity: The maturity date.
Frequency: The number of coupon payments per year.
B: Indicates the daycount convention to use in the calculation. 4.11.7
See also COUPDAYBS 6.12.5 , COUPDAYSNC 6.12.7 , COUPNCD 6.12.7 , COUPNUM 6.12.9 , COUPPCD 6.12.10
Summary: Calculates the number of days between a settlement date and the next coupon date.
Syntax: COUPDAYNC( DateParam Settlement ; DateParam Maturity ; Integer Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Settlement < Maturity
Frequency is one of the following values:
Table 11  COUPDAYSNC
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.
Settlement: The settlement date.
Maturity: The maturity date.
Frequency: The number of coupon payments per year.
B: Indicates the daycount convention to use in the calculation. 4.11.7
See also COUPDAYBS 6.12.5 , COUPDAYS 6.12.6 , COUPNCD 6.12.7 , COUPNUM 6.12.9 , COUPPCD 6.12.10
Summary: Calculates the next coupon date following a settlement.
Syntax: COUPNCD( DateParam Settlement ; DateParam Maturity ; Integer Frequency [ ; Basis B = 0 ] )
Returns: Date
Constraints: Settlement < Maturity
Frequency is the number of coupon payments per year. Frequency is one of the following values:
Table 12  COUPNCD
Frequency 
Frequency of coupon payments 

1 
Annual 
2 
Semiannual 
4 
Quarterly 
Semantics:
Calculates
the next coupon date after the Settlement
date based on the
Maturity
(expiration) date of
the asset, the Frequency
of coupon payments
and the daycount B.
B indicates the daycount convention to use in the calculation. 4.11.7
See also: COUPDAYSNC 6.12.7
Summary: Calculates the number of outstanding coupons between settlement and maturity dates.
Syntax: COUPNUM( DateParam Settlement ; DateParam Maturity ; Integer Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Frequency is the number of coupon payments per year. Frequency is one of the following values:
Table 13  COUPNUM
Frequency 
Frequency of coupon payments 

1 
Annual 
2 
Semiannual 
4 
Quarterly 
Semantics:
Calculates
the number of coupons in the interval between the Settlement
and the Maturity
(expiration) date of
the asset, the Frequency
of coupon payments
and the daycount B.
B indicates the daycount convention to use in the calculation. 4.11.7
See also COUPDAYBS 6.12.5, COUPDAYS 6.12.6, COUPDAYSNC 6.12.7, COUPNCD 6.12.7, COUPPCD 6.12.10
Summary: Calculates the next coupon date prior a settlement.
Syntax: COUPPCD( DateParam Settlement ; DateParam Maturity ; Integer Frequency [ ; Basis B = 0 ] )
Returns: Date
Constraints: Settlement < Maturity
Frequency is the number of coupon payments per year. Frequency is one of the following values:
Table 14  COUPPCD
Frequency 
Frequency of coupon payments 

1 
Annual 
2 
Semiannual 
4 
Quarterly 
Semantics:
Calculates
the next coupon date prior to the Settlement
date based on the
Maturity
(expiration) date of
the asset, the Frequency
of coupon payments
and the daycount B.
B indicates the daycount convention to use in the calculation. 4.11.7
See also COUPDAYBS 6.12.5, COUPDAYS 6.12.6, COUPDAYSNC 6.12.7, COUPNCD 6.12.7, COUPNUM 6.12.9
Summary: Calculates a cumulative interest payment.
Syntax: CUMIPMT( Number Rate ; Number Periods ; Number Value ; Integer Start ; Integer End ; Integer Type )
Returns: Currency
Constraints: Rate > 0; Value > 0; 1 ≤ Start ≤ End ≤ Periods
Type is one of the following values:
Table 15  CUMIPMT
Type 
Maturity date 

0 
due at the end 
1 
due at the beginning 
Semantics: Calculates the cumulative interest payment.
Rate: The interest rate per period.
Periods: The number of periods.
Value: The current value of the loan.
Start: The starting period.
End: The end period.
Type: The maturity date, the beginning or the end of a period.
See also IPMT 6.12.23, CUMPRINC 6.12.12
Summary: Calculates a cumulative principal payment.
Syntax: CUMPRINC( Number Rate ; Number Periods ; Number Value ; Integer Start ; Integer End ; Integer Type )
Returns: Currency
Constraints: Type is one of the following values:
Table 16  CUMPRINC
Type 
Maturity date 

0 
due at the end 
1 
due at the beginning 
Type 
Maturity date 

0 
due at the end 
1 
due at the beginning 
Semantics: Calculates the cumulative principal payment.
Rate: The interest rate per period.
Periods: The number of periods.
Value: The current value of the loan.
Start: The starting period.
End: The end period.
Type: The maturity date, the beginning or the end of a period.
See also PPMT 6.12.37 , CUMIPMT 6.12.11
Summary: Compute the depreciation allowance of an asset.
Syntax: DB( Number Cost ; Number Salvage ; Integer LifeTime ; Number Period [ ; Number Month = 12 ] )
Returns: Currency
Constraints: Cost > 0, Salvage ≥ 0, LifeTime > 0; Period > 0; 0 < Month < 13
Semantics: Calculate the depreciation allowance of an asset with an initial value of Cost, an expected useful LifeTime, and a final Salvage value at a specified Period of time, using the fixeddeclining balance method. The parameters are:
Cost: the total amount paid for the asset.
Salvage: the salvage value at the end of the LifeTime.
LifeTime: the number of periods that the depreciation will occur over. A positive integer.
Period: the time period for which you want to find the depreciation allowance, in the same units as LifeTime.
Month: (optional) the number of months in the first year of depreciation, assumed to be 12, if not specified. If a value is specified for Month, LifeTime and Period are assumed to be measured in years.
The rate is calculated as follows:
and is rounded to 3 decimals.
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.14, SLN 6.12.45, INT 6.17.2
Summary: Compute the amount of depreciation at a given period of time.
Syntax: DDB( Number Cost ; Number Salvage ; Number LifeTime ; Number Period [ ; Number DeclinationFactor = 2 ] )
Returns: Currency
Constraints: Cost ≥ 0, Salvage ≥ 0, Salvage ≤ Cost, 1 ≤ Period ≤ LifeTime, DeclinationFactor > 0
Semantics: Compute the amount of depreciation of an asset at a given period of time. The parameters are:
Cost: the total amount paid for the asset.
Salvage: the salvage value at the end of the LifeTime
LifeTime: the number of periods that the depreciation will occur over.
Period: the period for which a depreciation value is specified.
DeclinationFactor: the method of calculating depreciation, the rate at which the balance declines. Defaults to 2. If 2, doubledeclining balance is used.
To calculate depreciation, DDB uses a fixed rate. When DeclinationFactor = 2 this is the doubledecliningbalance method (because it is double the straightline rate that would depreciate the asset to zero). The rate is given by:
The depreciation each period is calculated as
depreciation_of_period = MIN( book_value_at_start_of_ period * rate; book_value_at_start_of_ period  Salvage )
Thus the asset depreciates at rate until the book value is Salvage value.
To allow also noninteger Period values this algorithm may be used:
If Period
is an Integer number, the relation between DDB and VDB is:
DDB(
Cost
; Salvage
; LifeTime
; Period
; DeclinationFactor
)
equals
VDB( Cost
; Salvage
; LifeTime
; Period
 1 ; Period
; DeclinationFactor
; TRUE )
See also SLN 6.12.45, VDB 6.12.50, MIN 6.18.48
Summary: Returns the discount rate of a security.
Syntax: DISC( DateParam Settlement ; DateParam Maturity ; Number Price ; Number Redemption [ ; Basis B = 0 ] )
Returns: Percentage
Constraints: Settlement < Maturity
Semantics: Calculates the discount rate of a security.
Settlement: The settlement date of the security.
Maturity: The maturity date.
Price: The price of the security.
Redemption: The redemption value of the security.
B: Indicates the daycount convention to use in the calculation. 4.11.7
See also YEARFRAC 6.10.24
Summary: Converts a fractional dollar representation into a decimal representation.
Syntax: DOLLARDE( Number Fractional ; Integer Denominator )
Returns: Number
Constraints: Denominator > 0
Semantics: Converts a fractional dollar representation into a decimal representation.
Fractional: Decimal fraction.
Denominator: The denominator of the fraction.
See also DOLLARFR 6.12.17 , TRUNC 6.17.8
Summary: Converts a decimal dollar representation into a fractional representation.
Syntax: DOLLARFR( Number Decimal ; Integer Denominator )
Returns: Number
Constraints: Denominator > 0
Semantics: Converts a decimal dollar representation into a fractional representation.
Decimal: A decimal number.
Denominator: The denominator of the fraction.
See also DOLLARDE 6.12.16, TRUNC 6.17.8
Summary: Returns the Macaulay duration of a fixed interest security in years
Syntax: DURATION( Date Settlement ; Date Maturity ; Number Coupon ; Number Yield ; Number Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Yield ≥0, Coupon ≥ 0, Settlement ≤ Maturity; Frequency = 1, 2, 4
Semantics: Computes the Macaulay duration, given:
Settlement: the date of purchase of the security
Maturity: the date when the security matures
Coupon: the annual nominal rate of interest
Yield: the annual yield of the security
Frequency: number of interest payments per year
B: Indicates the daycount convention to use in the calculation. 4.11.7
See also MDURATION 6.12.26
Summary: Returns the net annual interest rate for a nominal interest rate.
Syntax: EFFECT( Number Rate ; Integer Payments )
Returns: Number
Constraints: Rate ≥ 0; Payments > 0
Semantics: Nominal interest refers to the amount of interest due at the end of a calculation period. Effective interest increases with the number of payments made. In other words, interest is often paid in installments (for example, monthly or quarterly) before the end of the calculation period.
Rate: The interest rate per period.
Payments: The number of payments per period.
See also NOMINAL 6.12.28
Summary: Compute the future value (FV) of an investment.
Syntax: FV( Number Rate ; Number Nper ; Number Payment [ ; [ Number Pv = 0 ] [ ; Number PayType = 0 ] ] )
Returns: Currency
Constraints: None.
Semantics: Computes the future value of an investment. The parameters are:
Rate: the interest rate per period.
Nper: the total number of payment periods.
Payment: the payment made in each period.
Pv: the present value; default is 0.
PayType: the type of payment, defaults to 0. It is 0 if payments are due at the end of the period; 1 if they are due at the beginning of the period.
See PV 6.12.41 for the equation this solves.
See also PV 6.12.41, NPER 6.12.29, PMT 6.12.36, RATE 6.12.42
Summary: Returns the accumulated value given starting capital and a series of interest rates.
Syntax: FVSCHEDULE( Number Principal ; NumberSequence Schedule )
Returns: Currency
Constraints: None.
Semantics: Returns the accumulated value given starting capital and a series of interest rates, as follows:
See also PV 6.12.41, NPER 6.12.29, PMT 6.12.36, RATE 6.12.42
Summary: Computes the interest rate of a fully vested security.
Syntax: INTRATE( Date Settlement ; Date Maturity ; Number Investment ; Number Redemption [ ; Basis Basis = 0 ] )
Returns: Number
Constraints: Settlement < Maturity
Semantics: Calculates the annual interest rate that results when an item is purchased at the investment price and sold at the redemption price. No interest is paid on the investment. The parameters are:
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: indicates the daycount convention to use in the calculation. 4.11.7
The return value for this function is:
See also RECEIVED 6.12.43, YEARFRAC 6.10.24
Summary: Returns the amount of an annuity payment going towards interest.
Syntax: IPMT( Number Rate ; Number Period ; Number Nper ; Number PV [ ; Number FV = 0 [ ; Number Type = 0 ] ] )
Returns: Currency
Constraints: None.
Semantics: Computes the interest portion of an amortized payment for a constant interest rate and regular payments. The interest payment is the interest rate multiplied by the balance at the beginning of the period. The parameters are:
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.
Type: the due date for the payments (optional). Zero if omitted. If Type is 1, then payments are made at the beginning of each period. If Type is 0, then payments are made at the end of each period.
See also PPMT 6.12.37, PMT 6.12.36
Summary: Compute the internal rate of return for a series of cash flows.
Syntax: IRR( NumberSequence Values [ ; Number Guess = 0.1 ] )
Returns: Percentage
Constraints: None.
Semantics: Compute the internal rate of return for a series of cash flows.
If provided, Guess is an estimate of the interest rate to start the iterative computation. If omitted, the value 0.1 (10%) is assumed.
The result of IRR is the rate at which the NPV() function will return zero with the given values.
There is no closed form for IRR. Evaluators may return an approximate solution using an iterative method, in which case the Guess parameter may be used to initialize the iteration. If the evaluator is unable to converge on a solution given a particular Guess, it may return an Error.
See also NPV 6.12.30, RATE 6.12.42
Summary: Compute the interest payment of an amortized loan for a given period.
Syntax: ISPMT( Number Rate ; Number Period ; Number Nper ; Number Pv )
Returns: Currency
Constraints: None.
Semantics: Computes the interest payment of an amortized loan for a given period. The parameters are:
Rate: the interest rate per period.
Period: the period for which the interest is computed
Nper: the total number of payment periods.
Pv: the amount of the investment
See also PV 6.12.41, FV 6.12.20, NPER 6.12.29, PMT 6.12.36, RATE 6.12.42
Summary: Returns the modified Macaulay duration of a fixed interest security in years.
Syntax: MDURATION( Date Settlement ; Date Maturity ; Number Coupon ; Number Yield ; Number Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Yield ≥ 0, Coupon ≥ 0, Settlement ≤ Maturity; Frequency = 1, 2, 4
Semantics: Computes the modified Macaulay duration, given:
Settlement: the date of purchase of the security
Maturity: the date when the security matures
Coupon: the annual nominal rate of interest
Yield: the annual yield of the security
Frequency: number of interest payments per year
B: Indicates the daycount convention to use in the calculation. 4.11.7
See also DURATION 6.12.18
Summary: Returns the modified internal rate of return (IRR) of a series of periodic investments.
Syntax: MIRR( Array Values ; Number Investment ; Number ReinvestRate )
Returns: Percentage
Constraints: Values shall contain at least one positive value and at least one negative value.
Semantics: Values is a series of periodic income (positive values) and payments (negative values) at regular intervals (Text and Empty cells are ignored). Investment is the rate of interest of the payments (negative values); ReinvestRate is the rate of interest of the reinvestment (positive values).
Computes the modified internal rate of return, which is:
where N is the number of incomes and payments in Values (total).
See also IRR 6.12.24, NPV 6.12.30
Summary: Compute the annual nominal interest rate.
Syntax: NOMINAL( Number EffectiveRate ; Integer CompoundingPeriods )
Returns: Number
Constraints: EffectiveRate > 0 , CompoundingPeriods > 0
Semantics: Returns the annual nominal interest rate based on the effective rate and the number of compounding periods in one year. The parameters are:
EffectiveRate: effective rate
CompoundingPeriods: the compounding periods per year
Suppose that P is the present value, m is the compounding periods per year, the future value after one year is
The mapping between nominal rate and effective rate is
See also EFFECT 6.12.19
Summary: Compute the number of payment periods for an investment.
Syntax: NPER( Number Rate ; Number Payment ; Number Pv [ ; [ Number Fv ] [ ; Number PayType ] ] )
Returns: Number
Constraints: None.
Semantics: Computes the number of payment periods for an investment. The parameters are:
Rate: the constant interest rate.
Payment: the payment made in each period.
Pv: the present value of the investment.
Fv: the future value; default is 0.
PayType: the type of payment, defaults to 0. It is 0 if payments are due at the end of the period; 1 if they are due at the beginning of the period.
If Rate is 0, then NPER solves this equation:
If Rate is nonzero, then NPER solves this equation:
Evaluators claiming to support the “Medium” or “Large” set shall support negative rates; evaluators only claiming to support the “Small” set need not.
See also FV 6.12.20, RATE 6.12.42, PMT 6.12.36, PV 6.12.41
Summary: Compute the net present value (NPV) for a series of periodic cash flows.
Syntax: NPV( Number Rate ; { NumberSequenceList Values }^{+} )
Returns: Currency
Constraints: None.
Semantics: Computes the net present value for a series of periodic cash flows with the discount rate Rate. Values should be positive if they are received as income, and negative if the amounts are paid as outgo. Because the result is affected by the order of values, evaluators shall evaluate arguments in the order given and range reference and array arguments rowwise starting from top left.
If N is the number of values in Values, the formula for NPV is:
See also FV 6.12.20, IRR 6.12.24, NPER 6.12.29, PMT 6.12.36, PV 6.12.41, XNPV 6.12.52
Summary: Compute the value of a security per 100 currency units of face value. The security has an irregular first interest date.
Syntax: ODDFPRICE( DateParam Settlement ; DateParam Maturity ; DateParam Issue ; DateParam First ; Number Rate ; Number Yield ; Number Redemption ; Number Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Rate, Yield, and Redemption should be greater than 0.
Semantics: The parameters are
Settlement: the settlement/purchase date of the security
Maturity: the maturity/expiry date of the security
Issue: the issue date of the security
First: the first coupon date of the security
Rate: the interest rate of the security
Yield: the annual yield of the security
Redemption: the redemption value per 100 currency units face value
Frequency: the number of interest payments per year. 1 = annual; 2 = semiannual; 4 = quarterly.
B: indicates the daycount convention to use in the calculation. 4.11.7
See also ODDLPRICE 6.12.33 , ODDFYIELD 6.12.32
Summary: Compute the yield of a security per 100 currency units of face value. The security has an irregular first interest date.
Syntax: ODDFYIELD( DateParam Settlement ; DateParam Maturity ; DateParam Issue ; DateParam First ; Number Rate ; Number Price ; Number Redemption ; Number Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Rate, Price, and Redemption should be greater than 0. Maturity > First > Settlement > Issue.
Semantics: The parameters are
Settlement: the settlement/purchase date of the security
Maturity: the maturity/expiry date of the security
Issue: the issue date of the security
First: the first coupon date of the security
Rate: the interest rate of the security
Price: the price of the security
Redemption: the redemption value per 100 currency units face value
Frequency: the number of interest payments per year. 1 = annual; 2 = semiannual; 4 = quarterly.
B: indicates the daycount convention to use in the calculation. 4.11.7
See also ODDLYIELD 6.12.34 , ODDFPRICE 6.12.31
Summary: Compute the value of a security per 100 currency units of face value. The security has an irregular last interest date.
Syntax: ODDLPRICE( DateParam Settlement ; DateParam Maturity ; DateParam Last ; Number Rate ; Number AnnualYield ; Number Redemption ; Number Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Rate, AnnualYield, and Redemption should be greater than 0. The Maturity date should be greater than the Settlement date, and the Settlement should be greater than the last interest date.
Semantics: The parameters are
Settlement: the settlement/purchase date of the security
Maturity: the maturity/expiry date of the security
Last: the last interest date of the security
Rate: the interest rate of the security
AnnualYield: the annual yield of the security
Redemption: the redemption value per 100 currency units face value
Frequency: the number of interest payments per year. 1 = annual; 2 = semiannual; 4 = quarterly
B: indicates the daycount convention to use in the calculation. 4.11.7
See also ODDFPRICE 6.12.31
Summary: Compute the yield of a security which has an irregular last interest date.
Syntax: ODDLYIELD( DateParam Settlement ; DateParam Maturity ; DateParam Last ; Number Rate ; Number Price ; Number Redemption ; Number Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Rate, Price, and Redemption should be greater than 0.
Semantics: The parameters are
Settlement: the settlement/purchase date of the security
Maturity: the maturity/expiry date of the security
Last: the last interest date of the security
Rate: the interest rate of the security
Price: the price of the security
Redemption: the redemption value per 100 currency units face value
Frequency: the number of interest payments per year. 1 = annual; 2 = semiannual; 4 = quarterly.
B: indicates the daycount convention to use in the calculation. 4.11.7
See also ODDLPRICE 6.12.33 , ODDFYIELD 6.12.32
Summary: Returns the number of periods required by an investment to realize a specified value.
Syntax: PDURATION( Number Rate ; Number CurrentValue ; Number SpecifiedValue )
Returns: Number
Constraints: Rate > 0; CurrentValue > 0; SpecifiedValue > 0
Semantics: Calculates the number of periods for attaining a certain value SpecifiedValue, starting from CurrentValue and using the interest rate Rate.
Rate: The interest rate per period.
CurrentValue: The current value of the investment.
SpecifiedValue: The value, that should be reached.
See also DURATION 6.12.18
Summary: Compute the payment made each period for an investment.
Syntax: PMT( Number Rate ; Integer Nper ; Number Pv [ ; [ Number Fv = 0 ] [ ; Number PayType = 0 ] ] )
Returns: Currency
Constraints: Nper > 0
Semantics: Computes the payment made each period for an investment. The parameters are:
Rate: the interest rate per period.
Nper: the total number of payment periods.
Pv: the present value of the investment.
Fv: the future value of the investment; default is 0.
PayType: the type of payment, defaults to 0. It is 0 if payments are due at the end of the period; 1 if they are due at the beginning of the period. With PayType = 1 the first payment is made on the same day the loan is taken out.
If Rate is 0, the following equation is solved:
If Rate is nonzero, then PMT solves this equation:
See also FV 6.12.20, NPER 6.12.29, PV 6.12.41, RATE 6.12.42
Summary: Calculate the payment for a given period on the principal for an investment at a given interest rate and constant payments.
Syntax: PPMT( Number Rate ; Integer Period ; Integer Nper ; Number Present [ ; Number Future = 0 [ ; Number Type = 0 ] ] )
Returns: Number
Constraints: Rate and Present should be greater than 0. 0 < Period < Nper.
Semantics: The parameters are:
Rate: the interest rate.
Period: the given period that the payment returned is for.
Nper: the total number of periods.
Present: the present value.
Future: optional, the future value specified after Nper periods. The default value is 0.
Type: optional, 0 or 1, respectively for payment at the end or at the beginning of a period. The default value is 0.
See also PMT 6.12.36
Summary: Calculates a quoted price for an interest paying security, per 100 currency units of face value.
Syntax: PRICE( DateParam Settlement ; DateParam Maturity ; Number Rate ; Number AnnualYield ; Number Redemption ; Number Frequency [ ; Basis Bas = 0 ] )
Returns: Number
Constraints: Rate, AnnualYield, and Redemption should be greater than 0; Frequency = 1, 2 or 4.
Semantics: If A is the number of days from the Settlement date to next coupon date, B is the number of days of the coupon period that the Settlement is in, C is the number of coupons between Settlement date and Redemption date, D is the number of days from beginning of coupon period to Settlement date, then PRICE is calculated as
The parameters are:
Settlement: the settlement/purchase date of the security.
Maturity: the maturity/expiry date of the security.
Rate: the interest rate of the security.
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 = semiannual; 4 = quarterly.
Bas: indicates the daycount convention to use in the calculation. 4.11.7
See also PRICEDISC 6.12.39, PRICEMAT 6.12.40
Summary: Calculate the price of a security with a discount per 100 currency units of face value.
Syntax: PRICEDISC( DateParam Settlement ; DateParam Maturity ; Number Discount ; Number Redemption [ ; Basis B = 0 ] )
Returns: Number
Constraints: Discount and Redemption should be greater than 0.
Semantics: The parameters are:
Settlement: the settlement/purchase date of the security.
Maturity: the maturity/expiry date of the security.
Discount: the discount rate of the security.
Redemption: the redemption value per 100 currency units face value.
B: indicates the daycount convention to use in the calculation. 4.11.7
See also PRICE 6.12.38, PRICEMAT 6.12.40, YIELDDISC 6.12.54
Summary: Calculate the price per 100 currency units of face value of the security that pays interest on the maturity date.
Syntax: PRICEMAT( DateParam Settlement ; DateParam Maturity ; DateParam Issue ; Number Rate ; Number AnnualYield [ ; Basis B = 0 ] )
Returns: Number
Constraints: Settlement < Maturity, Rate ≥ 0, AnnualYield ≥ 0
Semantics: The parameters are:
Settlement: the settlement/purchase date of the security.
Maturity: the maturity/expiry date of the security.
Issue: the issue date of the security.
Rate: the interest rate of the security.
AnnualYield: the annual yield of the security.
B: indicates the daycount convention to use in the calculation. 4.11.7
If both, Rate and AnnualYield, are 0, the return value is 100.
See also PRICEDISC 6.12.39, PRICEMAT 6.12.40
Summary: Compute the present value (PV) of an investment.
Syntax: PV( Number Rate ; Number Nper ; Number Payment [ ; [ Number Fv = 0 ] [ ; Number PayType = 0 ] ] )
Returns: Currency
Constraints: None.
Semantics: Computes the present value of an investment. The parameters are:
Rate: the interest rate per period.
Nper: the total number of payment periods.
Payment: the payment made in each period.
Fv: the future value; default is 0.
PayType: the type of payment, defaults to 0. It is 0 if payments are due at the end of the period; 1 if they are due at the beginning of the period.
If Rate is 0, then:
If Rate is nonzero, then PV solves this equation:
See also FV 6.12.20, NPER 6.12.29, PMT 6.12.36, RATE 6.12.42
Summary: Compute the interest rate per period of an investment.
Syntax: RATE( Number Nper ; Number Payment ; Number Pv [ ; [ Number Fv = 0 ] [ ; [ Number PayType = 0 ] [ ; Number Guess = 0.1 ] ] ] )
Returns: Percentage
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:
Nper: the total number of payment periods.
Payment: the payment made in each period.
Pv: the present value of the investment.
Fv: the future value; default is 0.
PayType: the type of payment, defaults to 0. It is 0 if payments are due at the end of the period; 1 if they are due at the beginning of the period.
Guess: An estimate of the interest rate to start the iterative computation. If omitted, 0.1 (10%) is assumed.
RATE solves this equation:
See also FV 6.12.20, NPER 6.12.29, PMT 6.12.36, PV 6.12.41
Summary: Calculates the amount received at maturity for a zero coupon bond.
Syntax: RECEIVED( DateParam Settlement ; DateParam Maturity ; Number Investment ; Number Discount [ ; Basis B = 0 ] )
Returns: Number
Constraints: Investment and Discount should be greater than 0, Settlement < Maturity
Semantics: The parameters are:
Settlement: the settlement/purchase date of the security
Maturity: the maturity/expiry date of the security
Investment: the amount of investment in the security
Discount: the discount rate of the security
B: indicates the daycount convention to use in the calculation. 4.11.7
The returned value is:
See also YEARFRAC 6.10.24
Summary: Returns an equivalent interest rate when an investment increases in value.
Syntax: RRI( Number Nper ; Number Pv ; Number Fv )
Returns: Percentage
Constraints: Nper > 0
Semantics: Returns the interest rate given Nper (the number of periods), Pv (present value), and Fv (future value), calculated as follows:
See also FV 6.12.20, NPER 6.12.29, PMT 6.12.36, PV 6.12.41, RATE 6.12.42
Summary: Compute the amount of depreciation at a given period of time using the straightline depreciation method.
Syntax: DDB( Number Cost ; Number Salvage ; Number LifeTime )
Returns: Currency
Constraints: None.
Semantics: Compute the amount of depreciation of an asset at a given period of time using straightline depreciation. The parameters are:
Cost: the total amount paid for the asset.
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.14.
Summary: Compute the amount of depreciation at a given period of time using the SumoftheYears'Digits method.
Syntax: SYD( Number Cost ; Number Salvage ; Number LifeTime ; Number Period )
Returns: Currency
Constraints: None.
Semantics: Compute the amount of depreciation of an asset at a given period of time using the SumoftheYears'Digits method. The parameters are:
Cost: the total amount paid for the asset.
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.14.
See also SLN 6.12.45
Summary: Compute the bondequivalent yield for a treasury bill.
Syntax: TBILLEQ( DateParam Settlement ; DateParam Maturity ; Number Discount )
Returns: Number
Constraints: The maturity date should be less than one year beyond settlement date. Discount is any positive value.
Semantics: The parameters are defined as:
Settlement: the settlement/purchase date of the treasury bill.
Maturity: the maturity/expiry date of the treasury bill.
Discount: the discount rate of the treasury bill.
TBILLEQ is calculated 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.7)
See also TBILLPRICE 6.12.48, TBILLYIELD 6.12.49
Summary: Compute the price per 100 face value for a treasury bill.
Syntax: TBILLPRICE( DateParam Settlement ; DateParam Maturity ; Number Discount )
Returns: Number
Constraints: The maturity date should be less than one year beyond settlement. Discount is any positive value.
Semantics: The parameters are:
Settlement: the settlement/purchase date of the treasury bill.
Maturity: the maturity/expiry date of the treasury bill.
Discount: the discount rate of the treasury bill.
See also TBILLEQ 6.12.47, TBILLYIELD 6.12.49
Summary: Compute the yield for a treasury bill.
Syntax: TBILLYIELD( DateParam Settlement ; DateParam Maturity ; Number Price )
Returns: Number
Constraints: The maturity date should be less than one year beyond settlement. Price is any positive value.
Semantics: The parameters are:
Settlement: the settlement/purchase date of the treasury bill.
Maturity: the maturity/expiry date of the treasury bill.
Price: the price of the treasury bill per 100 face value
See also TBILLEQ 6.12.47, TBILLPRICE 6.12.48
Summary: Calculates the depreciation allowance of an asset with an initial value, an expected useful life, and a final value of salvage for a period specified, using the variablerate declining balance method..
Syntax: VDB( Number Cost ; Number Salvage ; Number LifeTime ; Number StartPeriod ; Number EndPeriod [ ; Number DepreciationFactor = 2 [ ; Logical NoSwitch = FALSE ] ] )
Returns: Number
Constraints: Salvage < Cost, LifeTime > 0, 0 ≤ StartPeriod ≤ LifeTime, StartPeriod ≤ EndPeriod ≤ LifeTime, DepreciationFactor ≥ 0
Semantics: The parameters are:
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.
LifeTime is the number of periods the asset takes to depreciate to its salvage value. LifeTime can be any value greater than 0.
StartPeriod is the point in the asset's life when you want to begin calculating depreciation. StartPeriod can be any value greater than or equal to 0, but cannot be greater than LifeTime.
EndPeriod is the point in the asset's life when you want to stop calculating depreciation. EndPeriod can be any value greater than StartPeriod.
StartPeriod and EndPeriod correspond to the asset's life, relative to the fiscal period. For example, if you want to find the first year's depreciation of an asset purchased at the beginning of the second quarter of a fiscal year, StartPeriod would be 0 and EndPeriod would be 0.75 (1 minus 0.25 of a year).
VDB allows for the use of an initialPeriod option to calculate depreciation for the period the asset is placed in service. VDB uses the fractional part of StartPeriod and EndPeriod to determine the initialPeriod option. If both StartPeriod and EndPeriod have fractional parts, then VDB uses the fractional part of StartPeriod.
DepreciationFactor is an optional argument that specifies the percentage of straightline depreciation you want to use as the depreciation rate. If you omit this argument, VDB uses 2, which is the doubledeclining balance rate. DepreciationFactor can be any value greater than or equal to 0; commonly used rates are 1.25, 1.50, 1.75, and 2.
NoSwitch is an optional argument that you include if you do not want VDB to switch to straightline depreciation for the remaining useful life. Normally, decliningbalance switches to such a straightline calculation when it is greater than the decliningbalance calculation.
If NoSwitch is FALSE or omitted, VDB automatically switches to straightline depreciation when that is greater than decliningbalance depreciation. If NoSwitch is TRUE, VDB never switches to straightline depreciation.
See also DDB 6.12.14, SLN 6.12.45
Summary: Compute the internal rate of return for a nonperiodic series of cash flows.
Syntax: XIRR( NumberSequence Values ; DateSequence Dates [ ; Number Guess = 0.1 ] )
Returns: Number
Constraints: The size of Values and Dates are equal. Values contains at least one positive and one negative cash flow.
Semantics: Compute the internal rate of return for a series of cash flows which is not necessarily periodic. The parameters are:
Values: a series of cash flows. The first cashflow amount is a negative number that represents the investment. The later cash flows are discounted based on the annual discount rate and the timing of the flow. The series of cash flow should contain at least one positive and one negative value.
Dates: a series of dates that corresponds to values. The first date indicates the start of the cash flows. The range of Values and Dates shall be the same size.
Guess: If provided, Guess is an estimate of the interest rate to start the iterative computation. If omitted, the value 0.1 (10%) is assumed. The result of XIRR is the rate at which the XNPV() function will return zero with the given cash flows. There is no closed form for XIRR. Implementations may return an approximate solution using an iterative method, in which case the Guess parameter may be used to initialize the iteration. If the implementation is unable to converge on a solution given a particular Guess, it may return an error.
See also IRR 6.12.24, XNPV 6.12.52
Summary: Compute the net present value of a series of cash flows.
Syntax: XNPV( Number Rate ; ReferenceArray Values ; ReferenceArray Dates )
Returns: Number
Constraints:
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]
Semantics: Compute the net present value for a series of cash flows which is not necessarily periodic. The parameters are:
Rate: discount rate. The value should be greater than 1.
Values: a series of cash flows. The first cashflow amount is a negative number that represents the investment. The later cash flows are discounted based on the annual discount rate and the timing of the flow. The series of cash flow should contain at least one positive and one negative value.
Dates: a series of dates that corresponds to values. The first date indicates the start of the cash flows. If the dimensions of the Values and Dates arrays differ, evaluators shall match value and date pairs rowwise starting from top left.
With N being the number of elements in Values and Dates each, the formula is:
See also NPV 6.12.30
Summary: Calculate the yield of a bond.
Syntax: YIELD( DateParam Settlement ; DateParam Maturity ; Number Rate ; Number Price ; Number Redemption ; Number Frequency [ ; Basis B = 0 ] )
Returns: Number
Constraints: Rate, Price, and Redemption should be greater than 0.
Semantics: The parameters are:
Settlement: the settlement/purchase date of the bond.
Maturity: the maturity/expiry date of the bond.
Rate: the interest rate of the bond.
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 = semiannual; 4 = quarterly.
B: indicates the daycount convention to use in the calculation. 4.11.7
See also PRICE 6.12.38, YIELDDISC 6.12.54, YIELDMAT 6.12.55
Summary: Calculate the yield of a discounted security per 100 currency units of face value.
Syntax: YIELDDISC( DateParam Settlement ; DateParam Maturity ; Number Price ; Number Redemption [ ; Basis B = 0 ] )
Returns: Number
Constraints: Price and Redemption should be greater than 0.
Semantics: The parameters are:
Settlement: the settlement/purchase date of the security.
Maturity: the maturity/expiry date of the security.
Price: the price of the security per 100 currency units face value.
Redemption: the redemption value per 100 currency units face value.
B: indicates the daycount convention to use in the calculation. 4.11.7
The return value is
See also PRICEDISC 6.12.39, YEARFRAC 6.10.24
Summary: Calculate the yield of the security that pays interest on the maturity date.
Syntax: YIELDMAT( DateParam Settlement ; DateParam Maturity ; DateParam Issue ; Number Rate ; Number Price [ ; Basis B = 0 ] )
Returns: Number
Constraints: Rate and Price should be greater than 0.
Semantics: The parameters are:
Settlement: the settlement/purchase date of the security.
Maturity: the maturity/expiry date of the security.
Issue: the issue date of the security.
Rate: the interest rate of the security.
Price: the price of the security per 100 currency units face value.
B: indicates the daycount convention to use in the calculation. 4.11.7
See also PRICE 6.12.38, YIELD 6.12.53, YIELDDISC 6.12.54
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 list of references.
Syntax: AREAS( ReferenceList R )
Returns: Number
Constraints: None
Semantics: Returns the number of areas in the reference list R.
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
Constraints: None
Semantics: The parameters are
Info_Type: the text string which specifies the type of information. Please refer to Table 17  CELL.
R : if R is a reference to a cell, it is the cell whose information will be returned; if R is a reference to a range, the topleft cell in the range is the selected one; if R is omitted, the current cell is used.
Table 17  CELL
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 and does not reference the same sheet as the sheet the expression is evaluated upon. 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 “^” (CIRCUMFLEX ACCENT, U+005E) = centered alignment “\” (REVERSE SOLIDUS, U+005C) = filled alignment otherwise, returns empty string "". 
Summary: Returns the column number(s) of a reference.
Syntax: COLUMN( [ Reference R ] )
Returns: Number
Constraints: AREAS(R) = 1
Semantics: Returns the column number of a reference, where “A” is 1, “B” is 2, and so on. If no parameter is given, the current cell is used. If a reference has multiple columns, an array of numbers is returned with all of the columns in the reference.
See also AREAS 6.13.2, ROW 6.13.29, SHEET 6.13.31
Summary: Returns the number of columns in a given range.
Syntax: COLUMNS( ReferenceArray R )
Returns: Number
Constraints: None
Semantics: Returns the number of columns in the range or array specified. The result is not dependent on the cell content in the range.
See also ROWS 6.13.30
Summary: Count the number of Numbers provided.
Syntax: COUNT( { NumberSequenceList N }^{+} )
Returns: Number
Constraints: One or more parameters.
Semantics: Counts the numbers in the list N. Only numbers in references are counted; all other types are ignored. Errors are not propagated. It is implementationdefined what happens if 0 parameters are passed, but it should be an Error or 0.
See also COUNTA 6.13.7
Summary: Count the number of nonempty values.
Syntax: COUNTA( { Any AnyValue }^{+} )
Returns: Number
Constraints: None.
Semantics: Counts the number of nonblank values. A value is nonblank if it contains any content of any type, including an Error. In a reference, every cell that is not empty is included in the count. An empty string value ("") is not considered blank. Errors contained in a range are considered a nonblank value for purposes of the count; errors do not propagate. Constant expressions or formulas are allowed; these are evaluated and if they produce an Error value the Error value is counted as one nonblank value (and not propagated as an Error). It is implementationdefined what happens if 0 parameters are passed, but it should be an Error or 0.
See also COUNT 6.13.6, ISBLANK 6.13.14
Summary: Count the number of blank cells.
Syntax: COUNTBLANK( ReferenceList R )
Returns: Number
Constraints: None.
Semantics: Counts the number of blank cells in R. A cell is blank if the cell is empty for purposes of COUNTBLANK. If ISBLANK(R) is TRUE, then it is blank. A cell with numeric value zero ('0') is not blank. It is implementationdefined whether or not a cell returning the empty string ("") is considered blank; because of this, there is a (potential) subtle difference between COUNTBLANK and ISBLANK.
Evaluators shall support one Reference as a parameter and may support a ReferenceList as a parameter.
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 )
Returns: Number
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.8).
The values returned may vary depending upon the HOSTUSEREGULAREXPRESSIONS or HOSTUSEWILDCARDS or HOSTSEARCHCRITERIAMUSTAPPLYTOWHOLECELL 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 ]... )
Returns: Number
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.8). 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 or HOSTSEARCHCRITERIAMUSTAPPLYTOWHOLECELL properties. 3.4
See also AVERAGEIFS 6.18.6, COUNT 6.13.6, COUNTA 6.13.7, COUNTBLANK 6.13.8, COUNTIF 6.13.9, SUMIF 6.16.62, SUMIFS 6.16.63, 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.
Syntax: ERROR.TYPE( Error E )
Returns: Number
Constraints: None.
Semantics: Returns a number representing what kind of Error has occurred. Note that unlike most functions, this function does not propagate Error values. Receiving a nonError value returns an Error. In particular, ERROR.TYPE(NA()) returns 7, and ERROR.TYPE applied to a nonError returns an Error.
See also NA 6.13.27
Summary: Returns formula at given reference as text.
Syntax: FORMULA( Reference X )
Returns: String
Constraints: Reference X shall contain a formula.
Semantics: Returns the formula in reference X as a string. The specific syntax of this returned string is implementationdefined. This function is intended to aid debugging by simplifying display of formulas in other cells. Error results of the referred formula cell are not propagated.
See also ISFORMULA 6.13.18
Summary: Returns information about the environment.
Syntax: INFO( Text Category )
Returns: Any (see below)
Constraints: Category shall be valid.
Semantics: Returns information about the environment in the given category.
Evaluators shall support at least the following categories:
Table 18  INFO
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.
See also CELL 6.13.3
Summary: Return TRUE if the referenced cell is blank, else return FALSE.
Syntax: ISBLANK( Scalar X )
Returns: Logical
Constraints: None
Semantics: If X is of type Number, Text, or Logical, return FALSE. If X is a reference to a cell, examine the cell; if it is blank (has no value), return TRUE, but if it has a value, return FALSE. A cell with the empty string is not considered blank. This function does not propagate Error values.
See also ISNUMBER 6.13.22, ISTEXT 6.13.25
Summary: Return TRUE if the parameter has type Error and is not #N/A, else return FALSE.
Syntax: ISERR( Scalar X )
Returns: Logical
Constraints: None
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 #N/A; 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, ISNA 6.13.20, ISNUMBER 6.13.22, ISTEXT 6.13.25, NA 6.13.27
Summary: Return TRUE if the parameter has type Error, else return FALSE.
Syntax: ISERROR( Scalar X )
Returns: Logical
Constraints: None
Semantics: If X is of type Error, returns TRUE, else returns FALSE. Note that this function returns TRUE if given #N/A; 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.
Syntax: ISEVEN( Number X )
Returns: Logical
Constraints: None
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, TRUNC 6.17.8
Summary: Return TRUE if the reference refers to a formula, else return FALSE.
Syntax: ISFORMULA( Reference X )
Returns: Logical
Constraints: None
Semantics: If X refers to a cell whose value is computed by a formula, return TRUE, else return FALSE. A formula itself may compute a constant; in that case it will still return TRUE since it is still a formula. Passing a nonreference, or a reference to more than one cell, is implementationdefined. This function does not propagate Error values.
See also ISTEXT 6.13.25, ISNUMBER 6.13.22
Summary: Return TRUE if the parameter has type Logical, else return FALSE.
Syntax: ISLOGICAL( Scalar X )
Returns: Logical
Constraints: None
Semantics: If X is of type Logical, returns TRUE, else FALSE. Evaluators that do not have a distinct Logical type will return the same value ISNUMBER(X) would return. This function does not propagate Error values.
See also ISTEXT 6.13.25, ISNUMBER 6.13.22
Summary: Return TRUE if the parameter has type Error and is #N/A, else return FALSE.
Syntax: ISERR( Scalar X )
Returns: Logical
Constraints: None
Semantics: If X is #N/A, return TRUE, else return FALSE. Note that if X is a reference, the value being referenced is considered. This function does not propagate Error values.
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.
Syntax: ISNONTEXT( Scalar X )
Returns: Logical
C