Use formulas in tables
Insert a formula
You can perform simple calculations on data in table cells by adding formulas. To insert a formula into a table cell,
- place the cursor within the cell where you want to display the result,
- click the Add formula button at the right sidebar,
- in the Formula Settings window that opens, enter the necessary formula into the Formula field.
You can enter a needed formula manually using the common mathematical operators (+, -, *, /), e.g. =A1*B2 or use the Paste Function drop-down list to select one of the embedded functions, e.g. =PRODUCT(A1,B2).
- manually specify necessary arguments within the parentheses in the Formula field. If the function requires several arguments, they must be separated by commas.
- use the Number Format drop-down list if you want to display the result in a certain number format,
- click OK.
The result will be displayed in the selected cell.
To edit the added formula, select the result in the cell and click the Add formula button at the right sidebar, make the necessary changes in the Formula Settings window and click OK.
Add references to cells
You can use the following arguments to quickly add references to cell ranges:
- ABOVE - a reference to all the cells in the column above the selected cell
- LEFT - a reference to all the cells in the row to the left of the selected cell
- BELOW - a reference to all the cells in the column below the selected cell
- RIGHT - a reference to all the cells in the row to the right of the selected cell
These arguments can be used with the AVERAGE, COUNT, MAX, MIN, PRODUCT, SUM functions.
You can also manually enter references to a certain cell (e.g., A1) or a range of cells (e.g., A1:B3).
Use bookmarks
If you have added some bookmarks to certain cells within your table, you can use these bookmarks as arguments when entering formulas.
In the Formula Settings window, place the cursor within the parentheses in the Formula entry field where you want the argument to be added and use the Paste Bookmark drop-down list to select one of the previously added bookmarks.
Update formula results
If you change some values in the table cells, you will need to manually update formula results:
- To update a single formula result, select the necessary result and press F9 or right-click the result and use the Update field option from the menu.
- To update several formula results, select the necessary cells or the entire table and press F9.
Embedded functions
You can use the following standard math, statistical and logical functions:
| Category | Function | Description | Example |
| Mathematical | ABS(x) | The function is used to return the absolute value of a number. | =ABS(-10) Returns 10 |
| Logical | AND(logical1, logical2, ...) | The function is used to check if the logical value you enter is TRUE or FALSE. The function returns 1 (TRUE) if all the arguments are TRUE. | =AND(1>0,1>3) Returns 0 |
| Statistical | AVERAGE(argument-list) | The function is used to analyze the range of data and find the average value. | =AVERAGE(4,10) Returns 7 |
| Statistical | COUNT(argument-list) | The function is used to count the number of the selected cells which contain numbers ignoring empty cells or those contaning text. | =COUNT(A1:B3) Returns 6 |
| Logical | DEFINED() | The function evaluates if a value in the cell is defined. The function returns 1 if the value is defined and calculated without errors and returns 0 if the value is not defined or calculated with an error. | =DEFINED(A1) |
| Logical | FALSE() | The function returns 0 (FALSE) and does not require any argument. | =FALSE Returns 0 |
| Mathematical | INT(x) | The function is used to analyze and return the integer part of the specified number. | =INT(2.5) Returns 2 |
| Statistical | MAX(number1, number2, ...) | The function is used to analyze the range of data and find the largest number. | =MAX(15,18,6) Returns 18 |
| Statistical | MIN(number1, number2, ...) | The function is used to analyze the range of data and find the smallest number. | =MIN(15,18,6) Returns 6 |
| Mathematical | MOD(x, y) | The function is used to return the remainder after the division of a number by the specified divisor. | =MOD(6,3) Returns 0 |
| Logical | NOT(logical) | The function is used to check if the logical value you enter is TRUE or FALSE. The function returns 1 (TRUE) if the argument is FALSE and 0 (FALSE) if the argument is TRUE. | =NOT(2<5) Returns 0 |
| Logical | OR(logical1, logical2, ...) | The function is used to check if the logical value you enter is TRUE or FALSE. The function returns 0 (FALSE) if all the arguments are FALSE. | =OR(1>0,1>3) Returns 1 |
| Mathematical | PRODUCT(argument-list) | The function is used to multiply all the numbers in the selected range of cells and return the product. | =PRODUCT(2,5) Returns 10 |
| Mathematical | ROUND(x, num_digits) | The function is used to round the number to the desired number of digits. | =ROUND(2.25,1) Returns 2.3 |
| Mathematical | SIGN(x) | The function is used to return the sign of a number. If the number is positive, the function returns 1. If the number is negative, the function returns -1. If the number is 0, the function returns 0. | =SIGN(-12) Returns -1 |
| Mathematical | SUM(argument-list) | The function is used to add all the numbers in the selected range of cells and return the result. | =SUM(5,3,2) Returns 10 |
| Logical | TRUE() | The function returns 1 (TRUE) and does not require any argument. | =TRUE Returns 1 |