Power Platform Community Forum Thread Details

Hi,

I need formula in PowerApps, which calculates Inverse of standard normal cumulative distribution, given a probability.

In Excel there is a formula NORMSINV, but nothing of this type in PowerApps.

tried using -

    -Sqrt(-2 * Ln(p)) * (2.515517 + 0.802853 * Sqrt(-2 * Ln(p)) + 0.010328 * (-2 * Ln(p))) / (
        1 + 1.432788 * Sqrt(-2 * Ln(p)) + 0.189269 * (-2 * Ln(p)) + 0.001308 * (-2 * Ln(p))^(3/2))
 
where p is the probability value, but the output does not match with the Excel output.
Please help. An Excel file is attached for reference.
 
Regards

Categories:

Power Query

@Sam70 Hi,

You can try this:

this should be from Peter J. Acklam’s algorithm.

/* Put this in, say, the Text property of a Label (or wherever you need it).
   Replace with your probability value (0<p<1).

With(
    {
        p: ProbabilityValue, //you can pass for example value from TextInput
        p_low: 0.02425,              /* split-points */
        p_high: 0.97575,

        /* coefficients for central region */
        a1: -3.969683028665376E+01,  a2:  2.209460984245205E+02,
        a3: -2.759285104469687E+02,  a4:  1.383577518672690E+02,
        a5: -3.066479806614716E+01,  a6:  2.506628277459239E+00,
        b1: -5.447609879822406E+01,  b2:  1.615858368580409E+02,
        b3: -1.556989798598866E+02,  b4:  6.680131188771972E+01,
        b5: -1.328068155288572E+01,

        /* coefficients for tails */
        c1: -7.784894002430293E-03, c2: -3.223964580411365E-01,
        c3: -2.400758277161838E+00, c4: -2.549732539343734E+00,
        c5:  4.374664141464968E+00, c6:  2.938163982698783E+00,
        d1:  7.784695709041462E-03, d2:  3.224671290700398E-01,
        d3:  2.445134137142996E+00, d4:  3.754408661907416E+00
    },
    /* guard against p=0 or 1 */
    If(
        p <= 0,
        -1e10,
        If(
            p >= 1,
            1e10,

            /* now the 3 regions */
            If(
                p < p_low,
                /* lower tail */
                With(
                    { q: Sqrt(-2 * Ln(p)) },
                    -(
                        (((( (c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6)
                        /
                        (((( (d1 * q + d2) * q + d3) * q + d4) * q + 1))
                    )
                ),

                If(
                    p <= p_high,
                    /* central region */
                    With(
                        { q: p - 0.5, r: (p - 0.5) * (p - 0.5) },
                        (
                            (((( (a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6)
                            * q
                        )
                        /
                        (
                            (((( (b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)
                        )
                    ),

                    /* upper tail */
                    With(
                        { q: Sqrt(-2 * Ln(1 - p)) },
                        (
                            (((( (c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6)
                            /
                            (((( (d1 * q + d2) * q + d3) * q + d4) * q + 1))
                        )
                    )
                )
            )
        )
    )
)