Articles with the tag:
Close
Changelog
Close
Articles with the tag:
Close

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,

  1. place the cursor within the cell where you want to display the result,
  2. click the Add formula button at the right sidebar,
  3. 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).

    Add formula

  4. manually specify necessary arguments within the parentheses in the Formula field. If the function requires several arguments, they must be separated by commas.
  5. use the Number Format drop-down list if you want to display the result in a certain number format,
  6. 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
Return to previous page
Try now for free Try and make your decision No need to install anything
to see all the features in action