Column Formulas 

Overview 

Column formulas allow you to generate column values dynamically based on mathematical expressions. Formulas can reference other columns, use built-in functions, and automatically update when source data changes. The formula system provides over 600 functions from the GNU Scientific Library (GSL), including mathematical, statistical, logical, and special functions.

Basic Syntax 

Creating a Formula 

To create a formula column:

Right-click on a column header → Formula
Enter your formula expression
Map variable names to actual columns
Enable Auto Update to recalculate automatically

Note

Columns can be referenced from any spreadsheet in the project, not just the current one. When mapping variables, you can select columns from different spreadsheets.

Syntax Rules 

Variables: Reference other columns (e.g., x, y, data)
Operators: +, -, *, /, ^ (power)
Functions: Use semicolon ; to separate arguments (not comma)
Constants: pi, e, and others from GSL
Cross-spreadsheet: Variables can reference columns from any spreadsheet in the project

Examples 

Basic arithmetic:

x * 2 + y

Distance calculation:

sqrt(x^2 + y^2)

Conditional logic:

if(temperature > 0; 1; 0)

Normalization (z-score):

(x - mean(x)) / stdev(x)

Moving average:

sma(10; temperature)

Date extraction:

year(date_column)

Trigonometry with degree conversion:

sin(angle_degrees * pi / 180)

Cross-spreadsheet reference:

# Variables can reference columns from different spreadsheets
# Example: temperature from Spreadsheet1 minus baseline from Spreadsheet2
temp - baseline

# Where 'temp' is mapped to: Project/Spreadsheet1/Temperature
# And 'baseline' is mapped to: Project/Spreadsheet2/Baseline

Available Functions 

Standard Mathematical Functions 

Function	Description	Example
`sqrt(x)`	Square root	`sqrt(16)` → 4
`pow(x; y)`	Power x^y	`pow(2; 8)` → 256
`exp(x)`	Exponential e^x	`exp(1)` → 2.718…
`log(x)`	Natural logarithm	`log(e)` → 1
`log10(x)`	Base-10 logarithm	`log10(100)` → 2
`abs(x)`, `fabs(x)`	Absolute value	`fabs(-5)` → 5
`ceil(x)`	Round up	`ceil(3.2)` → 4
`floor(x)`	Round down	`floor(3.8)` → 3
`round(x)`	Round to nearest	`round(3.5)` → 4
`roundn(x; n)`	Round to n decimals	`roundn(3.14159; 2)` → 3.14
`sgn(x)`	Sign (-1, 0, 1)	`sgn(-5)` → -1

Comparison and Logical Functions 

Function	Description	Returns
`if(cond; true_val; false_val)`	Conditional expression	Value based on condition
`and(a; b)`	Logical AND	1.0 or 0.0
`or(a; b)`	Logical OR	1.0 or 0.0
`not(a)`	Logical NOT	1.0 or 0.0
`equal(a; b)`	Equality test	1.0 or 0.0
`greaterThan(a; b)`	Greater than	1.0 or 0.0
`lessThan(a; b)`	Less than	1.0 or 0.0
`between(x; min; max)`	In range (exclusive)	1.0 or 0.0
`between_inc(x; min; max)`	In range (inclusive)	1.0 or 0.0

Note

You can also use operators: >, >=, <, <=, ==, !=

Example: Nested conditions

if(score >= 80; 2; if(score >= 60; 1; 0))

Column Statistics 

Compute statistics over entire columns. These return a constant value repeated for all rows.

Function	Description	Example
`size(col)`	Number of values	`size(x)`
`sum(col)`	Sum of values	`sum(x)`
`min(col)`	Minimum value	`min(x)`
`max(col)`	Maximum value	`max(x)`
`mean(col)`	Arithmetic mean	`mean(x)`
`median(col)`	Median	`median(x)`
`stdev(col)`	Standard deviation	`stdev(x)`
`var(col)`	Variance	`var(x)`
`quartile1(col)`	First quartile (Q1)	`quartile1(x)`
`quartile3(col)`	Third quartile (Q3)	`quartile3(x)`
`iqr(col)`	Interquartile range	`iqr(x)`
`percentile(p; col)`	p-th percentile	`percentile(95; x)`
`skew(col)`	Skewness	`skew(x)`
`kurt(col)`	Kurtosis	`kurt(x)`

Example: Detect outliers

if(fabs(x - mean(x)) > 2 * stdev(x); 1; 0)

Moving Statistics 

Operate on sliding windows of data.

Function	Description	Example
`sma(window; col)`	Simple moving average	`sma(5; x)`
`smmin(window; col)`	Moving minimum	`smmin(5; x)`
`smmax(window; col)`	Moving maximum	`smmax(5; x)`
`smr(window; col)`	Moving range	`smr(5; x)`

Cell Access Functions 

Access individual cell values for lag/lead operations and custom calculations.

Function	Description	Args
`cell(index; col)`	Access cell at index in another column	2
`cell_with_default(index; default; col)`	Access cell with default if out of range	3
`cell_curr_column(index)`	Access cell in current column	1
`cell_curr_column_with_default(index; default)`	Access cell in current column with default	2

Examples:

# Access previous value (lag 1)
cell_curr_column_with_default(i-1; 0)

# Access next value (lead 1)
cell_curr_column_with_default(i+1; 0)

# Difference from previous value
x - cell_curr_column_with_default(i-1; x)

# Access another column at offset
cell_with_default(i-2; 0; temperature)

# Moving difference (current minus 5 rows back)
x - cell_curr_column_with_default(i-5; x)

Trigonometric Functions 

All angles in radians. Available functions: sin, cos, tan, asin, acos, atan, atan2, sinh, cosh, tanh, asinh, acosh, atanh, and many more.

Example: Convert degrees to radians

sin(angle_degrees * pi / 180)

Example: Hypotenuse

hypot(x; y)  # Equivalent to sqrt(x^2 + y^2)

Datetime Functions 

Work with dates and times. Dates are represented as days since 1900-01-01.

Creation Functions 

today() - Current date
now() - Current date and time
date(year; month; day) - Create date
time(hour; minute; second) - Create time

Extraction Functions 

year(date) - Extract year
month(date) - Extract month (1-12)
day(date) - Extract day (1-31)
weekday(date) - Day of week (1=Monday)
hour(datetime) - Extract hour
minute(datetime) - Extract minute

Calculation Functions 

datedif(start; end; unit) - Date difference
networkdays(start; end) - Business days between dates

Examples 

Age calculation:

year(today()) - year(birthdate)

Quarter extraction:

ceil(month(date) / 3)

Special Functions 

LabPlot provides 600+ specialized functions through GSL:

Bessel Functions: J0, J1, Jn, Y0, Y1, Yn, I0, K0, etc.
Gamma and Beta: gamma, lgamma, beta, fact (factorial), choose (binomial)
Error Functions: erf, erfc, voigt
Elliptic Integrals: Kc, Ec, F, E
Legendre Polynomials: P1, P2, Pl
Hypergeometric: hyperg_0F1, hyperg_1F1, hyperg_2F1
Probability Distributions: PDF, CDF, and quantile functions for Gaussian, Student’s t, Chi-squared, F, Exponential, Gamma, Beta, Poisson, Binomial, and many more

Random Number Generation 

Generate random values from various distributions:

rand() - Random integer [0, RAND_MAX]
drand() - Random float [0, 1]
randflat(a; b) - Uniform random [a, b]
randgaussian(sigma) - Gaussian random
randpoisson(mu) - Poisson random
randbinomial(p; n) - Binomial random

Example: Add noise

x + randgaussian(0.1)

Text Columns and Formulas 

Formula results are always numeric. For text columns, use numeric codes with value labels:

Create a text column for the result
Use a numeric formula: if(score >= 80; 2; if(score >= 60; 1; 0))
Add value labels to the column:
- 0 → “Fail”
- 1 → “Pass”
- 2 → “Excellent”
The spreadsheet displays text while storing numbers internally

This approach works for:

Conditional text assignment
Categorical data classification
Status indicators

Tips and Best Practices 

Performance 

Disable Auto Update during data entry for large datasets
Re-enable when done editing
Column statistics are cached and fast to use

Accuracy 

Use log1p(x) instead of log(1+x) for small x
Use hypot(x; y) instead of sqrt(x^2+y^2) to avoid overflow
Use equalE(a; b; epsilon) for floating-point comparisons

Debugging 

Test formulas on small datasets first
Check for division by zero: if(equal(x; 0); 0; y/x)
Check for negative square roots: if(x >= 0; sqrt(x); 0)

Common Patterns 

Clamp values to range:

if(x < min; min; if(x > max; max; x))

Distance from mean in standard deviations:

fabs(x - mean(x)) / stdev(x)

Percentage change:

(current - previous) / previous * 100

Running total:

sum(x) / size(x) * i  # where i is row index

Additional Resources 

For a complete reference of all 620 available functions with detailed descriptions and examples, see the LabPlot source code.