Downloads
Power Query MList: summarizeAdvanced

List.StandardDeviation

Returns a standard deviation, a measure of how far the numbers in a list spread out from their average.

Syntax
List.StandardDeviation(list)

Returns: A number. A larger value means the items vary more.

What it does

A standard deviation measures how much the values in a list vary around their average. A small figure means the values sit close to the average; a large figure means they are spread out. Finance teams use it to see whether a month's spending is unusual compared with the others, or how steady a cash balance has been.

Syntax and arguments

Example

A cost center has twelve monthly utility bills. The controller wants to flag any month that is far from the usual level.

let
    Monthly = {4100, 4250, 3980, 4120, 4300, 4050, 4180, 4220, 4010, 4150, 4290, 9800},
    Average = List.Average(Monthly),
    Spread = List.StandardDeviation(Monthly),
    Flagged = List.Select(Monthly, each _ > Average + 2 * Spread)
in
    Flagged

The last month, 9,800, sits well above the rest, so it is the item that stands out. Using two standard deviations above the average is a common rule of thumb for "worth a look". It is a prompt for review, not proof of an error.

Example: a second, different use

Measuring how steady monthly cash balances have been, as one input to a review of the minimum cash policy.

Things to watch

In business terms

Variance analysis, unusual expense detection, cash volatility and control limits on recurring accruals use this measure. Keep the threshold and the method in the close notes so a reviewer can repeat the test.

Related functions

All Power Query M functions · View as Markdown · M basics