Videos
How are binomial coefficient and Pascal's triangle related?
The binomial coefficient and Pascal's triangle are intimately related, as you can find every binomial coefficient solution in Pascal's triangle, and can construct Pascal's triangle from the binomial coefficient formula. For n choose k, visit the n plus 1-th row of the triangle and find the number at the k-th position for your solution.
Why not use factorials directly?
What is the a choose b formula?
The a choose b formula is the same as the binomial coefficient formula – it is the factorial of a divided by the product of the factorial of b and the factorial of a minus b. It is also known as the n choose k formula and can also be solved using Pascal's triangle.
For anyone else who ends up here through google, Excel actually does have COMBIN for N >= K >= 0. If you know the inputs would otherwise be valid, one option for handling K > N would be IFERROR(COMBIN(N, K), 0), with the advantage that you only specify N and K once, and the disadvantage of hiding when your assumptions inevitably turn out to be wrong.
To bring it around to an actual answer (though honestly I'd have preferred just leaving a comment if the site would let me), the other answer's formula can then be simplified to
IF(A1>-1,IF(B1>A1,0,COMBIN(A1,B1)),(-1)^(B1)*COMBIN(-A1-1+B1,B1))
as a bonus, it seems to be able to handle a larger range of inputs, as however COMBIN is implemented avoids the issue of the FACTs temporarily exceeding 1.8e308 even though much of that would be cancelled out in the division.
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).