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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data 1: | Data 2: | Formulas: | Result: |
| 2 | 1 | 0.4 | =A2+A3+A4 | 8 |
| 3 | 2 | -0.9 | =Sum: {SUM(B2:B8)} | Sum: 11.5 |
| 4 | 5 | 4.5 | =SIN(PI()/2) | 1 |
| 5 | 4 | -0.1 | =ABS(B3)+CEIL(B4,0) | 0.9 |
| 6 | 3 | 2.2 | =POWER(A4,A5) | 625 |
| 7 | 7 | -0.4 | =MEDIAN(A2:A8) | 4 |
| 8 | 8 | 5.8 | =IF(A4>A5,1,0) | =IF(B5>B6,1,0) |
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_ID | ID of the current table |
NUM_ROWS | Number of rows of the current table |
NUM_COLUMNS | Number of columns of the current table |
ROW | Number of the row of this cell |
COLUMN | Number of the column of this cell |
CELL | Cell 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.