Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format

Committee Specification Draft 02 /

Public Review Draft 02

04 November 2019

This stage:

https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.odt (Authoritative)

Previous stage:

https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd01/part4-formula/OpenDocument-v1.3-csprd01-part4-formula.odt (Authoritative)

Latest stage:

https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.odt (Authoritative)

https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html

https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.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.oasis-open.org/office/OpenDocument/v1.3/csprd02/part1-introduction/OpenDocument-v1.3-csprd02-part1-introduction.html.

•Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 2: Packages. https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part2-packages/OpenDocument-v1.3-csprd02-part2-packages.html.

•Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 3: OpenDocument Schema. https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part3-schema/OpenDocument-v1.3-csprd02-part3-schema.html.

•Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format. (this part) https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html.

•XML/RNG schemas and OWL ontologies. https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/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.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os.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.oasis-open.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.oasis-open.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.oasis-open.org/committees/office/ipr.php).

Note that any machine-readable 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:

[OpenDocument-v1.3-part4]

Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format. Edited by Patrick Durusau. 04 November 2019. OASIS Committee Specification Draft 02 / Public Review Draft 02. https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html. Latest stage: https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.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.oasis-open.org/policies-guidelines/trademark for above guidance.

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

for recalculated formulas. 3.5

OpenFormula also defines functions.

OpenFormula does not define:

1.a user interface

2.a general notation for mathematical expressions

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

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 “A-Z” is the range U+0041 through U+005A inclusive.

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

B)It shall implement the syntax defined in these sections on syntax: Criteria 4.11.11; 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.

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

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

E)It shall implement at least the following functions as defined in this specification: ABS 6.16.2 ; ACOS 6.16.3 ; AND 6.15.2 ; ASIN 6.16.7 ; ATAN 6.16.9 ; ATAN2 6.16.10 ; AVERAGE 6.18.3 ; AVERAGEIF 6.18.5 ; CHOOSE 6.14.3 ; COLUMNS 6.13.5 ; COS 6.16.19 ; COUNT 6.13.6 ; COUNTA 6.13.7 ; COUNTBLANK 6.13.8 ; COUNTIF 6.13.9 ; DATE 6.10.2 ; DAVERAGE 6.9.2 ; DAY 6.10.5 ; DCOUNT 6.9.3 ; DCOUNTA 6.9.4 ; DDB 6.12.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

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

G)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 resource-constrained application may choose to not show the traditional glyph (e.g., it may show the [UNICODE] numeric code instead).

A)It shall implement the following functions as defined in this specification: ACCRINT 6.12.2 ; ACCRINTM 6.12.3 ; ACOSH 6.16.4 ; ACOT 6.16.5 ; ACOTH 6.16.6 ; ADDRESS 6.14.2 ; ASINH 6.16.8 ; ATANH 6.16.11 ; AVEDEV 6.18.2 ; BESSELI 6.16.12 ; BESSELJ 6.16.13 ; BESSELK 6.16.14 ; BESSELY 6.16.15 ; BETADIST 6.18.7 ; BETAINV 6.18.8 ; BINOMDIST 6.18.10 ; CEILING 6.17.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

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

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

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

B)It shall implement the complex number type as discussed in the section on Complex Number 4.4, array formulas, and Sheet-local 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

•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 locale-specific 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 case-sensitive, or if search criteria apply to the whole cell.

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

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

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

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.

4)If an expression consists of a named expression (5.11), the result of evaluating the named expression is returned.

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

1.If the result is a Reference and a single non-reference value is needed, it is converted to the referenced value, using the rules of Non-Scalar Evaluation, 3.3, 1.2.

2.If the result an Array, for the display area, apply the rules of Non-Scalar Evaluation, 3.3, 1.1.

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

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

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

The following properties are host-defined:

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

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

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

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

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.

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

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

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

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 implementation-defined. Some functions defined in OpenFormula are labeled as "normalization-sensitive", 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.

Implementations typically support many subtypes of Number, including Date, Time, DateTime, Percentage, fixed-point arithmetic, and arithmetic supporting arbitrarily long integers, and determine the display format from this. All such Number subtypes shall yield TRUE for the ISNUMBER 6.13.22 function.

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 represented as a fraction of a day.

Date is represented by an integer value.

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

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

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

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 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 implementation-defined 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:value-type (OpenDocument Part 3, 19.389) of an error value is string; if the computed value is stored, it is stored in the attribute office:string-value (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.

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.

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 cannot be converted to an array.

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

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

A basis is a subtype of Integer that specifies the day-count convention to be used in a calculation.

This standard defines five day-count conventions, corresponding to widely used current and historical accounting conventions. Each of these five bases defines two things:

1.How to calculate the number of days between two dates, date1 and date2.

2.How to calculate the number of days in each year between two dates, date1 and date2.

Historically day-count 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 day-count 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 1-12

•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

•is-leap-year(year) returns true if year is a leap year, otherwise false.

Note: Some of the day count procedures use intermediate results that contain counter-factual dates, such as February 30th. This is not an error. The above functions work on such dates as well, e.g., day(February 30th) == 30.

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

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

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

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

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

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

•A Number or Logical value. A matching cell content equals the Number or Logical value.

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

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

For <>, if the value is empty it matches non-empty 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 host-defined property HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is true, comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria. For = and <>, if the value is not empty and can not be interpreted as a Number type or one of its subtypes 3.4 applies.

•Other Text value. If the host-defined property HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria.

Evaluators should match the database field name case-insensitively.

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.10 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.8 Criterion.

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 implementation-defined as to whether the values are processed row-at-a-time or column-at-a-time, but it shall be one of these two processing orders. If processing row-at-a-time, the sequence shall be produced by processing each row in turn, from smallest to largest column value (e.g., A1, B1, C1). If processing column-at-a-time, the sequence 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 less-than symbol "<" is typically represented as “<”, the double-quote symbol as “"”, and the ampersand symbol as “&” (alternatively, a numeric character reference can be used).

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

Expression InfixOp Expression |

FunctionName Whitespace* '(' ParameterList ')' |

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

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

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

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

Operators are functions with one or more parameters.

InfixOp ::= ArithmeticOp | ComparisonOp | StringOp | ReferenceOp

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

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

ReferenceOp ::= IntersectionOp | ReferenceConcatenationOp | RangeOp

ReferenceConcatenationOp ::= '~'

Table 1 - Operators defines the associativity and precedence of operators, from 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 |

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

Function calls shall be given a parameter list, though it may be empty. An empty list of parameters is considered a call with 0 parameters, not a call with one parameter that happens to be empty. TRUE() is syntactically a function call with 0 parameters. It is syntactically legitimate to provide empty parameters, though functions 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 ::= ';'

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

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

SheetLocatorOrEmpty ::= SheetLocator | /* empty */

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

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

QuotedSheetName ::= '$'? SingleQuoted

SubtableCell ::= ( Column Row ) | QuotedSheetName

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

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

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

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

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 general-purpose “:” 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 same-document 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 IRI-reference rule (section 2.2 of [RFC3987]) after each pair of consecutive single-quote characters (APOSTROPHE, U+0027) is replaced by one single single-quote character.

Note: The escaping of single-quotes as paired single-quotes is because the IRI is enclosed in single quote characters of the Source.

Resolution of the [RFC3987] IRI reference is host-defined 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 identifies a column or a row, depending on the label range in which its text appears.

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

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

Row | Data | Expression | Result | Comment | ||||||||||||||||

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

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

Row | Data | Expression | Result | Comment | ||||||||||||||||

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

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

NamedExpression ::= SimpleNamedExpression |

SheetLocalNamedExpression | ExternalNamedExpression

SimpleNamedExpression ::= Identifier |

'$$' (Identifier | SingleQuoted)

SheetLocalNamedExpression ::=

QuotedSheetName '.' SimpleNamedExpression

ExternalNamedExpression ::=

Source (SimpleNamedExpression | SheetLocalNamedExpression)

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

Identifier ::= ( LetterXML

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

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

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

Error names shall have the following syntax:

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

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.

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

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

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

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

OpenFormula defines commonly used operators and functions.

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

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

–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 implementation-defined.

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

•Comment: Explanatory comment.

•See also A list of related operators and functions.

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

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

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

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

•Intersect this with the provided reference to multiple cells

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

See also Non-Scalar Evaluation 3.3

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

•Text: The specific conversion is implementation-defined; an evaluator may return 0, an Error value, or the results of its attempt to convert the Text value to a Number (and fall back to 0 or Error if it fails to do so). Evaluators may apply VALUE 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 “precision-as-shown” is true, then convert the number to the closest possible representation of the displayed number. If the cell is empty (blank), use 0 (zero) as the value. Evaluators may choose to convert references to Text in a different manner than they handle converting embedded Text to a Number.

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

Syntax: Number Left + Number Right

Semantics: Adds numbers together.

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

Summary: Subtract the second number from the first.

Syntax: Number Left - Number Right

Semantics: Subtracts one number from another number.

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

Summary: Multiply two numbers.

Syntax: Number Left * Number Right

Semantics: Multiplies numbers together.

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

Summary: Divide the first number by the second.

Syntax: Number Left / Number Right

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

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

Summary: Exponentiation (Power).

Syntax: Number Left ^ Number Right

Semantics: Returns POWER(Left, Right).

See also Infix Operator "*" 6.4.4, AND 6.15.2, NOT 6.15.7, POWER 6.16.46

Summary: Report if two values are equal

Syntax: Scalar Left = Scalar Right

See also Infix Operator "<>" 6.4.8

Summary: Report if two values are not equal

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

See also Infix Operator "=" 6.4.7, 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 ">="

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

Summary: Concatenate two strings.

Syntax: Text Left & Text Right

Semantics: Concatenates two text (string) values.

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

See also Infix Operator "+" 6.4.2, CONCATENATE 6.20.6

Summary: Computes an inclusive range given two references

Syntax: Reference Left : Reference Right

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

Summary: Compute the intersection of two references

Syntax: Reference Left ! Reference Right

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

See also Infix Operator Reference Union 6.4.13

Summary: Concatenate two references

Syntax: Reference Left ~ Reference Right

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

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

Summary: Divide the operand by 100

Semantics: Computes Left / 100.

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

Summary: No operation; returns its one argument.

See also Infix Operator "+" 6.4.2

Summary: Negate its one argument.

Semantics: Computes 0 - Right.

See also Infix Operator "-" 6.4.3

Matrix functions operate on matrices.

A matrix with M rows and N columns is defined by

Summary: Calculates the determinant of a matrix.

Syntax: MDETERM( ForceArray Array A )

Constraints: Only square matrices are allowed.

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

Summary: Returns the inverse of a matrix.

Syntax: MINVERSE( ForceArray Array A )

Constraints: Only square matrices are allowed.

Summary: Multiplies the matrices A and B.

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

Constraints: COLUMNS(A) = ROWS(B)

See also COLUMNS 6.13.5, ROWS 6.13.30

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

Constraints: The dimension has to be greater than zero.

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

Summary: Returns the transpose of a matrix.

Summary: Returns bitwise “and” of its parameters

Syntax: BITAND( Integer X ; Integer Y )

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 )

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

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 )

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 )

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

See also BITAND 6.6.2, BITXOR 6.6.6, BITLSHIFT 6.6.3, INT 6.17.2

Summary: Returns bitwise “exclusive or” of its parameters

Syntax: BITXOR( Integer X ; Integer Y )

See also BITAND 6.6.2, BITOR 6.6.4, OR 6.15.8

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

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

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

See also FIND 6.20.9 , LEFTB 6.7.3 , RIGHTB 6.7.7

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

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

Semantics: As LEFT, but using a byte position.

See also LEFT 6.20.12, RIGHT 6.20.19, RIGHTB 6.7.7

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

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

See also LEN 6.20.13, LEFTB 6.7.3, RIGHTB 6.7.7

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

Semantics: As MID, but using byte positions.

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

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

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

Semantics: As REPLACE, but using byte positions.

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

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

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

Semantics: As RIGHT, but using byte positions.

See also RIGHT 6.20.19, LEFTB 6.7.3

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

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

Semantics: As SEARCH, but using byte positions.

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

Functions for complex numbers.

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

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

Summary: Returns the absolute value of a complex number.

Summary: Returns the imaginary coefficient of a complex number.

Syntax: IMAGINARY( Complex X )

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

Summary: Returns the complex argument of a complex number.

Syntax: IMARGUMENT( Complex X )

Summary: Returns the complex conjugate of a complex number.

Syntax: IMCONJUGATE( Complex X )

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

Summary: Returns the cosine of a complex number.

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

Summary: Returns the hyperbolic cosine of a complex number.

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.

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

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.

Semantics: Equivalent to the following:

See also IMDIV 6.8.12, IMSIN 6.8.20

Summary: Returns the hyperbolic cosecant of a complex number.

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

See also IMSINH 6.8.21, CSCH 6.16.24

Summary: Divides the first number by the second.

Syntax: IMDIV( Complex X ; Complex Y )

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

Division by zero returns an Error.

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

Summary: Returns the natural logarithm of a complex number.

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.

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.

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)

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

Summary: Returns the real coefficient of a complex number.

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

Summary: Returns the sine of a complex number.

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

Summary: Returns the hyperbolic sine of a complex number.

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.

Semantics: Equivalent to the following:

See also IMCOS 6.8.7, IMDIV 6.8.12

Summary: Returns the hyperbolic secant of a complex number.

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

See also IMCOSH 6.8.8, IMDIV 6.8.12, SECH 6.16.57

Summary: Returns the square root of a complex number.

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 )

Semantics: Subtract complex number Y from X.

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

Syntax: IMSUM( { ComplexSequence N }+ )

Summary: Returns the tangent of a complex number

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

See also IMDIV 6.8.12, IMSIN 6.8.20, IMCOS 6.8.7, IMCOT 6.8.25

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

The results of database functions may change when the values of the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties change. 3.4

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

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

See also AVERAGE 6.18.3, COUNT 6.13.6, DSUM 6.9.11, DCOUNT 6.9.3, SUM 6.16.61

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

See also COUNT 6.13.6, COUNTA 6.13.7, DCOUNTA 6.9.4, DSUM 6.9.11

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

See also COUNT 6.13.6, COUNTA 6.13.7, DCOUNT 6.9.3, DSUM 6.9.11

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

See also DMAX 6.9.6, DMIN 6.9.7, DSUM 6.9.11

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

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

See also MAX 6.18.45, DMIN 6.9.7, MIN 6.18.48

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

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

See also MIN 6.18.48, DMAX 6.9.6, MAX 6.18.45

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

Semantics: Multiply together only the data records in database D field F that match criteria C.

See also SUM 6.16.61, DSUM 6.9.11

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

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

See also STDEV 6.18.72, DSTDEVP 6.9.10

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

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

See also STDEVP 6.18.74, DSTDEV 6.9.9

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

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

See also SUM 6.16.61, DMIN 6.9.7, DMAX 6.9.6

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

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

See also VAR 6.18.82, DVARP 6.9.13

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

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

See also VARP 6.18.84, DVAR 6.9.12

Summary: Constructs a date from year, month, and day of month.

Syntax: DATE( Integer Year ; Integer Month ; Integer Day )

See also TIME 6.10.17, DATEVALUE 6.10.4

Summary: Returns the difference in years, months, or days of two date numbers.

Syntax: DATEDIF( DateParam StartDate ; DateParam EndDate ; Text Format )

Semantics: Compute difference of StartDate and EndDate, in the units given by Format.

Format | Returns the number of |

y | Years |

m | Months. If there is not a complete month between the dates, 0 will be returned. |

d | Days |

md | Days, ignoring months and years |

ym | Months, ignoring years |

yd | Days, ignoring years |

See also DAYS360 6.10.7, DAYS 6.10.6, Infix Operator “-” 6.4.3

Summary: Returns the date serial number from given text.

See also TIME 6.10.17, DATE 6.10.2, TIMEVALUE 6.10.18, VALUE 6.13.34

Summary: Returns the day from a date.

Semantics: Returns the day portion of 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 )

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 360-day year

Syntax: DAYS360( DateParam StartDate ; DateParam EndDate [ ; Logical Method = FALSE ] )

The US/NASD method (30US/360):

3.Otherwise, if StartDate's day-of-month is the last day of February, it 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):

1.Truncate date values, set sign = 1.

2.If StartDate is after EndDate then swap dates and set sign = -1.

3.If StartDate's day-of-month is 31, it is changed to 30.

4.If EndDate's day-of-month 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 )

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

See also DAY 6.10.5, EDATE 6.10.8

Summary: Extracts the hour (0 through 23) from a time.

Semantics: Extract from T the hour value, 0 through 23, as per a 24-hour clock. This is equal to:

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 )

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.

Semantics: Extract from T the minute value, 0 through 59, as per a clock. This is equal to:

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 )

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.

Semantics: Returns the whole number of work days between two dates.

Summary: Returns the serial number of the current date and time.

Semantics: This returns the current day and time serial number, using the current locale. If you want only the serial number of the current day, use TODAY 6.10.19.

See also DATE 6.10.2, TIME 6.10.17, TODAY 6.10.19

HourFraction = (DayFraction * 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 )

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.

See also DATE 6.10.2, INT 6.17.2

Summary: Returns a time serial number from given text.

See also TIME 6.10.17, DATE 6.10.2, DATEVALUE 6.10.4, VALUE 6.13.34

Summary: Returns the serial number of today.

Semantics: This returns the current day's serial number, using current locale. This only returns the date, not the datetime value. For the specific time of day as well, use NOW 6.10.15.

See also TIME 6.10.17, NOW 6.10.15

Syntax: WEEKDAY( DateParam D [ ; Integer Type = 1 ] )

1.When Type is 1, Sunday is the first day of the week, with value 1; Saturday has value 7.

2.When Type is 2, Monday is the first day of the week, with value 1; Sunday has value 7.

3.When Type is 3, Monday is the first day of the week, with value 0; Sunday has value 6.

4.When Type is 11, Monday is the first day of the week, with value 1; Sunday has value 7.

5.When Type is 12, Tuesday is the first day of the week, with value 1; Monday has value 7.

6.When Type is 13, Wednesday is the first day of the week, with value 1; Tuesday has value 7.

7.When Type is 14, Thursday is the first day of the week, with value 1; Wednesday has value 7.

8.When Type is 15, Friday is the first day of the week, with value 1; Thursday has value 7.

9.When Type is 16, Saturday is the first day of the week, with value 1; Friday has value 7.

10. When Type is 17, Sunday is the first day of the week, with value 1; Saturday has value 7.

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

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.

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: Extracts the year from a date given in the current locale of the evaluator.

See also MONTH 6.10.13, DAY 6.10.5, VALUE 6.13.34

Summary: Extracts the number of years (including fractional part) between two dates

Syntax: YEARFRAC( DateParam StartDate ; DateParam EndDate [ ; Basis B = 0 ] )

Semantics: Computes the fraction of the number of years between a StartDate and EndDate.

B indicates the day-count 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 ] )

Mode is an optional parameter that determines how the results are returned:

Mode | Effect |

0 or missing | Data converted to number using VALUE in the number style's locale of the default table cell style |

1 | Data converted to number using VALUE in the English-US (en_US) locale |

2 | Data retrieved as text (not converted to number) |

Summary: Creation of a hyperlink involving an evaluated expression.

Syntax: HYPERLINK( Text IRI [ ; Text|Number FunctionResult ] )

The financial functions are defined for use in financial calculations.

Summary: Calculates the accrued interest for securities with periodic interest payments.

Constraints: Issue < First < Settlement ; Coupon > 0; Par > 0

Frequency is one of the following values:

Frequency | Frequency of coupon payments |

1 | Annual |

2 | Semiannual |

4 | Quarterly |

12 | Monthly |

•Par: The security's par value, that is, the principal to be paid at maturity.

•B: Indicates the day-count 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.

Constraints: Coupon > 0; Par > 0

Semantics: Calculates the accrued interest for securities that pay at maturity.

•Par: The security's par value, that is, the principal to be paid at maturity.

•B: Indicates the day-count convention to use in the calculation. 4.11.7

See also ACCRINT 6.12.2

Constraints: Cost > 0; PurchaseDate ≤ FirstPeriodEndDate; Salvage ≥ 0; Period ≥ 0; Rate > 0

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

•B: Indicates the day-count convention to use in the calculation. 4.11.7

When Period = 0:

$$\mathit{AMORLINC}=\mathit{Cost}\cdot \mathit{Rate}\cdot \mathit{YEARFRAC}(\mathit{PurchaseDate},\mathit{FirstPeriodEndDate},\mathit{Basis})$$

For full periods, where Period > 0, the depreciation is Cost * Rate

$$t=\frac{\mathit{Cost}-\mathit{Salvage}}{\mathit{Cost}\cdot \mathit{Rate}}$$

For the last period, possibly a partial period: the depreciation = Cost - Salvage - accumulated-depreciation, where accumulated-depreciation is the sum of the depreciation in period 0 plus any full period depreciations.

$$\mathit{AMORLINC}=\mathit{Cost}\cdot \mathit{Rate}$$

When Period > depreciated life of the asset, i.e., when Period > (Cost - Salvage) / (Cost * Rate) then the depreciation is 0.

$$\mathit{AMORLINC}=0$$

Note: The behavior of this function is implementation-defined in cases where PurchaseDate = FirstPeriodEndDate.

See also DB 6.12.13, DDB 6.12.14, YEARFRAC 6.10.24

Syntax: COUPDAYBS( DateParam Settlement ; DateParam Maturity ; Integer Frequency [ ; Basis B = 0 ] )

Constraints: Settlement < Maturity

Frequency is one of the following values:

Frequency | Frequency of coupon payments |

1 | Annual |

2 | Semiannual |

4 | Quarterly |

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

Constraints: Settlement < Maturity

Frequency is one of the following values:

Frequency | Frequency of coupon payments |

1 | Annual |

2 | Semiannual |

4 | Quarterly |

Semantics: Calculates the number of days in the coupon period containing the settlement date.

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

Constraints: Settlement < Maturity

Frequency is one of the following values:

Frequency | Frequency of coupon payments |

1 | Annual |

2 | Semiannual |

4 | Quarterly |

Semantics: Calculates the number of days between the settlement date and the next coupon date.

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

Constraints: Settlement < Maturity

Frequency is the number of coupon payments per year. Frequency is one of the following values:

Frequency | Frequency of coupon payments |

1 | Annual |

2 | Semiannual |

4 | Quarterly |

B indicates the day-count 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 ] )

Frequency | Frequency of coupon payments |

1 | Annual |

2 | Semiannual |

4 | Quarterly |

B indicates the day-count 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 ] )

Constraints: Settlement < Maturity

Frequency is the number of coupon payments per year. Frequency is one of the following values:

Frequency | Frequency of coupon payments |

1 | Annual |

2 | Semiannual |

4 | Quarterly |

B indicates the day-count 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.

Constraints: Rate > 0; Value > 0; 1 ≤ Start ≤ End ≤ Periods

Type is one of the following values:

Type | Maturity date |

0 | due at the end |

1 | due at the beginning |

Semantics: Calculates the cumulative interest payment.

See also IPMT 6.12.23, CUMPRINC 6.12.12

Summary: Calculates a cumulative principal payment.

Constraints: Type is one of the following values:

Type | Maturity date |

0 | due at the end |

1 | due at the beginning |

Type | Maturity date |

0 | due at the end |

1 | due at the beginning |

Semantics: Calculates the cumulative principal payment.

See also PPMT 6.12.37 , CUMIPMT 6.12.11

Summary: Compute the depreciation allowance of an asset.

Constraints: Cost > 0, Salvage ≥ 0, LifeTime > 0; Period > 0; 0 < Month < 13

The rate is calculated as follows:

For the first period the residual value is

For all periods, where Period ≤ LifeTime, the residual value is calculated by

If Month was specified, the residual value for the period after LifeTime becomes

The depreciation allowance for the first period is

For all other periods the allowance is calculated by

For all periods, where Period > LifeTime + 1 – INT(Month / 12), the depreciation allowance is zero.

See also DDB 6.12.14, SLN 6.12.45, INT 6.17.2

Summary: Compute the amount of depreciation at a given period of time.

Constraints: Cost ≥ 0, Salvage ≥ 0, Salvage ≤ Cost, 1 ≤ Period ≤ LifeTime, DeclinationFactor > 0

•LifeTime: the number of periods that the depreciation will occur over.

•Period: the period for which a depreciation value is specified.

The depreciation each period is calculated as

Thus the asset depreciates at rate until the book value is Salvage value.

To allow also non-integer Period values this algorithm may be used:

See also SLN 6.12.45, VDB 6.12.50, MIN 6.18.48

Summary: Returns the discount rate of a security.

Constraints: Settlement < Maturity

Semantics: Calculates the discount rate of a security.

$$\mathit{DISC}=\frac{\frac{\mathit{Redemption}-\mathit{Price}}{\mathit{Redemption}}}{\mathit{YEARFRAC}(\mathit{Settlement},\mathit{Maturity},B)}$$

See also YEARFRAC 6.10.24

Summary: Converts a fractional dollar representation into a decimal representation.

Syntax: DOLLARDE( Number Fractional ; Integer Denominator )

Semantics: Converts a fractional dollar representation into a decimal representation.

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 )

Semantics: Converts a decimal dollar representation into a fractional representation.

See also DOLLARDE 6.12.16, TRUNC 6.17.8

Summary: Returns the Macaulay duration of a fixed interest security in years

Constraints: Yield ≥0, Coupon ≥ 0, Settlement ≤ Maturity; Frequency = 1, 2, 4

Semantics: Computes the Macaulay duration, given:

See also MDURATION 6.12.26

Summary: Returns the net annual interest rate for a nominal interest rate.

Syntax: EFFECT( Number Rate ; Integer Payments )

Constraints: Rate ≥ 0; Payments > 0

Summary: Compute the future value (FV) of an investment.

Semantics: Computes the future value of an investment. The parameters are:

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 )

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.

Constraints: Settlement < Maturity

The return value for this function is:

$$\mathit{INTRATE}=\frac{\frac{\mathit{Redemption}-\mathit{Investment}}{\mathit{Investment}}}{\mathit{YEARFRAC}(\mathit{Settlement};\mathit{Maturity};B)}$$

See also RECEIVED 6.12.43, YEARFRAC 6.10.24

Summary: Returns the amount of an annuity payment going towards interest.

•Period: The period for which the interest payment is computed.

•Nper: The total number of periods for which the payments are made

•FV: The future value (optional) at the end of the periods. Zero if omitted.

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

Semantics: Compute the internal rate of return for a series of cash flows.

The result of IRR is the rate at which the NPV() function will return zero with the given values.

See also NPV 6.12.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 )

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.

Constraints: Yield ≥ 0, Coupon ≥ 0, Settlement ≤ Maturity; Frequency = 1, 2, 4

Semantics: Computes the modified Macaulay duration, given:

The modified duration is computed as follows:

$$\begin{array}{c}\mathit{duration}=\mathit{DURATION}(\mathit{Settlement},\mathit{Maturity},\mathit{Coupon},\mathit{Yield},\mathit{Frequency},B)\\ \mathit{MDURATION}=\frac{\mathit{duration}}{1+\left(\frac{\mathit{Yield}}{\mathit{Frequency}}\right)}\end{array}$$

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 )

Constraints: Values shall contain at least one positive value and at least one negative value.

Computes the modified internal rate of return, which is:

where N is the number of incomes and payments in Values (total).

See also IRR 6.12.24, NPV 6.12.30

Summary: Compute the annual nominal interest rate.

Syntax: NOMINAL( Number EffectiveRate ; Integer CompoundingPeriods )

Constraints: EffectiveRate > 0 , CompoundingPeriods > 0

The mapping between nominal rate and effective rate is

Summary: Compute the number of payment periods for an investment.

Syntax: NPER( Number Rate ; Number Payment ; Number Pv [ ; [ Number Fv ] [ ; Number PayType ] ] )

Semantics: Computes the number of payment periods for an investment. The parameters are:

If Rate is 0, then NPER solves this equation:

If Rate is non-zero, then NPER solves this equation:

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

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

Constraints: Rate, Yield, and Redemption should be greater than 0.

•Redemption: the redemption value per 100 currency units face value

•Frequency: the number of interest payments per year. 1 = annual; 2 = semiannual; 4 = quarterly.

•B: indicates the day-count convention to use in the calculation. 4.11.7

See also ODDLPRICE 6.12.33 , ODDFYIELD 6.12.32

•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 day-count convention to use in the calculation. 4.11.7

See also ODDLYIELD 6.12.34 , ODDFPRICE 6.12.31

•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 day-count 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.

Constraints: Rate, Price, and Redemption should be greater than 0.

•Redemption: the redemption value per 100 currency units face value

•Frequency: the number of interest payments per year. 1 = annual; 2 = semiannual; 4 = quarterly.

•B: indicates the day-count 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 )

Constraints: Rate > 0; CurrentValue > 0; SpecifiedValue > 0

Summary: Compute the payment made each period for an investment.

Semantics: Computes the payment made each period for an investment. The parameters are:

If Rate is 0, the following equation is solved:

If Rate is nonzero, then PMT solves this equation:

See also FV 6.12.20, NPER 6.12.29, PV 6.12.41, RATE 6.12.42

Constraints: Rate and Present should be greater than 0. 0 < Period < Nper.

Semantics: The parameters are:

Constraints: Rate, AnnualYield, and Redemption should be greater than 0; Frequency = 1, 2 or 4.

•AnnualYield: a measure of the annual yield of a security (compounded at each interest payment).

•Redemption: the redemption value per 100 currency units face value.

•Frequency: the number of interest payments per year. 1 = annual; 2 = semiannual; 4 = quarterly.

•Bas: indicates the day-count 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.

Constraints: Discount and Redemption should be greater than 0.

Semantics: The parameters are:

•Redemption: the redemption value per 100 currency units face value.

•B: indicates the day-count convention to use in the calculation. 4.11.7

See also PRICE 6.12.38, PRICEMAT 6.12.40, YIELDDISC 6.12.54

Constraints: Settlement < Maturity, Rate ≥ 0, AnnualYield ≥ 0

Semantics: The parameters are:

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.

Semantics: Computes the present value of an investment. The parameters are:

If Rate is nonzero, then PV solves this equation:

See also FV 6.12.20, NPER 6.12.29, PMT 6.12.36, RATE 6.12.42

Summary: Compute the interest rate per period of an investment.

Constraints: If Nper is 0 or less than 0, the result is an Error.

Semantics: Computes the interest rate of an investment. The parameters are:

See also FV 6.12.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.

Constraints: Investment and Discount should be greater than 0, Settlement < Maturity

Semantics: The parameters are:

Settlement: the settlement/purchase date of the security

The returned value is:

$$\mathit{RECEIVED}=\frac{\mathit{Investment}}{1-\mathit{Discount}\cdot \mathit{YEARFRAC}(\mathit{Settlement};\mathit{Maturity};B)}$$

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 )

See also FV 6.12.20, NPER 6.12.29, PMT 6.12.36, PV 6.12.41, RATE 6.12.42

Syntax: DDB( Number Cost ; Number Salvage ; Number LifeTime )

•Salvage: the salvage value at the end of the LifeTime (often 0)

•LifeTime: the number of periods that the depreciation will occur over. A positive integer.

For alternative methods to compute depreciation, see DDB 6.12.14.

Syntax: SYD( Number Cost ; Number Salvage ; Number LifeTime ; Number Period )

•Salvage: the salvage value at the end of the LifeTime (often 0).

•LifeTime: the number of periods that the depreciation will occur over. A positive integer.

•Period: the period for which the depreciation value is specified.

For other methods of computing depreciation, see DDB 6.12.14.

See also SLN 6.12.45

Summary: Compute the bond-equivalent yield for a treasury bill.

Syntax: TBILLEQ( DateParam Settlement ; DateParam Maturity ; Number Discount )

Semantics: The parameters are defined as:

where DSM is the number of days between settlement and maturity computed according to the 360 days per year basis (basis 2, 4.11.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 )

Semantics: The parameters are:

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 )

Semantics: The parameters are:

See also TBILLEQ 6.12.47, TBILLPRICE 6.12.48

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.

See also DDB 6.12.14, SLN 6.12.45

Summary: Compute the internal rate of return for a non-periodic series of cash flows.

Syntax: XIRR( NumberSequence Values ; DateSequence Dates [ ; Number Guess = 0.1 ] )

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 ; Reference|Array Values ; Reference|Array Dates )

Number of elements in Values equals number of elements in Dates.

All elements of Values are of type Number.

All elements of Dates are of type Number.

All elements of Dates ≥ Dates[1]

With N being the number of elements in Values and Dates each, the formula is:

Summary: Calculate the yield of a bond.

Constraints: Rate, Price, and Redemption should be greater than 0.

Semantics: The parameters are:

•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 day-count 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.

Constraints: Price and Redemption should be greater than 0.

Semantics: The parameters are:

•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 day-count convention to use in the calculation. 4.11.7

The return value is

$$\mathit{YIELDDISC}=\frac{\frac{\mathit{Redemption}}{\mathit{Price}}-1}{\mathit{YEARFRAC}(\mathit{Settlement};\mathit{Maturity};\mathit{Basis})}$$

See also PRICEDISC 6.12.39, YEARFRAC 6.10.24

Summary: Calculate the yield of the security that pays interest on the maturity date.

Constraints: Rate and Price should be greater than 0.

Semantics: The parameters are:

•Price: the price of the security per 100 currency units face value.

•B: indicates the day-count convention to use in the calculation. 4.11.7

See also PRICE 6.12.38, YIELD 6.12.53, YIELDDISC 6.12.54

Summary: Returns the number of areas in a given list of references.

Syntax: AREAS( ReferenceList R )

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

•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 top-left 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 = MMM-D-YY, MM-D-YY and similar formats D2 = DD-MM D3 = MM-YY D4 = DD-MM-YYYY HH:MM:SS D5 = MM-DD 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 ] )

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( Reference|Array R )

Summary: Count the number of Numbers provided.

Syntax: COUNT( { NumberSequenceList N }+ )

Constraints: One or more parameters.

Summary: Count the number of non-empty values.

Syntax: COUNTA( { Any AnyValue }+ )

See also COUNT 6.13.6, ISBLANK 6.13.14

Summary: Count the number of blank cells.

Syntax: COUNTBLANK( ReferenceList R )

See also COUNT 6.13.6, COUNTA 6.13.7, COUNTIF 6.13.9, ISBLANK 6.13.14

Summary: Count the number of cells in a range that meet a criteria.

Syntax: COUNTIF( ReferenceList R ; Criterion C )

Constraints: Does not accept constant values as the reference parameter.

Semantics: Counts the number of cells in the reference range R that meet the Criterion C (4.11.8).

The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. 3.4

See also COUNT 6.13.6, COUNTA 6.13.7, COUNTBLANK 6.13.8, COUNTIFS 6.13.10, SUMIF 6.16.62, Infix Operator "=" 6.4.7, Infix Operator "<>" 6.4.8, Infix Operator Ordered Comparison ("<", "<=", ">", ">=") 6.4.9

Summary: Count the number of cells that meet multiple criteria in multiple ranges.

Syntax: COUNTIFS( Reference R1 ; Criterion C1 [ ; Reference R2 ; Criterion C2 ]... )

Constraints: Does not accept constant values as the reference parameter.

Semantics: Counts the number of cells that meet the Criterion C1 in the reference range R1 and the Criterion C2 in the reference range R2, and so on (4.11.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 HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL 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.

Summary: Returns formula at given reference as text.

Syntax: FORMULA( Reference X )

Constraints: Reference X shall contain a formula.

Summary: Returns information about the environment.

Constraints: Category shall be valid.

Semantics: Returns information about the environment in the given category.

Evaluators shall support at least the following categories:

Category | Meaning | Type |

"directory" | Current directory. This shall be formatted so file names can be appended to the result (e.g., on POSIX and Windows systems it shall end with the separator “/” or “\” respectively). | Text |

"memavail" | Amount of memory “available”, in bytes. On many modern (virtual memory) systems this value is not really available, but a system should return 0 if it is known that there is no more memory available, and greater than 0 otherwise | Number |

"memused" | Amount of memory used, in bytes, by the data | Number |

"numfile" | Number of active worksheets in files | Number |

"osversion" | Operating system version | Text |

"origin" | The top leftmost visible cell's absolute reference prefixed with “$A:”. In locales where cells are ordered right-to-left, the top rightmost visible cell is used instead. | Text |

"recalc" | Current recalculation mode. If the locale is English, this is either "Automatic" or "Manual" (the exact text depends on the locale) | Text |

"release" | The version of the implementation. | Text |

"system" | The type of the operating system. | Text |

"totmem" | Total memory available in bytes, including the memory already used. | Number |

Evaluators may support other categories.

Summary: Return TRUE if the referenced cell is blank, else return FALSE.

See also ISNUMBER 6.13.22, ISTEXT 6.13.25

Summary: Return TRUE if the parameter has type Error and is not #N/A, else return FALSE.

Semantics: If X is of type Error, and ISNA(X) is not true, returns TRUE. Otherwise it returns FALSE. Note that this function returns FALSE if given #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.

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.

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

See also ISTEXT 6.13.25, ISNUMBER 6.13.22

Summary: Return TRUE if the parameter has type Logical, else return FALSE.

See also ISTEXT 6.13.25, ISNUMBER 6.13.22

Summary: Return TRUE if the parameter has type Error and is #N/A, else return FALSE.

See also ERROR.TYPE 6.13.11, ISERROR 6.13.16, ISERR 6.13.15, ISNUMBER 6.13.22, ISTEXT 6.13.25, NA 6.13.27

Summary: Return TRUE if the parameter does not have type Text, else return FALSE.

Semantics: If X is of type Text, ISNONTEXT returns FALSE, else TRUE. If X is a reference, it examines what X references. References to empty cells are not considered text, so for reference to an empty cell ISNONTEXT will return TRUE. Empty Cell 4.7 This function does not propagate Error values.

ISNONTEXT(X) is equivalent to NOT(ISTEXT(X))

See also ISNUMBER 6.13.22, ISLOGICAL 6.13.19, ISTEXT 6.13.25, NOT 6.15.7

Summary: Return TRUE if the parameter has type Number, else return FALSE.

See also ISTEXT 6.13.25, ISLOGICAL 6.13.19

Summary: Return TRUE if the value is even, else return FALSE.

Semantics: First, compute X1 = TRUNC(X). Then, if X1 is odd (a division by 2 has a remainder of 1), return TRUE, else return FALSE. The result is implementation-defined if given a Logical value; an evaluator may return either an Error or the result of converting the Logical value to a Number (per Conversion to Number 6.3.5 ).

See also ISEVEN 6.13.17, TRUNC 6.17.8

Summary: Return TRUE if the parameter is of type reference, else return FALSE.

See also ISNUMBER 6.13.22, ISTEXT 6.13.25

Summary: Return TRUE if the parameter has type Text, else return FALSE.

ISTEXT(X) is equivalent to NOT(ISNONTEXT(X)).

Semantics: If X is of type Text, returns TRUE, else FALSE. References to empty cells are NOT considered Text. If X is a reference, examines what X references. References to empty cells are NOT considered Text, so a reference to an empty cell will return FALSE. Empty Cell 4.7 This function does not propagate Error values.

See also ISNONTEXT 6.13.21, ISNUMBER 6.13.22, ISLOGICAL 6.13.19

Summary: Return the number of a value.

See also T 6.20.22, VALUE 6.13.34

Summary: Return the constant Error value #N/A.

Constraints: Shall have 0 parameters

Semantics: This function takes no arguments and returns the Error #N/A.

See also ERROR.TYPE 6.13.11, ISERROR 6.13.16

Summary: Convert text to number, in a locale-independent way.

Syntax: NUMBERVALUE( Text X [ ; Text DecimalSeparator [ ; Text GroupSeparator ] ] )

Constraints: LEN(DecimalSeparator) = 1, DecimalSeparator shall not appear in GroupSeparator

Semantics: Converts given Text value X into Number. If X is a Reference, it is first dereferenced.

X is transformed according to the following rules:

4.If the first character of the resulting string is a period FULL STOP (U+002E) then prepend a zero