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
Required: YesvaluesMEDIAN
Required: YesvaluesMODE.SNGL
Required: YesvaluesMAX
Required: YesvaluesMIN
Required: YesvaluesCOUNT
Required: YesvaluesCOUNTBLANK
Required: YesvaluesSTDEV.S
Required: YesvaluesSTDEV.P
Required: YesvaluesVAR.S
Required: YesvaluesVAR.P
Required: YesvaluesQUARTILE.INC
Required: Yesvalues
Required: YesquartPERCENTILE.INC
Required: Yesvalues
Required: YeskLARGE
Required: Yesvalues
Required: YeskSMALL
Required: Yesvalues
Required: YeskGEOMEAN
Required: YesvaluesHARMEAN
Required: YesvaluesAVEDEV
Required: YesvaluesDEVSQ
Required: YesvaluesTRIMMEAN
Required: Yesvalues
Required: YesproportionSKEW
Required: YesvaluesKURT
Required: YesvaluesMEAN
Required: YesvaluesMINA
Required: YesvaluesMODE.MULT
Required: YesvaluesSKEW.P
Required: YesvaluesGEOMEAN_ADV
Required: YesvaluesSTDEV
Required: YesvaluesSTDEVP
Required: YesvaluesSTDEVA
Required: YesvaluesSTDEVPA
Required: YesvaluesVAR
Required: YesvaluesVARP
Required: YesvaluesVARA
Required: YesvaluesVARPA
Required: Yesvalues
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
AVERAGE | decimal | The mean | AVERAGE([1, 2, 3, 4]) |
MEDIAN | decimal | The middle value | MEDIAN([1, 3, 2, 10]) |
MODE.SNGL | decimal | The most frequent value | MODE.SNGL([1, 2, 2, 3]) |
MAX | decimal | The largest value | MAX([1, 5, 3]) |
MIN | decimal | The smallest value | MIN([4, 2, 9]) |
COUNT | whole number | How many values are numbers | COUNT([1, 2, 3]) |
COUNTBLANK | whole number | How many values are empty | COUNTBLANK([1, null, 3]) |
STDEV.S | decimal | The sample standard deviation | STDEV.S([1, 2, 3, 4]) |
STDEV.P | decimal | The population standard deviation | STDEV.P([1, 2, 3, 4]) |
VAR.S | decimal | The sample variance | VAR.S([1, 2, 3, 4]) |
VAR.P | decimal | The population variance | VAR.P([1, 2, 3, 4]) |
QUARTILE.INC | decimal | The quartile, counting the ends as included | QUARTILE.INC([1, 2, 3, 4, 5], 3) |
PERCENTILE.INC | decimal | The value at that fraction of the list | PERCENTILE.INC([1, 2, 3, 4, 5], 0.5) |
LARGE | decimal | The k-th largest value | LARGE([1, 5, 3], 2) |
SMALL | decimal | The k-th smallest value | SMALL([1, 5, 3], 2) |
GEOMEAN | decimal | The geometric mean | GEOMEAN([1, 4, 16]) |
HARMEAN | decimal | The harmonic mean | HARMEAN([1, 2, 4]) |
AVEDEV | decimal | The average distance from the mean | AVEDEV([1, 2, 3, 4]) |
DEVSQ | decimal | The sum of squared distances from the mean | DEVSQ([1, 2, 3, 4]) |
TRIMMEAN | decimal | The mean without the extremes | TRIMMEAN([1, 2, 3, 4, 100], 0.4) |
SKEW | decimal | How lopsided the values are | SKEW([1, 2, 3, 10]) |
KURT | decimal | How heavy the tails are | KURT([1, 2, 3, 4, 10]) |
MEAN | decimal | The mean. The same as AVERAGE | MEAN([1, 2, 3, 4]) |
MINA | decimal | The smallest value, counting text and true or false | MINA([4, 2, 9]) |
MODE.MULT | list of decimals | Every value that occurs more than once, the most frequent first. A list with no repeated value is refused | MODE.MULT([1, 2, 2, 3, 3, 3]) |
SKEW.P | decimal | How lopsided the values are, for a whole population. Needs a spread above 0 | SKEW.P([1, 2, 3, 10]) |
GEOMEAN_ADV | record | A 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 refused | GEOMEAN_ADV([1, 4, 16, 8, 2, 32]) |
STDEV | decimal | The sample standard deviation. The same as STDEV.S | STDEV([1, 2, 3, 4]) |
STDEVP | decimal | The population standard deviation. The same as STDEV.P | STDEVP([1, 2, 3, 4]) |
STDEVA | decimal | The sample standard deviation, counting text and true or false | STDEVA([1, 2, 3, 4]) |
STDEVPA | decimal | The population standard deviation, counting text and true or false | STDEVPA([1, 2, 3, 4]) |
VAR | decimal | The sample variance. The same as VAR.S | VAR([1, 2, 3, 4]) |
VARP | decimal | The population variance. The same as VAR.P | VARP([1, 2, 3, 4]) |
VARA | decimal | The sample variance, counting text and true or false | VARA([1, 2, 3, 4]) |
VARPA | decimal | The population variance, counting text and true or false | VARPA([1, 2, 3, 4]) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | An 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 1 | Pass a list of numbers and values in range |
HTQL-8004 | PERCENTILE.INC's k is outside 0 to 1 | Pass a fraction from 0 to 1 |
Limitations
- Write the list in square brackets:
AVERAGE([1, 2, 3]).AVERAGE,MAXandMINalso 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 andSKEW([1, 2])at least 3. AVERAGErefuses a list that holds an empty value or text, withHTQL-2001"Expected a numeric value, Null". Remove the gaps before you average.GEOMEANof a list holding a negative number answers 0, andAVERAGE([])andHARMEAN([1, 0])stop with a message beginningcalled Result::unwrap() on an Err value, with no error code.MAX([])answers 0. Pass at least one value, positive forGEOMEANandHARMEAN.
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.SandVAR.Sfor a sample andSTDEV.PandVAR.Pfor the whole population. - Use MEDIAN when a few extreme values would distort the mean.