Search code examples
excelbinomial-coefficients

Provide a single Excel Formula for calculating Binomial Coefficients (N,K) in Excel with positive or negative N

Is there a single excel formula that can take integer inputs N and K and generate the binomial coefficient (N,K), for positive or negative (or zero) values of N?

The range of N and K should be fairly small e.g. -11 < N < +11 and -1 < K < +11. Otherwise large numbers will be generated that exceed excel's capabilities.

CONTEXT

Excel does not provide a Binomial function. So how how to get around this? The binomial function for positive N is straightforward:- Binomial(N,K) = Factorial(N)/(Factorial(N-K)*Factorial(K)). But this doesn't work for negative N.

For information on Binomial Coefficients there is useful stuff in Ken Ward's pages on Pascals Triangle and Extended Pascal's Triangle.

I wanted to make a similar tabular resource in Excel...but with one single table covering positive, zero and negative values of $N$.

One efficient way of doing this is to define a single formula which can be used in every cell of the table. The formula should discriminate between values of N which are negative, zero, or positive and use appropriate logic to obtain the correct output in each case.

Of course, rather than build a whole table, the same formula can be used to calculate the binomial coefficient for a single (N,K) input pair of values.

Solution

  • You can simulate a binomial function by using a conditional formula in a single Excel cell which takes as input the contents of two other cells.

    e.g. if worksheet cells A1 and A2 contain the numeric values corresponding to N,K in the binomial expression (N,K) then the following conditional formula can be put in another worksheet cell (e.g. A3)...

    =IF(A1>-1,IF(B1>A1,0,(FACT(A1)/(FACT(B1)*FACT(A1-B1)))),(-1)^(B1)*(FACT(-A1-1+B1)/(FACT(B1)*FACT(-A1-1+B1-B1))))

    This will handle positive and negative (and zero) values of N. In this solution both N and K must be integers (including zero). There is a limit to the size of N and K that excel will be able to cope with (but I havent tested the limits beyond the range -11

    The excelf formula uses the conditional construct: IF(test,operation if true, operation if false).

    In pseudo-code the logic is as follows:-

    IF(N>-1) THEN
    IF(K>N) THEN
        Result = 0
    ELSE
        Result = (N!)/(K!*(N-K)!)
    ENDIF
ELSE
    Result = (-1)^(K) * (-N-1+K)! / ( (K)! * (-N-1+K-K)! )
ENDIF

    Note the formula uses the Upper Negation Identity to determine coefficients when N is negative:-

    (-N,K) = (-1)^K * (K-N-1,K).

    Pascal's Triangle Table

    To create a "Pascal's Triangle"-type table for negative and positive values of N, proceed as follows.

    (1) Create a new blank excel worksheet.

    (2) In column B put the integer N values (starting at cell B4 and proceeding downwards):- 

    e.g Nmin,Nmin-1,...-2,-1,0,1,2,3,...,Nmax-1,Nmax.

    (3) In row 3 put the integer K values (starting at cell C3 and proceeding rightwards):-

    0,1,2,3,...Kmax.

    (4) Then in cell (C4) enter the conditional formula:-

    =IF($B4>-1,IF(C$3>$B4,0,(FACT($B4)/(FACT(C$3)*FACT($B4-C$3)))),(-1)^(C$3)*(FACT(-$B4-1+C$3)/(FACT(C$3)*FACT(-$B4-1+C$3-C$3))))

    (5) Copy cell C4 and paste it to all cells in the grid bounded (at left and at top) by your N and K values.

    The grid cells will then contain the binomial ceofficient corresponding to (N,K).