Formulas

Formulas in Spreadsheet for WPF conform to the standard formula format established by the modern spreadsheet engines. The formula is expressed as a string, starting with an equal sign (=), followed by an expression. The expression can contain literals, mathematical operators, cell references, function calls, and so on.

Literals

A literal is a constant value written directly in the expression. For example, the number 5 or the text "Hello World" in the example below are both literals.

Formula

="Hello World"
=5

Both of the above formulas will evaluate to their respective literals. That is, the first formula will evaluate to the text "Hello World" and the second formula will evaluate to the number 5.

The literals in Spreadsheet for WPF can be numbers, texts and error values. The text literals should be enclosed in quotes. The error values are #NAME?, #REF!, #ERROR!, #VALUE!, #NUM!, #N/A, #DIV/0!, and #NULL!.

Cell and Cell Range References

Formula expressions can contain references to cells or cell ranges. The cell references are written as a combination of the column letter(s) and row number of the cell. For example, A1 is a reference to the top-left cell of the current worksheet. Alternatively, cell references can be written in the RMCN format, where M and N are the numbers of the cell row and column respectively. For example, the reference to the top-left cell of the current worksheet in this format will look like this: R1C1. The cell reference can include the name of a worksheet when the referenced cell is not part of the worksheet containing the formula. The worksheet name is written before the cell name, separated with an exclamation mark, for example, Sheet1!A1. If the worksheet name contains characters other than letters and numbers or the worksheet name starts with a character other than a letter, the worksheet name must be enclosed in single quotes, for example, 'My sheet'!A1.

The cell range references are expressed as a pair of cell names separated with a colon (:), for example, A1:C2 references the cell range with the cell A1 at the top-left and the cell C2 at the bottom-right. Cell ranges can also include the name of a worksheet, for example, Sheet1!A1:C2.

The cell and cell range references are evaluated differently depending on the context in which they are encountered. Generally, the cell references are dereferenced and the value of the cell is used in the expression. For example, the following formula calculates the sum of two cells:

Formula

=A1+B1

In some cases the referenced cell or cell range is not dereferenced. For example, the following formula returns the number of the cell row. The actual value of the cell has no effect on the result of the evaluation.

Formula

=ROW(A1)

Operators

Formula expression can contain mathematical or comparison operators. The operators are similar to functions - they take one or more operands and return a value. The syntax of the operators depend on their type, but is usually in the form <operand1><operator><operand2>, where <operand1> is an expression representing the first operand, <operand2> is an expression representing the second operand, and <operator> is the symbol of the operator. For example, the addition operator (+) can be used to sum two numeric values (A1+5). The preceding syntax applies to operators that accept two operands (binary operators). Operators accepting a single operand (unary operators) are written in the form <operator><operand>. For example, the unary minus operator (–) can be used like this:

Formula

=-PI()

The mathematical operators supported by Spreadsheet for WPF are + (addition), – (subtraction), – (unary minus), / (division), * (multiplication), & (concatenation), and ^ (exponent). The supported comparison operators are < (less than), <= (less than or equal), = (equal), <> (not equal), >= (greater than or equal), and > (greater than).

The operators encountered in a formula are evaluated according to their precedence. Operators with higher priority are evaluated before operators with lower priority. For example, the following formula will evaluate to 14, because the multiplication will be evaluated first:

Formula

=2+3*4

Operator precedence can be changed by using parentheses. Changing the order of the operators in the above example, so that the addition is evaluated first, can be done like this:

Formula

=(2+3)*4

The following table lists the operators in order of precedence:

Unary	– (unary minus)
Arithmetic - Multiplicative	*, /
Arithmetic - Additive	+. –
Comparison	<, <=, >=, >
Equality	=, <>

Functions

The formula expressions can contain calls to one or more of the built-in functions or to a custom function. For a complete list of the functions available in Spreadsheet for WPF formulas, check the Function Reference topic. The function call has the following syntax: <name>(<param1>,..,<paramN>), where <name> is the name of the function and <param1> through <paramN> are the function parameters. The following example illustrates a formula containing a function call:

Formula

=SUM(A1:D5)

Some function do not have parameters. In this case the parentheses are still required:

Formula

=NA()

Function calls can be nested. In this case the result of the nested function is used as a parameter of the nesting function. For example:

Formula

=ABS(COS(PI()))

Custom functions

Spreadsheet for WPF provides the ability to register custom functions with the calculation engine. This can be done by implementing the IExpressionEvaluator interface and assigning an instance of the class to the ExtendedEvaluator property of the Workbook class. For illustration how to do this, check the CustomFunctions sample.

Arrays and Matrices

Arrays and matrices can be written directly into a formula, using the following syntax:

Syntax

{E11;..;E1N|E21;..;E2N|..|EM1;..;EMN}

The first sequence of values E11 to E1N represents the top row of the matrix, the second sequence (E21..E2N) represents the second row, and so on. For example, the following formula demonstrates a matrix with 3 columns and 2 rows:

Formula

={1;2;3|4;5;6}