Number.Sqrt
Returns the square root of a number.
Number.Sqrt(number as nullable number) as nullable numberReturns: The square root, or null when the input is null. An error for a negative input.
What it does
Number.Sqrt returns the square root of a number. In finance it appears in standard deviation, volatility and some distance measures. A variance report that shows the spread of monthly results uses a square root to turn the variance into a standard deviation, which is in the same units as the amounts.
Syntax and arguments
- number: the value to take the square root of. It must not be negative.
Example
A controller wants the typical size of monthly swings in a cost line. The variance of the monthly amounts is calculated, and the square root gives the standard deviation.
let
Monthly = {100, 110, 90, 120, 80},
Average = List.Average(Monthly),
Squares = List.Transform(Monthly, each Number.Power(_ - Average, 2)),
Variance = List.Sum(Squares) / (List.Count(Monthly) - 1),
StdDev = Number.Sqrt(Variance)
in
StdDev
The average is 100, the sample variance is 250, and the standard deviation is about 15.8. The result is the typical distance of a month from the average, in dollars.
Example: a second, different use
Take the square root of a measure to scale a risk score, such as the standard deviation of a customer's payment days.
Things to watch
- A negative input is an error. Check for it before you take the root.
- A square root turns a variance into a standard deviation, so the units change. Check the label on the report.
- Use the sample variance (divide by count minus one) when the data is a sample, and the population version only when you have every month.
- Null in gives null out.
In business terms
Volatility, standard deviation and the spread of monthly results all start with a square root. A finance team that shows the spread with the average gives the reader a fair sense of how stable a line really is.