Descriptive statistics

Summarise a list of numbers in one call: where the middle is, how far values spread and where one value ranks.

Syntax

Write the function's name and a list of numbers.

AVERAGE(values)
MEDIAN(values)
MODE.SNGL(values)
MAX(values)
MIN(values)
COUNT(values)
COUNTBLANK(values)
STDEV.S(values)
STDEV.P(values)
VAR.S(values)
VAR.P(values)
QUARTILE.INC(values, quart)
PERCENTILE.INC(values, k)
LARGE(values, k)
SMALL(values, k)
GEOMEAN(values)
HARMEAN(values)
AVEDEV(values)
DEVSQ(values)
TRIMMEAN(values, proportion)
SKEW(values)
KURT(values)
MEAN(values)
MINA(values)
MODE.MULT(values)
SKEW.P(values)
GEOMEAN_ADV(values)
STDEV(values)
STDEVP(values)
STDEVA(values)
STDEVPA(values)
VAR(values)
VARP(values)
VARA(values)
VARPA(values)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • AVERAGE

    values

    Required: Yes
  • MEDIAN

    values

    Required: Yes
  • MODE.SNGL

    values

    Required: Yes
  • MAX

    values

    Required: Yes
  • MIN

    values

    Required: Yes
  • COUNT

    values

    Required: Yes
  • COUNTBLANK

    values

    Required: Yes
  • STDEV.S

    values

    Required: Yes
  • STDEV.P

    values

    Required: Yes
  • VAR.S

    values

    Required: Yes
  • VAR.P

    values

    Required: Yes
  • QUARTILE.INC

    values

    Required: Yes
  • quart

    Required: Yes
  • PERCENTILE.INC

    values

    Required: Yes
  • k

    Required: Yes
  • LARGE

    values

    Required: Yes
  • k

    Required: Yes
  • SMALL

    values

    Required: Yes
  • k

    Required: Yes
  • GEOMEAN

    values

    Required: Yes
  • HARMEAN

    values

    Required: Yes
  • AVEDEV

    values

    Required: Yes
  • DEVSQ

    values

    Required: Yes
  • TRIMMEAN

    values

    Required: Yes
  • proportion

    Required: Yes
  • SKEW

    values

    Required: Yes
  • KURT

    values

    Required: Yes
  • MEAN

    values

    Required: Yes
  • MINA

    values

    Required: Yes
  • MODE.MULT

    values

    Required: Yes
  • SKEW.P

    values

    Required: Yes
  • GEOMEAN_ADV

    values

    Required: Yes
  • STDEV

    values

    Required: Yes
  • STDEVP

    values

    Required: Yes
  • STDEVA

    values

    Required: Yes
  • STDEVPA

    values

    Required: Yes
  • VAR

    values

    Required: Yes
  • VARP

    values

    Required: Yes
  • VARA

    values

    Required: Yes
  • VARPA

    values

    Required: Yes

Examples

A list of daily sales held once, then its average, middle, highest, lowest and count in one answer.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
AVERAGEdecimalThe meanAVERAGE([1, 2, 3, 4])
MEDIANdecimalThe middle valueMEDIAN([1, 3, 2, 10])
MODE.SNGLdecimalThe most frequent valueMODE.SNGL([1, 2, 2, 3])
MAXdecimalThe largest valueMAX([1, 5, 3])
MINdecimalThe smallest valueMIN([4, 2, 9])
COUNTwhole numberHow many values are numbersCOUNT([1, 2, 3])
COUNTBLANKwhole numberHow many values are emptyCOUNTBLANK([1, null, 3])
STDEV.SdecimalThe sample standard deviationSTDEV.S([1, 2, 3, 4])
STDEV.PdecimalThe population standard deviationSTDEV.P([1, 2, 3, 4])
VAR.SdecimalThe sample varianceVAR.S([1, 2, 3, 4])
VAR.PdecimalThe population varianceVAR.P([1, 2, 3, 4])
QUARTILE.INCdecimalThe quartile, counting the ends as includedQUARTILE.INC([1, 2, 3, 4, 5], 3)
PERCENTILE.INCdecimalThe value at that fraction of the listPERCENTILE.INC([1, 2, 3, 4, 5], 0.5)
LARGEdecimalThe k-th largest valueLARGE([1, 5, 3], 2)
SMALLdecimalThe k-th smallest valueSMALL([1, 5, 3], 2)
GEOMEANdecimalThe geometric meanGEOMEAN([1, 4, 16])
HARMEANdecimalThe harmonic meanHARMEAN([1, 2, 4])
AVEDEVdecimalThe average distance from the meanAVEDEV([1, 2, 3, 4])
DEVSQdecimalThe sum of squared distances from the meanDEVSQ([1, 2, 3, 4])
TRIMMEANdecimalThe mean without the extremesTRIMMEAN([1, 2, 3, 4, 100], 0.4)
SKEWdecimalHow lopsided the values areSKEW([1, 2, 3, 10])
KURTdecimalHow heavy the tails areKURT([1, 2, 3, 4, 10])
MEANdecimalThe mean. The same as AVERAGEMEAN([1, 2, 3, 4])
MINAdecimalThe smallest value, counting text and true or falseMINA([4, 2, 9])
MODE.MULTlist of decimalsEvery value that occurs more than once, the most frequent first. A list with no repeated value is refusedMODE.MULT([1, 2, 2, 3, 3, 3])
SKEW.PdecimalHow lopsided the values are, for a whole population. Needs a spread above 0SKEW.P([1, 2, 3, 10])
GEOMEAN_ADVrecordA record with geomean, confidence (VERY_LOW under 6 values, LOW from 6, MEDIUM from 12, HIGH from 36), sample_size and log_mean. A value that is not above 0 is refusedGEOMEAN_ADV([1, 4, 16, 8, 2, 32])
STDEVdecimalThe sample standard deviation. The same as STDEV.SSTDEV([1, 2, 3, 4])
STDEVPdecimalThe population standard deviation. The same as STDEV.PSTDEVP([1, 2, 3, 4])
STDEVAdecimalThe sample standard deviation, counting text and true or falseSTDEVA([1, 2, 3, 4])
STDEVPAdecimalThe population standard deviation, counting text and true or falseSTDEVPA([1, 2, 3, 4])
VARdecimalThe sample variance. The same as VAR.SVAR([1, 2, 3, 4])
VARPdecimalThe population variance. The same as VAR.PVARP([1, 2, 3, 4])
VARAdecimalThe sample variance, counting text and true or falseVARA([1, 2, 3, 4])
VARPAdecimalThe population variance, counting text and true or falseVARPA([1, 2, 3, 4])
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001An argument is not a list of numbers, a list holds an empty value, QUARTILE.INC's quart is not 0 to 4, LARGE or SMALL is asked for a position beyond the list, or TRIMMEAN's proportion is not from 0 up to but not including 1Pass a list of numbers and values in range
HTQL-8004PERCENTILE.INC's k is outside 0 to 1Pass a fraction from 0 to 1
Limitations
  • Write the list in square brackets: AVERAGE([1, 2, 3]). AVERAGE, MAX and MIN also take separate numbers, AVERAGE(1, 2, 3); the others take one list.
  • A list with too few values for the function is refused: STDEV.S([5]) needs at least 2 values and SKEW([1, 2]) at least 3.
  • AVERAGE refuses a list that holds an empty value or text, with HTQL-2001 "Expected a numeric value, Null". Remove the gaps before you average.
  • GEOMEAN of a list holding a negative number answers 0, and AVERAGE([]) and HARMEAN([1, 0]) stop with a message beginning called Result::unwrap() on an Err value, with no error code. MAX([]) answers 0. Pass at least one value, positive for GEOMEAN and HARMEAN.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Use STDEV.S and VAR.S for a sample and STDEV.P and VAR.P for the whole population.
  • Use MEDIAN when a few extreme values would distort the mean.