The Excel NORM.INV function hasn’t made it into the standard library of M functions for Power Query yet. So here I’m sharing a custom function that replicates it.
Excel NORM.INV function in Power Query
The Excel NORM.INV function returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. So unlike the NORM.DIST function, that returns the probability of a threshold value to occur under the normal distribution (in CDF mode), this function returns the threshold value that matches a given probability.
Again, the parameters of this function fully match the Excel function syntax. Unfortunately I still don’t know how Excel does the exact calculation for it, so I’m using another approximation that I’ve found on the web:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let func = | |
(probability as number, mean as number, standard_dev as number) as number => | |
let | |
// Source for NormalCDFInverse: https://www.johndcook.com/blog/normal_cdf_inverse/ | |
//StdDev = 1.5, | |
//Mean = 2, | |
p = probability, | |
RationalApproximation = (t as number) => | |
[c = {2.515517, 0.802853, 0.010328}, d = {1.432788, 0.189269, 0.001308}, return | |
= t – ((c{2} * t + c{1}) * t + c{0}) / | |
(((d{2} * t + d{1}) * t + d{0}) * t + 1)][return], | |
NormalCDFInverse = if (p < 0.5) | |
then – RationalApproximation(Number.Sqrt(- 2 * Number.Log(p))) | |
else RationalApproximation(Number.Sqrt(- 2 * Number.Log(1 – p))), | |
DenormCDFInverse = NormalCDFInverse * standard_dev + mean | |
in | |
DenormCDFInverse , | |
documentation = [ | |
Documentation.Name = " Xls.NORMINV.pq ", | |
Documentation.Description = " Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. ", | |
Documentation.LongDescription = " Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. https://support.microsoft.com/en-us/office/norm-inv-function-54b30935-fee7-493c-bedb-2278a9db7e13 ", | |
Documentation.Category = " Xls.Statistical ", | |
Documentation.Source = " www.TheBIccountant.com ", | |
Documentation.Version = " 1.0 ", | |
Documentation.Author = " Imke Feldmann ", | |
Documentation.Examples = {[Description = " ", | |
Code = " ", | |
Result = " "]}] | |
in | |
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
How to use the NORM.INV function
The syntax for the NORM.INV function in Power Query is identical to the Excel syntax:
- probability under the normal distribution
- mean is the arithmetic mean of the distribution
- standard_dev holds the Standard Deviation of the distibution
Alternatives
If you want to use this function to generate random numbers along the normal distribution curve, I also recommend to check out Sandeep Pavars version here.
Sample File
Please check out the sample file: ExcelNORMINV_Sample.xlsx
Enjoy and stay queryious 😉
Pingback: Implementing NORM.INV in Power Query – Curated SQL