Excel Syntax and Grammer Information

COMP MATH OPRE STAT

This page is no longer being updated as it has been relocated to http://www.mcs.vuw.ac.nz/~elvis/db/FishBrainWiki?Excel.

Definition

A description of what is possible in spreadsheets will go here.
Given this scope/latitude, how do people use the space?

Example Formulas

=1
=1+1
=A1
=$B$2
=SUM(B5:B15)
=SUM(B5:B15,D5:D15)
=SUM(B5:B15 A7:D7)
=SUM(sheet1!$A$1:$B$2)
=[data.xls]sheet1!$A$1
=SUM((A:A 1:1))
=SUM((A:A,1:1))
=SUM((A:A A1:B1))
- The Function SUM, with a Intersection between a Column and a Range as the argument.
=SUM((D9:D11,(E9:E11,F9:F11)))
- The Function SUM, with a Union between a Range and a smaller Union as the argument.
=IF(P5=1.0,"NA",IF(P5=2.0,"A",IF(P5=3.0,"B",IF(P5=4.0,"C",IF(P5=5.0,"D",IF(P5=6.0,"E",IF(P5=7.0,"F",IF(P5=8.0,"G"))))))))
- From an actual spreadsheet.
={SUM(B2:D2*B3:D3)}
- This is an array formula
  (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { }, and are entered by pressing CTRL+SHIFT+ENTER.).

Grammer

The work on the Grammer here is mainly targeted at the structure of the Excel sheets (cells references etc).
To parse a formula string into a List of tokens in RPN order something like the following would be used. (Inspired by "Lets Build a Compiler", by Jack Crenshaw and extracted from source code of POI>HSSF.)
BNF [BNF] for the formula expression is :
<expression> ::= <term> [<addop> <term>]*
<term> ::= <factor> [ <mulop> <factor> ]*
<factor> ::= <number> | (<expression>) | <cellRef> | <function>
<function> ::= <functionName> ([expression [, expression]*])

Formula

Formula ::= "=" <a combination of references, operators, functions, numbers and other primitives.>
Formulas can refer to cells or ranges of cells, or to names or labels that represent cells or ranges

ReferencePrefix ::= (<Workbook> | <Sheet> | &epsilon ) <Reference>
Reference ::= <Cell> | <AreaReference>
AreaReference ::= <Range> | <Vector> | <Intersection> | <Union>
Workbook ::= "["workbook_path/filename.xls"]" <Sheet>
Sheet ::= <Sheetname>"!"
Sheetname ::= <The name of each worksheet.> e.g. Sheet1, Sheet2, Sheet3, ...
Cell ::= ["$"]<Column>["$"]<Row>
The $ symbol denotes an absolute reference to that dimension/axis.
Row ::= x | x ε Int, 0 < x ≤ 65536
Column ::= [A-Z] | [A-H][A-Z] | I[A-Z]
Here [A-Z] is interupted like a regular expression i.e. Any character between 'A' and 'Z'. Columns use letters (A through IV, for a total of 256 columns) for labels.

AreaReference operators

Range ::= Cell:Cell
Vector ::= <ColumnRange> | <RowRange>
It is possiblie to refer to a single column(B:B) or row(5:5) range (known as a vector) in Excel .
ColumnRange ::= Column:Column
The first column reference should be ≤ to the second column reference.
RowRange ::= Row:Row
The first row reference should be ≤ to the second row reference.
Intersection ::= AreaReference AreaReference
Two ranges seperated by a single space.
Union ::= AreaReference,AreaReference
Reference operators combine ranges of cells for calculations.

Operators

Operator ::= ComparrisonOperator | ArithmaticOperator | ConcatinationOperator | OtherOperator
ComparrisonOperator ::= "<=" | ">=" | "<" | ">" | "!=" | "="
ArithmaticOperator ::= "+" | "-" | "*" | "/" | "^"
ConcatinationOperator ::= "&"
OtherOperator ::= "%"

Functions

Markus Clermont's Grammer

Spreadsheet ::= Row, {col, Row}, col
Row ::=         (Formula|Number|string), {;,Row}
Number ::=      ['-'], digit, {digit}, [',', digit, {digit}]
Formula ::=     '=', FormulaExpression
FormulaExpression ::= Expression, {('+'|'-'|'*'|'/'|'^'), Expression}
CellAddress ::= 'R',('$' AbsCord)|RelCord, 'C',('$' AbsCord)|RelCord
RelCord ::=     ['-'],digit,{digit}
AbsCord ::=     digit, {digit}
LogOp ::=       '>' | '<' | '=' | '!=' | '>=' | '<='
LogicalExpression ::= FormulaExpression [LogOp FormulaExpression]
LogArgList ::=  LogicalExpression ';' LogicalExpression
FunctionCall ::= ('IF(',LogicalExpression, ';', FormulaExpression, [';',FormulaExpression],')' |
                 (GroupFkt'(',RArgList,')') |
                 ('NOT(', LogicalExpression, ')') |
                 (Function, '(', ArgList ')') |
                 (LogFunc'(', LogArgList ')') |
                 ('SVerweis(', FormulaExpression, ';', CellAddress, [':', CellAddress], ';', FormulaExpression, ';' FormulaExpression, ')')
GroupFkt ::=     'SUM'|'MAX'|'MIN'|...
Function ::=     ...
LogFunc ::=      'Or'|'And'|...
ArgList ::=      FormulaExpression, {';', FormulaExpression}
RArgList ::=     RArgument, [';' RArgument]
RArgument ::=    RangeReference | FormulaExpression
RangeRefernece ::= CellAddress, ':', CellAddress

Operator Precedence

Based on Excel Tutorial - SC 2000 - Basic Math (local). and Microsoft support.

When you combine several operators into a single formula, Microsoft Excel performs the operations in the following order:

: Range
space Intersection
, Union
- Negation
% Percentage
^ Exponentiation
* or / Multiplication or Division
+ or - Addition or Subtraction
& Text Operator
= < > <= >= <> Comparison Operators

In Excel 97 Help:
operators, evaluation order in formulas > "The order in which Microsoft Excel performs operations in formulas" topic
operators, precedence...

Note: (From http://www.faqs.org/faqs/spreadsheets/faq/)
10.13 Why is =-1^2 positive and =0-1^2 negative?

nonstandard operator precedence -- the `unary minus' has a high precedence, as normal, but the `exponentiation operator' has a higher one, which is NOT normal.

Excel treats -1^2 as (-1)^2, while most languages would as -(1^2). (you can ALWAYS use parentheses to force either interpretation.)

note that Excel formulas and VBA formulas disagree on the order.

Calculation operators in formulas

(Based on [WSFF] and help files from [EXCEL])
Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.

Arithmetic operators perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results.

Arithmetic operator	Meaning	Example
+ (plus sign)	Addition	3+3
– (minus sign)	Subtraction	3–1
	Negation	–1
* (asterisk)	Multiplication	3*3
/ (forward slash)	Division	3/3
% (percent sign)	Percent	20%
^ (caret)	Exponentiation	3^2 (the same as 3*3)

Comparison operators compare two values and then produce the logical value TRUE or FALSE.

Comparison operator	Meaning	Example
= (equal sign)	Equal to	A1=B1
> (greater than sign)	Greater than	A1>B1
> (less than sign)	Less than	A1<B1
>= (greater than or equal to sign)	Greater than or equal to	A1>=B1
>= (less than or equal to sign)	Less than or equal to	A1<=B1
<> (not equal to sign)	Not equal to	A1<>B1

The text operator "&" combines one or more text values to produce a single piece of text.

Text operator Meaning Example

& (ampersand) Connects, or concatenates, two values to produce one continuous text value "North" & "wind" produces "Northwind"

Text operator	Meaning	Example
& (ampersand)	Connects, or concatenates, two values to produce one continuous text value	"North" & "wind" produces "Northwind"

Reference operators combine ranges of cells for calculations.

Reference operator	Meaning	Example
: (colon)	Range operator, which produces one reference to all the cells between two references, including the two references	B5:B15
, (comma)	Union operator, which combines multiple references into one reference	SUM(B5:B15,D5:D15)
(single space)	Intersection operator, which produces one reference to cells common to two references	SUM(B5:B15 A7:D7) In this example, cell B7 is common to both ranges.

"Although the multidensional spreadsheet has attracted a lot of interest since it empowers the end user to analyze business data, this has not replaced traditional analysis by means of a managed query environment." - [COMP442 ODWOT]

Links

Consider using a parser generator [JavaCC] or JLex to automatically create code directly from the definition.
[SMSSE] [WFE] [MRX] [UYCS] [ECS] [FD] [DSGPASL] [WSSF]

Metrics

FishBrain :: SpreadsheetMetrics For a formula:

Length when viewed as string
Number of (distinct) cells referenced
Number of (distinct) ranges referenced
Number of functions used
Number of operators (+, -, *, /) used
Degree of nesting present
Position of formulas and raw data.
Consider branching factor information
Euclidean distance between cells in dependency trees
Coupling between sheets and workbooks.
Cohesion of data within clusters.
Number of non-orphan cells with values.
Number of dependent cells - as a measure of the degree to which it effects the rest of the sheet.
Number of prependent cells - as a measure of the degree to which it is effected by the rest of the sheet.

Help Stuff from XP

You can use Auto Fill Options to choose options for how to fill the selection. For example, you can choose to Fill Formatting Only or Fill Without Formatting.

If a cell contains a formula that breaks one of the rules, a triangle appears in the top-left corner of the cell. You can prevent these indicators from being displayed. formula problem Clicking in these cells will display the trace error icon.

Error detection rules

Formula omits cells in region
The formula may not include a correct reference. If a formula refers to a range of cells, and you add cells to the bottom or right of that range, the references may no longer be correct. The formula does not always automatically update its reference to include the new cells. This rule compares the reference in a formula against adjacent cells. If the adjacent cells contain more numbers (are not blank cells), then the problem is noted.
For example, the formula =SUM(A2:A4) would be noted with this rule, because A6, A7, and A8 are adjacent, and contain data.

Invoice
15,000
9,000
8,000
20,000
5,000
22,500
=SUM(A2:A4)

Errors, Checkers, Auditors

FishBrain::SpreadsheetAuditing
(From http://www.faqs.org/faqs/spreadsheets/faq/)

Auditor tools and books The Excel Auditor [XLAUD]
Spreadsheet Detective [SSD]
Professional's Guide to Robust Spreadsheets http://www.manning.com/Richardson/

Error value

Meaning

#DIV/0!

The formula or function is trying a division by 0. Which is mathematically undetermined. Check for values = 0. One of the references in the formula may be to a blank or empty cell. In this case, change the reference or enter a value other than zero.

#N/A

Refers to a value that is not available, or non existing ( a blank cell for instance)

#NAME?

Uses a name that EXCEL does not regognize.

#NULL!

Specifies an invalid intersection of two areas.

#NUM!

Uses a number incorrectly for instant in the argument of a function. It is also displayed if the numbers are too big or too small to be understood by EXCEL.

#REF!

Refers to a cell that is not valid (non existing cell for instance).

#VALUE!

Uses an incorrect argument or operant.

#####

Produces a result too long to be displayed in the cell. This is not really an error: it indicates the the cell need to be wider or of a different format.

Notes and stuff

Absolute Column, Absolute Row, $A$1, is an absolute cell reference
Relative Column, Absolute Row, A$1, is a mixed cell reference
Absolute Column, Relative Row, $A1, is a mixed cell reference
Relative Column, Relative Row, A1, is a relative cell reference

Pressing the F4 key in Excel, while typing the cell address, will cycle it through the four variations.

Google discussion group: http://groups.google.com/groups?group=comp.apps.spreadsheets

"The shortcut key to cell names is Ctrl + F3." [OOPS]

To make Excel calculate a circular reference, set iteration to on. Use Tools|Options|Calculation|Iteration. Also set the Maximum Iterations number to 50 or more.

Extract from http://groups.google.com
Subject:
 Formula- Grammar
Newsgroup:
 comp.apps.spreadsheets
Harlan Grove (hrlngrv@aol.com)
Subject: Re: Formula- Grammar
Newsgroups: comp.apps.spreadsheets
View this article only
Date: 2001-09-06 11:44:32 PST

Markus CLERMONT  wrote...
>I'm doing currently some research in debugging excel-spreadsheets.
>As we are currently developping a tool for detecting errors in
>spreadsheets, we have to parse Excel-Formulas. Therefore I'd
>need a formula-grammar for excel-formulas.

Meaning a BNF-style grammar? I don't think there is one. For the most part
it's very basic: 5 infix arithmetic operators (+, -, *, /, ^), 6 infix
comparison operators (=, <>, <, <=, >, >=), locale-varying argument
separator and decimal 'point' (usually , or ; and . or ,) taken from
Windows' Regional Settings (that alone would make a BNF grammar rather a
tricky thing to write), numeric constants, string constants, error constants
(all begin with #), function calls as function-name(argument-list), and then
there's the harder stuff.

- range references, including collections, intersections and 3D blocks
- array constants
- whitespace in limited instances
- no more than 7 levels of formula nesting
- no argument list longer than 29 arguments
- anything not interpreted as one of the items above that matches the
regular expression [_A-Za-z][_0-9A-Za-z]* but not 'R' or 'C' would be
interpreted as defined names

Not sure why you need this. Excel won't allow users to enter syntactically
invalid formulas, so you'll never find any cell formulas in Excel workbooks
for which you'd need a grammar to check them. If the tool you're developing
would locate argument type errors in function calls within cell formulas,
that's problematic. In may instances Excel can use numeric and text values
interchangeably, like awk or perl.

So likely all you really need is a means of identifying function calls
within formulas. The syntax isn't materially different from C. Function
names match the regular expression [A-Za-z][_0-9A-Za-z]* (no restrictions,
but several built-in functions), function names are immediately followed by
left parenthesis, then there's the argument list, and finally the closing
right parenthesis. So the regular expression [A-Za-z][_0-9A-Za-z]*[(]
(assuming you're not inside a double-quoted string) matches the beginning of
any function call.

About R1C1 references

Ref. To toggle between A1 and R1C1 style referencing Tools > Options > General tab > R1C1 reference style.
In R1C1 style, Microsoft Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number. For example, the absolute cell reference R1C1 is equivalent to the absolute reference $A$1 in A1 reference style. If the active cell is A1, the relative cell reference R[1]C[1] refers to the cell one row down and one column to the right, or B2. The following are examples of references in R1C1 style.

Reference       Meaning
R[-2]C	        A relative reference to the cell two rows up and in the same column
R[2]C[2]        A relative reference to the cell two rows down and two columns to the right
R2C2	        An absolute reference to the cell in the second row and in the second column
R[-1]	        A relative reference to the entire row above the active cell
R	        An absolute reference to the current row

UML Spreadsheet Strucutre

Internal Format

Invesitgation into Excel Syntax