Statistics

Statistics functions operate over a table.

Reading the table

Most take a single-column table, or a multi-column table plus a column index to operate on. The first column is index 1, and when column is omitted it defaults to 1; a column out of range returns an Invalid array dimensions error.

Units in a column

Cells may carry units. Compatible units convert before the calculation; mixing dimensions — a length with a mass — returns input.incompatible_units. Where a result is a quantity, it keeps the unit: sum and avg report in the first cell’s unit, while min and max report the winning cell as it was written.

sum([1m; 100cm])          => 2 m
average([1m; 2m; 3m])     => 2 m
min([5m; 3ft])            => 3 ft
max([1m; 50cm; 2ft])      => 1 m
stddev([1m; 2m; 3m])      => 1 m

Three results are a different dimension than the input, because of what they measure: variance, product, and sum2 are squared or multiplied quantities, and count is always a bare number.

variance([1m; 2m; 3m])    => 1 m^2
product([2m; 3m])         => 6 m^2
count([1m; 2m])           => 2

Aggregation

These read a table column — see Reading the table.

Count [count]

count(table)

The number of rows in table.

count([2; 3; 4])   => 3

Sum [sum]

sum(table)
sum(table; column)

The sum of the first column of table, or of the given column.

sum([3; 4; 5])                     => 12
sum([[3;10]; [4;20]; [5;30]]; 2)   => 60

Average [avg, mean, average]

avg(table)
avg(table; column)

The average of the values. mean and average are aliases.

avg([3; 4; 5])                     => 4
avg([[3;10]; [4;20]; [5;30]]; 2)   => 20

Product [product]

product(list)

The product of the values in a one-dimensional list.

Range and extremes

These read a table column — see Reading the table.

Minimum [min, minimum]

min(table)
min(table; column)
min(valueA; valueB; ...)

The minimum value. Given several equal-length single-column tables, returns the row-wise minimum as a table.

min([3; 4; 5; 9])                       => 3
min(3; 4; 5; 9)                         => 3
min([3;50]; [4;25]; [5;40]; [9;10])     => [3; 4; 5; 9]

Maximum [max, maximum]

max(table)
max(table; column)
max(valueA; valueB; ...)

The maximum value, with the same forms as min.

max([3; 4; 5; 9])                          => 9
max([[3;50]; [4;25]; [5;40]; [9;10]]; 2)   => 50

Range [range]

range(table)
range(table; column)

The difference between the maximum and minimum.

range([3; 4; 5; 9])   => 6

Dispersion

These read a table column — see Reading the table.

Standard deviation [stddev, stddevp]

stddev(table)
stddevp(table)
stddev(table; column)
stddevp(table; column)

stddev is the sample standard deviation; stddevp is the population standard deviation.

stddev([3; 4; 5; 6; 7; 8])    => 1.8708
stddevp([3; 4; 5; 6; 7; 8])   => 1.7078

Variance [variance, variancep]

variance(table)
variancep(table)
variance(table; column)
variancep(table; column)

variance is the sample variance; variancep is the population variance.

variance([3; 4; 5; 6; 7; 8])    => 3.5
variancep([3; 4; 5; 6; 7; 8])   => 2.9167

Median and quartiles [median, quartile1, quartile3]

median(table)
median(table; column)
quartile1(table)
quartile1(table; column)
quartile3(table)
quartile3(table; column)

The median, first quartile, and third quartile.

median([3; 4; 5; 5; 6])         => 5
quartile1([3; 4; 5; 6; 7; 8])   => 4
quartile3([3; 4; 5; 6; 7; 8])   => 7

Sums of products

These read a table column — see Reading the table.

Sum of products [sumofproducts]

sumofproducts(table)
sumofproducts(table; resultsAsTable)

Multiplies the items within each row, then sums those products. This is the operation for a weighted sum or dot product — a table of (weight; value) rows totals to sumofproducts(table), where sum reads only the first column. With resultsAsTable set to true, returns the per-row products as a table instead.

sumofproducts([[3;4]; [5;6]; [7;8]])         => 98
sumofproducts([[3;4]; [5;6]; [7;8]]; true)   => [12; 30; 56]

Sum of squares [sum2]

sum2(table)
sum2(table; column)

The sum of the squares of the values.

sum2([3; 4; 5; 6; 7; 8])   => 199

Sum XY [sumxy]

sumxy(table; x_column; y_column)

The sum of each row’s x_column value multiplied by its y_column value.

sumxy([[3;10]; [4;20]; [5;20]; [6;30]; [7;40]; [8;50]]; 1; 2)   => 1070

Probability

These take plain numbers. An argument carrying a unit returns input.incompatible_type.

Factorial [fact]

fact(value)

The factorial of value, a whole number from 1 to 170. Values outside that range return a Parameter out of range error.

fact(5)          => 120
fact([2; 3; 4])  => [2; 6; 24]

Permutations [perm]

perm(n; r)

The number of permutations of n taken r at a time (order matters). n and r must be whole numbers with n ≥ r > 0.

perm(8; 3)            => 336
perm([8;7;6]; 3)      => [336; 210; 120]

Combinations [comb]

comb(n; r)

The number of combinations of n taken r at a time (order does not matter). n and r must be whole numbers with n ≥ r > 0.

comb(8; 3)            => 56
comb([8;7;6]; 3)      => [56; 35; 20]

Random [rand]

rand()
rand(length)
rand(lower; upper)
rand(lower; upper; length)

A random real number between 0 and 1 inclusive, or a random whole number between lower and upper inclusive. With length, returns a table of that many random values.

Introduction

Quickstart

Concepts

Domain Library

Playground

Authoring Domains

Data Types

Units

Function Library

API Reference

Resources

Reading the table

Units in a column

Aggregation

Count [count]

Sum [sum]

Average [avg, mean, average]

Product [product]

Range and extremes

Minimum [min, minimum]

Maximum [max, maximum]

Range [range]

Dispersion

Standard deviation [stddev, stddevp]

Variance [variance, variancep]

Median and quartiles [median, quartile1, quartile3]

Sums of products

Sum of products [sumofproducts]

Sum of squares [sum2]

Sum XY [sumxy]

Probability

Factorial [fact]

Permutations [perm]

Combinations [comb]

Random [rand]

​Reading the table

​Units in a column

​Aggregation

​Count [count]

​Sum [sum]

​Average [avg, mean, average]

​Product [product]

​Range and extremes

​Minimum [min, minimum]

​Maximum [max, maximum]

​Range [range]

​Dispersion

​Standard deviation [stddev, stddevp]

​Variance [variance, variancep]

​Median and quartiles [median, quartile1, quartile3]

​Sums of products

​Sum of products [sumofproducts]

​Sum of squares [sum2]

​Sum XY [sumxy]

​Probability

​Factorial [fact]

​Permutations [perm]

​Combinations [comb]

​Random [rand]

Reading the table

Units in a column

Aggregation

Count [count]

Sum [sum]

Average [avg, mean, average]

Product [product]

Range and extremes

Minimum [min, minimum]

Maximum [max, maximum]

Range [range]

Dispersion

Standard deviation [stddev, stddevp]

Variance [variance, variancep]

Median and quartiles [median, quartile1, quartile3]

Sums of products

Sum of products [sumofproducts]

Sum of squares [sum2]

Sum XY [sumxy]

Probability

Factorial [fact]

Permutations [perm]

Combinations [comb]

Random [rand]