List.StandardDeviation
Returns a standard deviation, a measure of how far the numbers in a list spread out from their average.
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
list: the numbers to measure. Remove nulls first.
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
- A single extreme value inflates the standard deviation, and it can hide itself. Compare with List.Median if you suspect outliers.
- The rule of two standard deviations is a convention, not a law. Agree the threshold with the reviewer.
- Check how Microsoft's current M reference defines the formula before you cite the figure in a memo, so that it matches the method your team uses elsewhere.
- Use it on like-for-like numbers. Mixing a seasonal month with a normal month makes the spread look large for the wrong reason.
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.