# Using Formulas in Table Cells

> Write TableCellFormula formulas with constants, operators, functions such as SUM, and A1 or R1C1 cell references, ranges and the LEFT, ABOVE and RIGHT flags.

- **Product:** TX Text Control ActiveX
- **Section:** Documentation
- **Kind:** Topic
- **Path:** TX Text Control ActiveX > Document Editor > Tables > Using Formulas in Table Cells
- **2792 words**
- **Web URL:** https://docs.textcontrol.com/tx-text-control-activex/document-editor/tables/formulas/
- **LLMs URL:** https://docs.textcontrol.com/tx-text-control-activex/document-editor/tables/formulas/llms.txt
- **LLMs-Full URL:** https://docs.textcontrol.com/tx-text-control-activex/document-editor/tables/formulas/llms-full.txt

---

A table cell in TX Text Control ActiveX can calculate its own text: you give it a formula with the [TableCellFormula](https://docs.textcontrol.com/tx-text-control-activex/reference/txtextcontrol/tablecellformula-property/llms-full.txt) property, and the cell displays the result of the calculation. The property addresses the cell by its table identifier, row and column.

![A table of the TXWords demo document whose cells calculate totals and percentages with formulas.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/table-formulas-document.7c67f2ba.webp)

Users enter a formula on the **Formulas** tab of the **Table Properties** dialog box that [TableAttrDialog](https://docs.textcontrol.com/tx-text-control-activex/reference/txtextcontrol/tableattrdialog-method/llms-full.txt) opens, and choose the A1 or R1C1 reference style there:

![The Formulas tab of the built-in Table Properties dialog box of TXTextControl with the formula, the functions list, the reference style and the number format.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/table-properties-formulas.cecddac8.webp)

The syntax of these formulas mostly follows the formula specification of SpreadsheetML (Office Open XML File Formats, ISO/IEC 29500-1, chapter 18.17.2 Syntax), which most spreadsheet applications, such as Microsoft Excel, also use. If you have written spreadsheet formulas before, the constants, operators, functions and cell references below will look familiar.

### How a Formula Is Built

A formula can consist of four kinds of elements: functions, cell references, constants and operators. The first three represent values inside the formula. Operators connect these elements and perform the calculation or operation.

The following sample formula contains a function, a cell reference, a constant and two operators:

![The sample formula with its four parts numbered: 1 the constant 2, 2 the caret and multiplication operators, 3 the function PI and 4 a relative R1C1 cell reference.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/formula-parts.b830642d.webp)

1. **Constant.** `2` is a number constant.
2. **Operators.** `^` is the caret operator. It raises the value of the table cell referenced by `R[-2]C[-1]` to the power of 2. The `*` operator multiplies the result of this calculation by 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 table cell two rows above and one column to the left of the formula's table cell.

### Use Constants

A constant is a value that doesn't change and can be part of a formula. TX Text Control supports five types of constants: numerical, logical, string, error and array constants.

#### Numerical Constants

A numerical constant is an integer (for example 4), a decimal number (for example 3.937) or a number in engineering notation (for example 5e4). It can have a minus sign in front of it (for example -7.6).

| Formula | Result |
|---|---|
| `9+7` | 16 |
| `4*5.5` | 22 |
| `4*3e5` | 1200000 |
| `-9+7` | -2 |

#### Logical Constants

There are two logical constants: `TRUE` and `FALSE`.

| Formula | Result |
|---|---|
| `15>7=TRUE` | TRUE |
| `15>7=FALSE` | FALSE |

#### String Constants

A string constant is a sequence of characters in double quotes.

| Formula | Result |
|---|---|
| `"TX"&"Text"&"Control"` | TXTextControl |
| `"Two"="One"` | FALSE |
| `"FALSE"="FALSE"` | TRUE |

#### Array Constants

An array constant represents a matrix of numerical, logical or string constants, or a mix of them. The constants of a row are separated by commas and the rows by semicolons. Braces enclose the array constant.

| Formula | Result |
|---|---|
| `SUM(10,{1,2,3;4,5})` | 25 |

### Combine Values With Operators

An operator applies an operation to the formula elements next to it. TX Text Control supports three groups of operators:

- **Arithmetic operators** perform mathematical operations. Their result is a numerical value.
- **Comparison operators** result in a logical value.
- **The text concatenation operator** joins strings.

#### Arithmetic Operators

| Arithmetic operator | Example |
|---|---|
| `+` (addition) | The formula `8+6` returns 14. |
| `-` (subtraction) | 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 Operators

| 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

| Text concatenation operator | Example |
|---|---|
| `&` (concatenation) | The formula `"TX"&"Spell"` returns TXSpell. |

### Call Functions

TX Text Control provides predefined functions that perform specific calculations. The result of a function is always a number, a logical value, a string or an array of such values.

A function begins with its unique name in uppercase letters, followed by an opening parenthesis, the arguments it needs separated by commas, and a closing parenthesis:

![The function POWER(1.234,5.1) with its function name, its two arguments and the parenthesis area labeled.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/formula-function-parts.6541df41.webp)

Valid arguments are numerical, logical, string and array values. You can give them as constants, as the result of another function or as 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)`: adds the numeric values of the arguments in argument-list | `SUM(1,2,3)` results in 6. |
| `SUM(argument-list)` with an array constant | `SUM({1;2;3})` results in 6. |
| `SUM(argument-list)` with a cell reference range | `SUM(A1:A3)` results in 6 if the table cells A1, A2 and A3 hold the values 1, 2 and 3. |

The [Supported Formula Functions](https://docs.textcontrol.com/tx-text-control-activex/reference/formula-functions/llms-full.txt) section lists every function, grouped into [Mathematical and Trigonometric](https://docs.textcontrol.com/tx-text-control-activex/reference/formula-functions/mathematical-trigonometric/llms-full.txt), [Statistical](https://docs.textcontrol.com/tx-text-control-activex/reference/formula-functions/statistical/llms-full.txt), [Logical](https://docs.textcontrol.com/tx-text-control-activex/reference/formula-functions/logical/llms-full.txt), [Lookup and Reference](https://docs.textcontrol.com/tx-text-control-activex/reference/formula-functions/lookup-reference/llms-full.txt) and [Information](https://docs.textcontrol.com/tx-text-control-activex/reference/formula-functions/information/llms-full.txt) functions.

### Refer to Other Table Cells

A formula often needs the values of other table cells, as an element of the formula or as an argument of a function. TX Text Control provides two styles of cell references, the R1C1 style and the A1 style, and the [FormulaReferenceStyle](https://docs.textcontrol.com/tx-text-control-activex/reference/txtextcontrol/formulareferencestyle-property/llms-full.txt) property determines which of them a formula uses.

#### R1C1 Style

An R1C1 cell reference gives the row as `R` and a number and the column as `C` and a number. These numbers can be relative or absolute.

The relative R1C1 style is the one TX Text Control recommends for cell references. A relative reference puts the numbers in square brackets, and they count rows and columns from the table cell in which the formula is defined.

In the following table, a formula is defined in the fourth row (R4) of the third column (C3):

![A table of five rows and four columns, labeled R1 to R5 and C1 to C4, with a formula with a cell reference in the cell at R4 and C3 and numbers in the other cells.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/formula-r1c1-table.44608763.webp)

| Relative R1C1 reference | Description |
|---|---|
| `R[-3]C[-1]` | Refers to the table cell in the first row (R1) and the second column (C2), with the value 3. |
| `R[1]C[-2]` | Refers to the table cell in the fifth row (R5) and the first column (C1), with the value 14. |
| `R[0]C[1]` | Refers to the table cell in the same row (R4) and the fourth column (C4), with the value -5. |
| `R[-2]C[1]` | Refers to the table cell in the second row (R2) and the fourth column (C4), with the value 5. |

An absolute R1C1 reference has no square brackets around its numbers. It always refers to the same cell position, no matter in which table cell the formula is defined: `R5C4` is always the cell in the fifth row and the fourth column. Absolute R1C1 references are supported, but don't use them if the table is edited or the part of the table that contains them is pasted as a new table. In these cases, an absolute R1C1 reference returns invalid or incorrect values.

#### A1 Style

An A1 cell reference gives the column in uppercase letters (A to Z, then AA to AZ, BA to BZ and so on; the first column is A, the second B) followed by the row as an integer. `B3` refers to the table cell in the second column and the third row. This notation is a relative cell reference and is the equivalent of the relative R1C1 style.

To make an A1 reference absolute, put a `$` sign to the left of the column, the row or both. An absolute reference does not depend on the table cell in which the formula is defined.

#### Cell Reference Ranges

Some functions accept an argument list with any number of values. If these values are in table cells, adding the references one by one is inconvenient. A cell reference range defines a matrix of table cells instead: two cell references separated by a colon, the first for the upper left and the second for the lower right corner of the range.

The following table applies four ranges to the `SUM` function in a formula defined in the cell at R3 and C3:

![A table of five rows and five columns with a formula with a cell reference at R3 and C3 and four highlighted, numbered ranges of cells: 1 from R2C1 to R3C2, 2 from R1C3 to R2C3, 3 from R1C5 to R4C5 and 4 from R5C2 to R5C4.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/formula-cell-ranges.38fceb7e.webp)

| Formula with cell reference range | Description |
|---|---|
| 1. `SUM(R[-1]C[-2]:R[0]C[-1])` | Sums the values between R2C1 and R3C2 and returns 16. |
| 2. `SUM(R[-2]C[0]:R[-1]C[0])` | Sums the values between R1C3 and R2C3 and returns 14. |
| 3. `SUM(R[-2]C[2]:R[1]C[2])` | Sums the values between R1C5 and R4C5 and returns 20. |
| 4. `SUM(R[2]C[-1]:R[2]C[1])` | Sums the values between R5C2 and R5C4 and returns 19. |

#### LEFT, ABOVE and RIGHT

The flags `LEFT`, `ABOVE` and `RIGHT` are another way to specify a cell reference range. Each of them refers to the cells next to the formula's table cell in its direction: `SUM(ABOVE)` sums the values of all cells above the formula's table cell.

The following table applies the three flags to the `SUM` function in the cell at R3 and C3:

![A table of five rows and five columns with SUM(...) in the cell at R3 and C3 and three highlighted, numbered ranges: 1 the cells to the left of it, 2 the cells above it and 3 the cells to the right of it.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/formula-left-above-right.7efae2a0.webp)

| Formula with cell reference range | Description |
|---|---|
| 1. `SUM(LEFT)` | Sums the values to the left of 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 to the right of the formula's table cell and returns 8. |

### Related Pages

- [Number Formats](https://docs.textcontrol.com/tx-text-control-activex/document-editor/tables/number-formats/llms-full.txt): displaying the results of formulas with a common format.
- [Programming with Table Identifiers](https://docs.textcontrol.com/tx-text-control-activex/document-editor/tables/identifiers/llms-full.txt): the identifiers that address a table from code.
- [Tables](https://docs.textcontrol.com/tx-text-control-activex/document-editor/tables/llms-full.txt): inserting tables and the members for programming with them.
- [Supported Formula Functions](https://docs.textcontrol.com/tx-text-control-activex/reference/formula-functions/llms-full.txt): every function a formula can call.

---

## Nearby

- **Previous:** [Programming With Table Identifiers](https://docs.textcontrol.com/tx-text-control-activex/document-editor/tables/identifiers/llms-full.txt)
- **Next:** [Number Formats](https://docs.textcontrol.com/tx-text-control-activex/document-editor/tables/number-formats/llms-full.txt)