Calculation functions
Arithmetic, rounding, modulo, powers, absolute values, standard deviation, and the conditional aggregates sumif / countif / maxif / minif / average / median / quartiles / distinctcountif.
Two kinds: per-row arithmetic (calc for the four operations, ceil / floor / mod / power / abs), and cross-row conditional aggregates (the sumif family). Conditional aggregates are the core of growing a summary table; the x-prefixed xsumif / xcountif calculate incrementally when the primary key is not a formula, and can trigger automations.
Conventions: reference a column by its code (AA6); function names are case-insensitive; text goes in double quotes; conditions accept and / or. "In the result column, enter" means typing the formula into a new column's formula bar. The wording below is the same as the in-app function help.
calc
Basic addition, subtraction, multiplication, and division calculations
Formula Description
Performs basic addition (+), subtraction (-), multiplication (*), and division (/) calculations on columns/numbers.
Formula Syntax
In the result column, enter:
column/value + column/value
column/value - column/value
column/value * column/value
column/value / column/value- Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1
add
Sum of numerical values
Formula Description
Sums numerical values horizontally across columns, supports multi-column calculations, and treats non-numeric data as "0" in calculations.
Adds numbers together.
Formula Syntax
Directly input the column CODE in the result column (i.e., the number at the top of each column in the table data, e.g., AA1)
Directly input the numbers to be calculated in the result column (e.g., 1+2+3)
abs
No detailed description ships in the app for this one — the function help in the formula editor is authoritative.
ceil
Formula Description
Ceiling function, returns the smallest integer greater than or equal to a given number.
Formula Syntax
Input in result column: ceil(value / column number)- Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1
floor
Formula Description
Floor function, returns the largest integer less than or equal to a specified number.
Formula Syntax
Input in result column: floor(value / column number)- Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1
mod
Formula Description
Modulo function, which calculates the remainder after division of two numeric expressions.
Formula Syntax
In the result column, enter: mod(dividend / column number, divisor / column number)- Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1
power
Formula Description
Exponentiation function, calculates a number raised to a power, i.e., a^b.
Formula Syntax
In the result column, enter: power(base / column number, exponent)- Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1
stdevs
Calculate standard deviation
Formula Description
Calculates the standard deviation.
Standard deviation measures the spread of values around the mean.
Formula Syntax
In the result column, enter: stdevs(data column / data column1..n)if
Returns conditional judgment
Formula Description
Evaluates a condition as "TRUE" or "FALSE" and returns corresponding content based on the logical result.
Compares values against expected values;
Performs logical evaluation based on logical expressions;
Returns different results based on the evaluation, executing conditional checks on values or formulas.
Formula Syntax
In the result column, enter: if(condition, value_if_true, value_if_false)Given a condition, if a value meets the condition, it returns A; if it doesn't meet the condition, it returns B.
sumif
Filters data rows based on given conditions and sums the specified column
Formula Description
sumif filters data rows based on given conditions and sums the specified column. xsumif [when the serial number column is not a formula] enables incremental updates, replacing the sumif function, and can trigger automation.
Formula Syntax
In the result column, enter: sumif(column_to_sum, condition)column_to_sum: The column to be summed, e.g., AH2;
condition: Filters data rows in the original table, e.g., AH4>90, AH3="phone" and AH3=AI1
countif
Formula Description
countif: Filters data rows based on given conditions and counts the specified column.
xcountif: When the serial number column is not a formula enables incremental updates, replacing the countif function, and can trigger automation.
Formula Syntax
In the result column, enter: countif(column_to_count, condition)- column_to_count: The column number to be counted, e.g., AA1, AD12;
- condition: Filters the values in the count column, e.g., AA1>90, AH3=AI1 and AC1=AD3
- Can perform calculations within the current table or across tables
distinctcountif
Formula Description
Filters data rows based on given conditions and counts the number of unique values in the specified column.
Formula Syntax
In the result column, enter: distinctcountif(column_number, condition)- column_number: The column to count unique values, e.g., AA1, AD12;
- condition: Filters the values in the data column, e.g., AB2="phone" and AH3=AI1
maxif
Retrieves the maximum value from the specified column
Formula Description
Filters data rows based on given conditions and retrieves the maximum value from the specified column.
Formula Syntax
In the result column, enter: maxif(column_to_evaluate, condition)- column_to_evaluate: The column to evaluate, e.g., AA1, AD12;
- condition: Filters the values in the data column, e.g., AB2="phone" and AH3=AI1
minif
Retrieves the minimum value from the specified column
Formula Description
Filters data rows based on given conditions and retrieves the minimum value from the specified column.
Formula Syntax
In the result column, enter: minif(column_to_evaluate, condition)- column_to_evaluate: The column to evaluate, e.g., AA1, AD12;
- condition: Filters the values in the data column, e.g., AB2="phone" and AH3=AI1
average
Formula Description
Calculates the average of a set of values.
Formula Syntax
In the result column, enter: average(column_number)- column_number: The column to calculate the average, e.g., AA1, AD12
median
Formula Description
Calculates the median of a set of values.
Formula Syntax
In the result column, enter: median(column_number)- column_number: The column to calculate the median, e.g., AA1, AD12
lowerquartile
Calculates the lower quartile of a set of values
Formula Description
Calculates the lower quartile (25th percentile) of a set of values.
Formula Syntax
In the result column, enter: lowerquartile(column_number)- column_number: The column to calculate the lower quartile, e.g., AA1, AD12
upperquartile
Calculates the upper quartile of a set of values
Formula Description
Calculates the upper quartile (75th percentile) of a set of values.
Formula Syntax
In the result column, enter: upperquartile(column_number)- column_number: The column to calculate the upper quartile, e.g., AA1, AD12
quartileif
Formula Description
Filters data rows based on given conditions and calculates the X/4 quartile for the specified column.
Formula Syntax
quartileif(column_number, quartile, filter_condition)quartile: Integer from 1 to 3, representing the 1/4, 1/2, and 3/4 quartiles respectively
condition: Data condition for filtering IDs. E.g., AB1 = AD3, AB1 = 13, AB1 < 100.9
All parameters can use data columns, i.e., the CODE at the top of each table data column, e.g., AA1
stdevsif
Formula Description
Filters data rows based on given conditions and calculates the estimated standard deviation for the specified column.
Standard deviation measures the spread of values around the mean.
Formula Syntax
In the result column, enter: stdevsif(column_number, condition)All parameters can use data columns, i.e., the CODE at the top of each table data column, e.g., AA1
condition: Data condition for filtering IDs. E.g., AB1 = AD3, AB1 = 13, AB1 < 100.9