TablePress features explained: Formulas

TablePress supports mathematical functions and formulas in table cells, in a very similar way as they can be used in Excel or other spreadsheet applications.

The following table shows some examples of made-up data and formulas that calculate something:

 ABCD
1Data 1:Data 2:Formulas:Result:
210.4=A2+A3+A48
32-0.9=Sum: {SUM(B2:B8)}Sum: 11.5
454.5=SIN(PI()/2)1
54-0.1=ABS(B3)+CEIL(B4)5.9
632.2=POWER(A4,A5)625
77-0.4=MEDIAN(A2:A8)4
885.8=IF(A4>A5,1,0)1

The content of a cell is evaluated as a formula, if it starts with the equal sign =.
Formulas can reference other cells, by stating the column letter and the row number. It is possible to use single references, like A2, or to reference multiple cells in a row, column, or even block, by using the colon (:) operator. With that, A2:A8 means all cells in the A column, while A3:E3 would refer to all cells in the third row.
Those references will even be adjusted automatically whenever the referenced cell changes position in the table, e.g. because its row or column is moved or because another row or column is deleted or inserted.

Also, it is possible to wrap a formula in text, with the syntax =TEXT{FORMULA}TEXT. The cell content still has to start with the equal sign = and the formulas then have to be wrapped in curly brackets {and }. This can be seen in the example =Sum: {SUM(B2:B8)} in cell C3 in the table above.

Most of the supported functions expect only one argument, either a number, or a reference to a cell, while some of the functions will work with multiple arguments, either given as a comma-separated list, or by using the colon operator that is described above.

These functions are supported by TablePress:

Math operators:

+addition
-subtraction
*multiplication
/division
^power

Comparison operators:

=equal
<smaller than
>greater than

Placeholder variables:

TABLE_IDID of the current table
NUM_ROWSNumber of rows of the current table
NUM_COLUMNSNumber of columns of the current table
ROWNumber of the row of this cell
COLUMNNumber of the column of this cell
CELLCell name/reference of this cell

Basic math functions:

sqrt(x)Square root of x
abs(x)Absolute value of x
ln(x)Natural logarithm of x (Logarithm of x to the base e)
log(x,b)Logarithm of x to the base b
log10(x)Logarithm of x to the base 10
exp(x)Exponential function
power(x,y)Value of the x raised to the yth power
round(x,p)Round x to the precision p (The default p=0 will result in an integer.)
floor(x)Greatest integer that is smaller or equal to x
ceil(x)Smallest integer that is greater or equal to x
pi()Value of π (3.14159…)

Statistics functions:

average()
(also as mean())
Average value of the arguments
median()Median value of the arguments
mode()Mode value (most common value) of the arguments
range()Range: Difference between the largest and smallest arguments
max()Largest value of the arguments
min()Smallest value of the arguments
mod(x,y)Remainder of the division x divided by y

Other functions:

number_format(x,d)Formats x with d decimals after the period . where the comma , will be used as the thousands delimiter (if x has more than four digits)
number_format_eu(x,d)Formats x with d decimals after the comma , where the period . will be used as the thousands delimiter (if x has more than four digits)
sum()Sum of the arguments
product()Product of the arguments
rand_int(a,b)Random integer between a and b
rand_float()Random real number between 0 and 1
if(condition,then,else)If condition is true, then will be returned, and else otherwise
counta()Number of non-empty values from the arguments

Trigonometric functions:

sin(x)Sine of x
sinh(x)Hyperbolic sine of x
arcsin(x)
(also as asin(x))
Inverse sine of x
arcsinh(x)
(also as asinh(x))
Inverse hyperbolic sine of x
cos(x)Cosine of x
cosh(x)Hyperbolic cosine of x
arccos(x)
(also as acos(x))
Inverse cosine of x
arccosh(x)
(also as acosh(x))
Inverse hyperbolic cosine of x
tan(x)Tangent of x
tanh(x)Hyperbolic tangent of x
arctan(x)Inverse tangent of x
arctan2(x,y)
(also as atan2(x,y))
Inverse tangent of the quotient x/y, while taking the sign into account
arctanh(x)Inverse hyperbolic tangent of x

Note that all of them work with pure numbers only. If you need to do calculations on e.g. dates or work with text, you should consider using Excel or similar and export the final results table as a CSV file and import that into TablePress.

If this feature is helpful for you, please consider supporting further TablePress development with a donation. Thank you!

Test: 25[table “veranstaltungen-2020” not found /]
Top