TablePress features explained: Formulas

TablePress has a really neat feature that many people like, once they find out about it: You can use mathematical functions and formulas in your table cells, in a very similar way as you can use them in Excel or other spreadsheet applications.

To show you what I mean, here’s a quick example of a table that contains some made-up data, and some 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 =.
The nice thing in the formulas is that you can use references to other cells, by stating the column letter and the row number. You can either use single references, like A2, or 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.

There are multiple functions available:

Math operators:

+addition
-subtraction
*multiplication
/division
^power

Comparison operators:

=equal
<smaller than
>greater than

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
floor(x)Greatest integer that is smaller or equal to x
ceil(x)Smallest integer that is greater or equal to x

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
pi()Value of π (3.14159…)
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.)
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

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

Most of them 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.

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

Top