Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Created Jun 18, 2020
Embed
What would you like to do?
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment