Export (0) Print
Expand All
Expand Minimize

2.1.851 Part 1 Section 18.17.2, Syntax

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.

Show:
© 2015 Microsoft