TX Text Control provides the TableCell.Formula property to define formulas for table cells and to display the result of such calculations as cell text. The syntax of those formulas correlates mostly with the formula specification in SpreadsheetML (Reference: Office Open XML File Formats - ISO/IEC 29500 - 1, Chapter: 18.17.2 Syntax) which is also used by most spreadsheet applications such as Microsoft Excel.
A formula can consist of four elements: functions, cell references, constants and operators. The first three represent specific values inside the formula. Operators concatenate these elements and perform the corresponding calculation/operation.
The following example shows a sample formula containing a function, a cell reference, a constant and two operators:
1. Constant: 2 is a number constant.
2. Operators: '^' is the caret operator. It exponentiates the value of table cell R[-2]C[-1] with 2. The operator '*' multiplies the result of this calculation with Pi.
3. Function: PI() is a function that returns the value of Pi: 3.14159265.
4. Cell Reference: R[-2]C[-1] returns the value of the corresponding table cell (2 rows above and 1 column to the left from the formula's table cell).
A constant is a non-varying value that can be part of a formula. TX Text Control supports five different types of constants: Numerical, logical, string, error and array constants.
Numerical constant:
A numerical constant can be an integer (e.g. 4), a decimal number (e.g. 3.937) or a number in engineering notation (e.g. 5e4). Furthermore, a numerical constant can be combined with a prefixed minus (e.g. -7.6) sign.
| Formula | Result |
|---|---|
| 9+7 | 16 |
| 4*5.5 | 22 |
| 4*3e5 | 1200000 |
| -9+7 | -2 |
Logical constant:
Two logical constants are provided: TRUE and FALSE.
| Formula | Result |
|---|---|
| 15>7=TRUE | TRUE |
| 15>7=FALSE | FALSE |
String constant:
A string constant is a concatenation of characters surrounded by double quotes.
| Formula | Result |
|---|---|
| "TX"&"Text"&"Control" | TXText |
| "Two"="One" | FALSE |
| "FALSE"="FALSE" | TRUE |
Array constant:
An array constant represent a matrix of numerical, logical or/and string constants. The constants of each row in this array are separated by commas. Rows by semicolons. The array constant itself is surrounded by braces.
| Formula | Result |
|---|---|
| SUM(10,{1,2,3;4,5}) | 25 |
An operator applies a specific operation to adjacent formula elements. There a three different groups of operators TX Text Control supports: arithmetic, comparison and text concatenation operators. Arithmetic operators perform mathematical operations where the result is a numerical value. While applying comparison operators results in a logical value, text concatenations are joining strings.
| Arithmetic Operator | Example |
|---|---|
| + (Addition) | The formula 8+6 returns 14. |
| - (Substraction) | The formula 14-6 returns 8. |
| * (Multiplication) | The formula 4*3 returns 12. |
| / (Division) | The formula 17/2 returns 8.5. |
| % (Percent) | The formula 290% returns 2.9. |
| ^ (Exponentiation) | The formula 3^2 returns 9. |
| Comparison Operator | Example |
|---|---|
| = (Equal to) | The formula 2=2 returns TRUE. |
| > (Greater Than) | The formula 4>6 returns FALSE. |
| < (Less Than) | The formula 4<6 returns TRUE. |
| >= (Greater than or equal to) | The formula 6>=4 returns TRUE. |
| <= (Less than or equal to) | The formula 26<=4 returns FALSE. |
| <> (Not equal to) | The formula 2<>2 returns FALSE. |
| Text Concatenation Operator | Example |
|---|---|
| & (Concatenation) | The formula "TX"&"Spell" returns TXSpell. |
TX Text Control provides predefined functions that perform specific calculations. The result of such calculations is always a number, logical value, string or an array of such values. The structure of a function begins with an upper case unique name followed by an opening parenthesis, all needed arguments separated by commas and ends with a closing parenthesis.
Valid arguments are numerical, logical, string and array values. These values can be directly set as constants, by the result of other function calculations or by a reference to another table cell.
| Function | Example |
|---|---|
| ABS(x) (Computes the absolute value of x) | ABS(-7.765) results in 7.765. |
| PI() (Returns the value Pi) | PI() results in 3.141592654. |
| POWER(x,y) (Computes x raised to the power y.) | POWER(3,2.5) results in 15.5884573. |
| SUM(argument-list) (Returns the value 6) | SUM(1,2,3) results in 6. |
| SUM(argument-list) (Returns the value 6) | SUM({1;2;3}) results in 6. |
| SUM(argument-list) (Returns the value 6) | SUM(A1:A3) results in 6 if the table cells A1, A2 and A3 provide the values 1, 2 and 3. |
Lists with all supported formula functions can be found in the Supported Formula Functions section.
In some cases, it is necessary to use values from other table cells as an element of a formula or an argument of a function. Therefore, two kinds of cell reference types are provided that can be chosen by the TextControl.FormulaReferenceStyle property to determine how references to table cells are specified: The R1C1 style and the A1 style.
R1C1 Cell Reference Style
R1C1 cell references are displayed using numerical row ('R' + number) and column ('C' + number) values. These values can be relative and absolute.
TX Text Control recommends to use the relative R1C1 style for cell reference operations. The notation of a relative R1C1 cell reference includes square brackets around the numbers. These numbers refer to cell positions relative to the table cell where the formula is defined.
The following example illustrates relative R1C1 cell references in a formula that is defined at the fourth row (R4) of the third column (C3):
| Relative R1C1 Style Example | Description |
|---|---|
| R[-3]C[-1] | Reference to table cell at first row (R1) and second column (C2) with value 3. |
| R[1]C[-2] | Reference to table cell at fifth row (R5) and first column (C1) with value 14. |
| R[0]C[1] | Reference to table cell at the same row (R4) and fourth column (C4) with value -5. |
| R[-2]C[1] | Reference to table cell at second row (R2) and fourth column (C4) with value 5. |
An absolute R1C1 cell reference does not include square brackets around numbers. These values always refer to an absolute cell position, no matter in which table cell the formula is defined (e.g. R5C4 represents always the cell at the fifth row in the fourth column). This type of R1C1 references is supported, but should not be used, if the table is edited or the corresponding scope is pasted as a new table. In these cases, an absolute R1C1 cell reference returns invalid or incorrect values.
A1 Cell Reference Style
To refer to a specific table cell, the A1 cell reference style uses upper case letters (A-Z, AA-AZ, BA-BZ...) to define the cell's column (first column is A, second B etc.) followed by integers to specify the row (e.g. B3 refers to the table cell at the second column in the third row). This notation describes a relative cell reference and is the equivalent to the relative R1C1 cell reference style.
To apply absolute A1 cell references, the notation has to be extended with '$' signs to the left of the corresponding column or/and row to mark them as absolute. In this case, the cell reference does not depend on the table cell location where the formula is defined.
Cell Reference Ranges
Some functions accept argument lists where an unspecified number of values can be committed. If such values are stored in table cells, it could be very inconvenient to set these references one by one to the function. For those cases it is possible to apply cell reference ranges to define a matrix of table cells. Range definition is composed of two cell references, separated by a colon, where one reference represents the upper left and the other one the lower right of the scope to define.
The following example applies sample cell reference ranges to the SUM formula:
| Formula with Cell Reference Range | Description |
|---|---|
| 1. SUM(R[-1]C[-2]:R[0]C[-1]) | Sums the values in the scope between R2C1 and R3C2 and returns 16. |
| 2. SUM(R[-2]C[0]:R[-1]C[0]) | Sums the values in the scope between R1C3 and R2C3 and returns 14. |
| 3. SUM(R[-2]C[2]:R[1]C[2]) | Sums the values in the scope between R1C5 and R4C5 and returns 20. |
| 4. SUM(R[2]C[-1]:R[2]C[1]) | Sums the values in the scope between R5C2 and R5C4 and returns 19. |
LEFT, ABOVE, RIGHT and BELOW
Another option to specify cell reference ranges is to use the LEFT, ABOVE, RIGHT or BELOW flag. Each of these flags refer to the corresponding cell references next to the table cell where the formula is defined (e.g. SUM(ABOVE) sums the values of all cell references above the formula's table cell).
The following example applies the LEFT, ABOVE, RIGHT and BELOW flags to the SUM formula:
| Formula with Cell Reference Range | Description |
|---|---|
| 1. SUM(LEFT) | Sums the values left to the formula's table cell and returns 5. |
| 2. SUM(ABOVE) | Sums the values above the formula's table cell and returns 14. |
| 3. SUM(RIGHT) | Sums the values right to the formula's table cell and returns 8. |
| 4. SUM(BELOW) | Sums the values below the formula's table cell and returns 15. |