The syntax of formulas is inadequately described.

Specify the syntax using ISO BNF or similar notation that allows a parser or validator to be constructed.

S3.17.2

te

Proposed Disposition of DIS 29500 Comment AU-0011 (Modified: 2007-12-17) The grammar for SpreadsheetML formulas has been completely rewritten using the notation defined by ISO/IEC 14977:1996, “Syntactic metalanguage — Extended BNF”. The following series of changes (which do not incorporate changes resulting from responses to other comments covering these subclauses) will be made to the designated subclauses in Part 4, §3.17: Part 4, §3.17.2, page 2,508, lines 3­17: 3.17.2 Syntax 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 one-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. The syntax rules below are modified by the context-sensitive rules stated in other subclauses of §3.17.*, as indicated by an EBNF comment of the form “(* see semantic rules at 3.17… *)”. [Note: Thus, in order to produce an automated verifier, the context-sensitive rules in subclauses 3.17.* must all be considered. The context-free syntax rules shown in this subclause provide an overview to assist comprehension by the reader, but do not represent the entirety of the context-sensitive rules. end note] The general syntax of a formula is as follows: formula: expression expression: ( expression ) constant prefix-operator expression expression infix-operator expression expression postfix-operator cell-reference function-call name formula= expression ; expression= "(", expression, ")" | constant | prefix-operator, expression | expression, infix-operator, expression | expression, postfix-operator | cell-reference | function-call | name ; where expression is an arbitrarily complex expression involving constants (§3.17.2.1), operators (§ 0 3.17.2.2 ), cell references (§3.17.2.3), calls to functions (§3.17.2.4), and names (§3.17.2.5). Part 4, §3.17.2.1, page 2,509, lines 21 through page 2,510, line 34: 3.17.2.1 Constants A constant is a predefined value that is not calculated, and, therefore, does not change. A constant has the following form: constant: error-constant logical-constant numerical-constant string-constant array-constant error-constant: #DIV/0! | #N/A | #NAME? | #NULL! | #NUM! | #REF! | #VALUE! logical-constant: FALSE TRUE numerical-constant: whole-number-part [ . ] [ exponent-part ] . fractional-part [ exponent-part ] whole-number-part . fractional-part [ exponent-part ] whole-number-part: digit-sequence fractional-part: digit-sequence exponent-part: e [ sign ] digit-sequence E [ sign ] digit-sequence sign: + - digit-sequence: a series of one or more decimal digits string-constant: " [ string-chars ] " string-chars string-char string-chars string-char string-char "" any character except " constant= error-constant | logical-constant | numerical-constant | string-constant | array-constant ; error-constant= "#DIV/0! " | "#N/A" | "#NAME? " | "#NULL! " | "#NUM! " | "#REF! " | "#VALUE! " ; logical-constant= "FALSE" | "TRUE" ; numerical-constant= whole-number-part, [full-stop], [exponent-part] | full-stop, fractional-part, [exponent-part] | whole-number-part, full-stop, fractional-part [exponent-part] ; full-stop= "." ; (* also known as “period” *) whole-number-part= digit-sequence ; fractional-part= digit-sequence ; exponent-part= "e" [ sign ] digit-sequence | "E" [ sign ] digit-sequence ; sign= "+" | "-" ; digit-sequence= decimal-digit, {decimal-digit} ; decimal-digit= "0" | "1" | "2″ | "3" | "4" | "5″ | "6" | "7" | "8″ | "9" ; string-constant= double-quote, [string-chars], double-quote ; double-quote= ‘ " ‘ ; (* one double-quote character*) string-chars= string-char, {string-char} ; string-char= ‘ "" ‘ | (* consecutive double-quotes, with no space between them *) character - double-quote ; (* any character except double-quote *) character= as defined by the production Char in the XML 1.0 specification, §2.2. To include a double-quote character (") in string-chars, precede it with another double-quote character. [Example: "ab""cd" contains the characters ab"cd, and """abcd""" contains the characters "abcd". end example] An array constant is a list of one or more constants organized in one or two dimensions, and delimited by braces. An array constant has the following form: array-constant: { constant-list-rows } constant-list-rows: constant-list-row constant-list-rows ; constant-list-row constant-list-row: constant constant-list-row , constant array-constant= "{", constant-list-rows, "}" ; constant-list-rows= constant-list-row, { semicolon, constant-list-row } ; semicolon= ";" ; constant-list-row= constant, { comma, constant } ; An array-constant shall not contain Part 4, §3.17.2.2 page 2,511, lines 15­24: 3.17.2.2 Operators An operator is a symbol that specifies the type of operation to perform on one or more operands. There are arithmetic, comparison, text, and reference operators. infix-operator: : | , | space | ^ | * | / | + | - | & | = | <> | < | <= | > | >= postfix-operator: % prefix-operator: - operator= ":" | comma | space | "^" | "*" | "/" | "+" | "-" | "&" | "=" | "<>" | "<" | "<=" | ">" | ">=" | “%” ; infix-operator= ":" | comma | space | "^" | "*" | "/" | "+" | "-" | "&" | "=" | "<>" | "<" | "<=" | ">" | ">=" ; postfix-operator= "%" ; prefix-operator= "-" ; The operators permitted in expression are: Part 4, §3.17.2.3, page 2,513, lines 15­27: 3.17.2.3 Cell References Each set of horizontal cells in a worksheet is a row, and each set of vertical cells is a column. A cell’s row and column combination designates the location of that cell. [Guidance An implementation is encouraged to not unnecessarily limit the number of rows and columns in a worksheet. end guidance] A cell reference designates one or more cells on the same worksheet. Using references, one can: Use data contained in different parts of the same worksheet in a single formula. Use the value from a single cell in several formulas. Refer to cells on other sheets in the same workbook, and even to other workbooks. (References to cells in other workbooks are called links.) A cell reference has the following form: cell-reference: name [ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] A1-reference [ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] A1-reference : A1-reference [ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] R1C1-reference [ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] R1C1-reference : R1C1-reference workbook-name: book-name-start-character [ book-name-characters ] book-name-start-character: any character except ‘ , * , [ , ] , : , and ? book-name-characters: book-name-characters book-name-character book-name-character: any character except * , [ , ] , : , and ? sheet-name: sheet-name-start-character [ sheet-name-characters ] sheet-name-start-character: any character except ‘ , * , [ , ] , \ , : , / , and ? sheet-name-characters: sheet-name-characters sheet-name-character sheet-name-character: any character except * , [ , ] , \ , : , / , and ? cell-reference= name | [work-sheet-prefix] A1-reference | [work-sheet-prefix] A1-reference, ":", A1-reference | [work-sheet-prefix] R1C1-reference | [work-sheet-prefix] R1C1-reference, ":", R1C1-reference ; work-sheet-prefix= work-sheet-prefix-special | sheet-name, "!" | sheet-name, ":", sheet-name, "!" | "[", workbook-name, "]", sheet-name, ":", sheet-name, "!" ; work-sheet-prefix-special= apostrophe, sheet-name-special, apostrophe, "!" | apostrophe, sheet-name-special, ":", sheet-name-special, apostrophe, "!" | apostrophe, "[", workbook-name-special, "]", sheet-name-special, apostrophe, "!" ; apostrophe, "[", workbook-name-special, "]", sheet-name-special, ":", sheet-name-special, apostrophe, "!" ; workbook-name= book-name-characters ; book-name-characters= book-name-character, {book-name-character} ; book-name-character= character ­ (operator | apostrophe | "[" | "]" | "?") ; (* any character except operator or ‘, [, ], or ? *) apostrophe= " ‘ " ; (* one apostrophe character *) sheet-name= sheet-name-characters ; sheet-name-character= character ­ (operator | apostrophe | "[" | "]" | "\" | "?") ; (* any character except operator or ‘, [, ], \, or ? *) sheet-name-characters= sheet-name-character, {sheet-name-character} ; workbook-name-special= book-name-start-character-special, [ book-name-characters-special ]; book-name-start-character-special= character ­ (apostrophe | "*" | "[" | "]" | ":" | "?") ; (* any character, including operator, except ‘, *, [, ], :, or ? *) book-name-characters-special= book-name-character-special, {book-name-character-special} ; book-name-character-special= apostrophe, apostrophe | character ­ (apostrophe | "*" | "[" | "]" | ":" | "?") ; (* any character, including operator, except ‘, *, [, ], :, or ? *) sheet-name-special= sheet-name-start-character-special, [ [sheet-name-characters-special], sheet-name-end-character-special] ; sheet-name-start-character-special= character ­ (apostrophe | "*" | "[" | "]" | "\" | ":" | "/" | "?") ; (* any character, including operator, except ‘, *, [, ], \, :, /, or ? *) sheet-name-end-character-special= sheet-name-start-character-special ; sheet-name-characters-special= sheet-name-character-special, {sheet-name-character-special} ; sheet-name-character-special= apostrophe, apostrophe | character ­ (apostrophe | "*" | "[" | "]" | "\" | ":" | "/" | "?") ; (* any character, including operator, except ` *, [, ], \, :, /, or ? *) A relative cell reference is based on the relative position of the cell that contains the formula and the cell to which the reference refers. If the position of the cell that contains the formula changes, the reference is changed along with it. Part 4, §3.17.2.3.1, page 2,515, lines 9­32: 3.17.2.3.1 A1-Style Cell References A cell reference using the A1 reference style has the following form: A1-reference: A1-column A1-row A1-column A1-row A1-column: A1-relative-column A1-absolute-column A1-relative-column: A Latin letter A­Z The Latin letters AA­AZ, BA­BZ, …, ZA­ZZ, AAA-AAZ, ABA­ABZ, …, and so on A1-absolute-column: $ A1-relative-column A1-row: A1-relative-row A1-absolute-row A1-relative-row: A positive decimal number A1-absolute-row: $ relative-row A1-reference= A1-column, ":", A1-column | A1-row, ":", A1-row | A1-column, A1-row ; A1-column= A1-relative-column | A1-absolute-column ; A1-relative-column= letter, {letter} ; (* see semantic rules at 3.17.2.3.1 *) letter= "a"|"b"|"c"|"d"|"e"|"f"|"g"|"h"|"i"|"j"|"k"|"l"|"m"| "n"|"o"|"p"|"q"|"r"|"s"|"t"|"u"|"v"|"w"|"x"|"y"|"z"| "A"|"B"|"C"|"D"|"E"|"F"|"G"|"H"|"I"|"J"|"K"|"L"|"M"| "N"|"O"|"P"|"Q"|"R"|"S"|"T"|"U"|"V"|"W"|"X"|"Y"|"Z" ; A1-absolute-column= "$", A1-relative-column ; A1-row= A1-relative-row | A1-absolute-row ; A1-relative-row= digit-sequence ; (* must be non-zero; see 3.17.2.3.1 *) A1-absolute-row= “$”, A1-relative-row ; In this style, each row has a numeric heading numbered sequentially from the top down, starting at 1. Each column has an alphabetic heading named sequentially from left-to-right, A­Z, then AA­AZ, BA­BZ, …, ZA­ZZ, AAA­AAZ, ABA­ABZ, and so on. Column letters are not case-sensitive. Part 4, §3.17.2.3.2, page 2,516, lines 4­31: 3.17.2.3.2 R1C1-Style Cell Reference A cell reference using the R1C1 reference style has the following form: R1C1-reference: R1C1-row R1C1-column R1C1-row R1C1-column R1C1-row: R1C1-relative-row R1C1-absolute-row R1C1-relative-row: R [ R1C1-relative-number ] R1C1-absolute-row: R R R1C1-absolute-number R1C1-column: R1C1-relative-column R1C1-absolute-column R1C1-relative-column: C [ R1C1-relative-number ] R1C1-absolute-column: C C R1C1-absolute-number R1C1-relative-number: An optionally signed decimal number R1C1-absolute-number: A positive decimal number R1C1-reference= R1C1-row-only | R1C1-column-only | R1C1-row, R1C1-column ; R1C1-row-only= "R", R1C1-absolute-number, | "R[", R1C1-relative-number, "]" ; R1C1-row= R1C1-relative-row | R1C1-absolute-row ; R1C1-relative-row= "R[", R1C1-relative-number, "]" ; R1C1-absolute-row= "R" | "R", R1C1-absolute-number ; R1C1-column-only= "C", R1C1-abolute-number | "C[", R1C1-relative-number, "]" ; R1C1-column= R1C1-relative-column | R1C1-absolute-column ; R1C1-relative-column= "C[", R1C1-relative-number, "]" ; R1C1-absolute-column= "C" | "C", R1C1-absolute-number ; R1C1-relative-number= ["-"], digit-sequence ; R1C1-absolute-number= digit-sequence ; (* must be non-zero; see 3.17.2.3.2 *) In this style, each row has a numeric heading numbered sequentially from the top down, starting at 1. Each column has a numeric heading numbered sequentially from left-to-right, starting at 1. Part 4, §3.17.2.4, page 2,517, lines 2­27: 3.17.2.4 Functions A function is a named formula that takes zero or more arguments, performs an operation, and, optionally, returns a result. A function call has the following form: function-call: function-name ( [ argument-list ] ) function-name: predefined-function-name user-defined-function-name predefined-function-name: ABS | ACOS | ACOSH | any of the other functions defined in §3.17.7 user-defined-function-name: letter [ user-defined-name-characters ] user-defined-name-characters: user-defined-name-characters user-defined-name-character user-defined-name-character: letter any decimal digit 0­9 . letter: any Latin letter A­Z, a­z argument-list: argument argument-list , argument argument: expression function-call= function-name, "(", [argument-list], ")" ; function-name= predefined-function-name | user-defined-function-name ; predefined-function-name= "ABS" | "ACOS" | "ACOSH" | ( any of the other functions defined in §3.17.7 ) ; user-defined-function-name= letter, [ user-defined-name-characters ] ; user-defined-name-characters= user-defined-name-character, {user-defined-name-character} ; user-defined-name-character= letter | decimal-digit | full-stop ; argument-list= argument, { comma, argument } ; comma= "," ; argument= expression ; predefined-function-names and user-defined-function-names are not case-sensitive. Part 4, §3.17.2.5, page 2,518, line 19 through page 2,519, line 1: 3.17.2.5 Names A name is an alias for a constant, a cell reference, or a formula. [Note: A name in a formula can make it easier to understand the purpose of that formula. For example, the formula SUM(FirstQuarterSales) is easier to identify than SUM(C20:C30). end note] Here is the syntax for name: name: [ workbook-name ! ] name-start-character [ name-characters ] name-start-character: letter _ \ name-characters: name-characters name-character name-character: letter any decimal digit 0­9 _ . name= [ workbook-name, "!" ], name-start-character, [ name-characters ] ; name-start-character= letter | underscore | backslash ; underscore= "_" ; backslash= "\" ; name-characters= name-character, {name-character} ; name-character= letter | decimal-digit | underscore | full-stop ; names are not case-sensitive. Part 4, §3.17.7, page 2,530, lines 6­9: 3.17.7 Predefined Function Definitions Each of the subclauses below this subclause describes a separate function, and each description contains a section marked Syntax. That section contains pieces of the function grammar as they pertain to that specific function. These pieces are presented in a slightly simpler form to aid in the understanding of the description. In those sections, the left-square-bracket and right-square-bracket designate the start and end of an option, as used in ISO/IEC 14977. However, the function-name, the open-parenthesis and the close-parenthesis designate actual literal text, as does each comma. [Note: Therefore, in a strict presentation according to ISO/IEC 14977, each would appear with double-quotes surrounding each instance. end note] The Syntax entry section for each function defined in this sub clause corresponds to a call to that function. Except for argument-list, T t he names in any Syntax entry section typeset as in number and string-1, are parameter names for that function, and are local to that function definition’s description. argument-list is the name of a production in the grammar, and, as defined in §3.17.2.4, permits a comma-separated list of arguments. Similar Comments: CA-0043 , FR-0436 , GB-0361 , US-0245