2.1.860 Part 1 Section 18.17.2, Syntax

  • Article

a.   The standard does not specify the EBNF standard used to describe formula.

Office defines the syntax rules in this subclause follow the system shown in ISO/IEC 14977: literal text is surrounded by double-quotes (or by apostrophes); the left-square-bracket and right-square-bracket designate the start and end of an option; the left-curly-bracket and right-curly-bracket designate the start and end of an sequence of zero-or-more items; the vertical-line indicates an alternative; and each rule ends with a semicolon. Whenever hyphen is used as the exception-symbol (as per ISO/IEC 14977), it is surrounded by white space, and further clarified by a comment.

b.   The standard does not specify the set of productions for formula using EBNF.

Office writes the productions for formula using the following EBNF:

 formula=
   expression  ;
 expression= {space}, nospace-expression, {space};
 nospace-expression=
   "(",  expression,  ")"  | 
   constant  | 
   prefix-operator,  expression  | 
   expression,  infix-operator,  expression  | 
   expression,  postfix-operator  | 
   cell-reference  |
   function-call  | 
   name-reference  |
 structure-reference ;

c.   The standard does not state that structured referencing in formulas is allowed.

Office allows structured referencing in formulas.

d.   The standard provides a rather loose definition of value space for arithmetic terms in an expression.

Excel uses IEC 60559's double precision, excluding denormalized numbers.

e.   The standard does not define how behavior of range values differ between array formulas and normal formulas.

In Office, the way in which input ranges are interpreted and output values are understood to relate to cells sharing the formula may mean one of two things based on whether the formula was an array formula or not.

For an array-entered formula: All range arguments are interpreted to be their full range. If the result of the formula is an array, the values of the array are meant to be returned across all of the cells in the sheet sharing the formula. (When the size of the range for an array formula exceeds in either dimension the size of the returned array, the excess cells take on a value of #N/A.)

For a normal (non-array) formula:

  • Implicit intersection is performed on all arguments to functions except for those that allow a range.

  • If the formula results in an array, only the first value from the array is returned to the cell.

  • In Office, if implicit intersection cannot be performed, #VALUE! is returned to the cell.

Implicit intersection is determined as follows: When a range is passed to a function which expects only a single cell, a test is made to discover whether the calling cell intersects that range at any point horizontally or vertically. If it does, the cell at the point of intersection is passed to the function. [Example: The formula ABS(B1:B3) is entered into A2. Because the ABS function does not expect a range, implicit intersection is performed. A2 intersects B1:B3 horizontally on row 2, and so the value in B2 is passed into the function. end example]

f.   The standard does specify which function's arguments can be a range.

Office defines the list of function arguments that allow a range is as follows:

  • ACCRINT - all arguments

  • ACCRINTM - all arguments

  • AMORDEGRC - all arguments

  • AMORLINC - all arguments

  • AND - all arguments

  • AREAS – reference argument

  • AVEDEV - all arguments

  • AVERAGE - all arguments

  • AVERAGEA - all arguments

  • AVERAGEIF - all arguments except for criteria

  • AVERAGEIFS - all arguments except for criteria1, criteria2, and so on.

  • BESSELI - all arguments

  • BESSELJ - all arguments

  • BESSELK - all arguments

  • BESSELY - all arguments

  • BIN2DEC - all arguments

  • BIN2HEX - all arguments

  • BIN2OCT - all arguments

  • CHOOSE - all arguments except index

  • COLUMN - all arguments

  • COLUMNS - all arguments

  • COMPLEX - all arguments

  • CONVERT - all arguments

  • COUNT - all arguments

  • COUNTA - all arguments

  • COUNTBLANK - all arguments

  • COUNTIF - all arguments except criteria

  • COUNTIFS - all arguments except for criteria1, criteria2, and so on.

  • COUPDAYBS- all arguments

  • COUPDAYS - all arguments

  • COUPDAYSNC - all arguments

  • COUPNCD - all arguments

  • COUPNUM - all arguments

  • COUPPCD - all arguments

  • CUBEMEMBER - member-expression

  • CUBESET - set-expression

  • CUBEVALUE - all except connection

  • CUMIPMT - all arguments

  • CUMPRINC - all arguments

  • DAVERAGE - all arguments

  • DCOUNT - all arguments

  • DCOUNTA - all arguments

  • DEC2BIN - all arguments

  • DEC2HEX - all arguments

  • DEC2OCT - all arguments

  • DELTA - all arguments

  • DEVSQ - all arguments

  • DGET - all arguments

  • DISC - all arguments

  • DMAX - all arguments

  • DMIN - all arguments

  • DOLLARDE - all arguments

  • DOLLARFR - all arguments

  • DPRODUCT - all arguments

  • DSTDEV - all arguments

  • DSTDEVP - all arguments

  • DSUM - all arguments

  • DURATION - all arguments

  • DVAR - all arguments

  • DVARP - all arguments

  • EDATE - all arguments

  • EFFECT - all arguments

  • EOMONTH - all arguments

  • ERF - all arguments

  • ERFC - all arguments

  • FACTDOUBLE - all arguments

  • FREQUENCY - all arguments

  • FVSCHEDULE - schedule argument

  • GCD - all arguments

  • GEOMEAN - all arguments

  • GESTEP - all arguments

  • GETPIVOTDATA - data-field, pivot-table, field-1, and item-1 arguments

  • GROWTH - all arguments

  • HARMEAN - all arguments

  • HEX2BIN - all arguments

  • HEX2DEC - all arguments

  • HEX2OCT - all arguments

  • HLOOKUP - all arguments

  • IFERROR - value-if-error argument

  • IMABS - all arguments

  • IMAGINARY - all arguments

  • IMARGUMENT - all arguments

  • IMCONJUGATE - all arguments

  • IMCOS - all arguments

  • IMDIV - all arguments

  • IMEXP - all arguments

  • IMLN - all arguments

  • IMLOG10 - all arguments

  • IMLOG2 - all arguments

  • IMPOWER - all arguments

  • IMPRODUCT - all arguments

  • IMREAL - all arguments

  • IMSIN - all arguments

  • IMSQRT - all arguments

  • IMSUB - all arguments

  • IMSUM - all arguments

  • INDEX - array or reference argument

  • INTRATE - all arguments

  • IRR - values argument

  • ISEVEN - all arguments

  • ISODD - all arguments

  • ISREF - all arguments

  • KURT - all arguments

  • LARGE - array argument

  • LCM - all arguments

  • LINEST - known-xs and known-ys arguments

  • LOGEST - known-xs and known-ys arguments

  • LOOKUP - all arguments except lookup_value

  • MATCH - lookup_array argument

  • MAX - all arguments

  • MAXA - all arguments

  • MDURATION - all arguments

  • MEDIAN - all arguments

  • MIN - all arguments

  • MINA - all arguments

  • MIRR - values argument

  • MROUND - all arguments

  • MULTINOMIAL - all arguments

  • N - all arguments

  • NETWORKDAYS - holidays argument

  • NOMINAL - all arguments

  • NPV - all arguments except rate

  • OCT2BIN - all arguments

  • OCT2DEC - all arguments

  • OCT2HEX - all arguments

  • ODDFPRICE - all arguments

  • ODDFYIELD - all arguments

  • ODDLPRICE - all arguments

  • ODDLYIELD - all arguments

  • OFFSET - reference argument

  • OR - all arguments

  • PERCENTILE - array argument

  • PERCENTRANK - array argument

  • PHONETIC - all arguments

  • PRICE - all arguments

  • PRICEDISC - all arguments

  • PRICEMAT - all arguments

  • PRODUCT - all arguments

  • QUARTILE – array argument

  • QUOTIENT - all arguments

  • RANDBETWEEN - all arguments

  • RANK - ref argument

  • RECEIVED - all arguments

  • ROW – reference argument

  • ROWS – array argument

  • SERIESSUM - all arguments

  • SKEW - all arguments

  • SMALL - array argument

  • SQRTPI - all arguments

  • STDEV - all arguments

  • STDEVA - all arguments

  • STDEVP - all arguments

  • STDEVPA - all arguments

  • SUBTOTAL - all arguments except function_num

  • SUM - all arguments

  • SUMIF - all arguments except criteria

  • SUMIFS - all arguments except criteria1, criteria2, and so on.

  • SUMSQ - all arguments

  • T - all arguments

  • TBILLEQ - all arguments

  • TBILLPRICE - all arguments

  • TBILLYIELD - all arguments

  • TREND – all arguments except const-flag

  • TRIMMEAN – array argument

  • VAR – all arguments

  • VARA – all arguments

  • VARP – all arguments

  • VARPA – all arguments

  • VLOOKUP – table-array argument

  • WEEKNUM - all arguments

  • WORKDAY – holidays argument

  • XIRR – all arguments except guess

  • XNPV – all arguments except rate

  • YEARFRAC - all arguments

  • YIELD - all arguments

  • YIELDDISC - all arguments

  • YIELDMAT - all arguments

  • ZTEST – array argument

g.   The standard does not specify what happens when implicit intersection cannot be performed.

Office returns #VALUE! when implicit intersection cannot be performed.