Greg_Deckler's avatar
Greg_Deckler
Community Champion
1 year ago

BINOM.INV Approximation

Been a while since I published a Quick Measure. Here is one that is an update to my previously published BINOM.INV. The problem with that version is that it relied on the FACT function which tops out at 170. So, for populations larger than 170 use this one which is an extremely accurate "approximation".

 

BINOM_INV_APPROX = 
    VAR __n = [n Value]
    VAR __Alpha = [Alpha Value]
    VAR __p = [p Value]
    VAR __pi = PI()

    -- Stirling's approximation for ln(n!) defined inline
    VAR __LnFactorial = 
        ADDCOLUMNS(
            GENERATESERIES( 0, __n ),
            "ln_n!",
                    VAR __k = [Value]
                    VAR __Result = 
                        IF(
                            __k = 0,
                            0,
                            __k * LN( __k ) - __k + 0.5 * LN( 2 * __pi * __k )
                        )
                RETURN
                    __Result
        )

    -- Join ln(n!), ln(k!), ln(n-k!) for each k
    VAR __Table =
        ADDCOLUMNS(
            __LnFactorial,
            "ln_k!", 
                MAXX( FILTER( __LnFactorial, [Value] = EARLIER( [Value] ) ), [ln_n!] ),
            "ln_n-k!", 
                MAXX( FILTER( __LnFactorial, [Value] = __n - EARLIER( [Value] ) ), [ln_n!] ),
            "LogB",
                VAR __k = [Value]
                VAR __ln_n_fact = __n * LN(__n) - __n + 0.5 * LN(2 * __pi * __n)
                VAR __ln_k_fact = IF( __k = 0, 0, __k * LN( __k ) - __k + 0.5 * LN( 2 * __pi * __k ) )
                VAR __ln_nk_fact = 
                        VAR __nk = __n - __k
                        VAR __Result = IF( __nk = 0, 0, __nk * LN( __nk ) - __nk + 0.5 * LN( 2 * __pi * __nk ) )
                    RETURN 
                        __Result
                VAR __Result = 
                    __ln_n_fact - __ln_k_fact - __ln_nk_fact
                    + __k * LN( __p )
                    + ( __n - __k ) * LN( 1 - __p )
                RETURN 
                    __Result
        )

    VAR __TableWithProb =
        ADDCOLUMNS(
            __Table,
            "B", EXP( [LogB] )
        )

    VAR __CumulativeTable =
        ADDCOLUMNS(
            __TableWithProb,
            "Cumulative",
                SUMX(
                    FILTER( __TableWithProb, [Value] <= EARLIER( [Value] ) ),
                    [B]
                )
        )
    VAR __Result = 
        MINX(
            FILTER(__CumulativeTable, [Cumulative] >= __Alpha),
            [Value]
        )
RETURN
    __Result

 

Mathematical