How to Use the COMBIN and COMBINA Functions in Microsoft Excel to Calculate Combinations

In probability theory, financial risk modeling, and operational research, determining the total number of ways to select a subset of items from a larger pool is a foundational problem. Unlike permutations—where the sequence or order of selection matters (such as first, second, and third place in a race)—combinations evaluate groups where the order does not matter.

For instance, picking a committee of three employees from a department of ten produces the same committee regardless of the order names are drawn. Combinations divide into two mathematical models: selections without repetition (each item chosen at most once) and selections with repetition (items can be picked multiple times, such as scooping three ice cream flavours from ten choices where you can pick chocolate twice). Microsoft Excel handles both scenarios effortlessly using the COMBIN and COMBINA functions. This tutorial explores their mathematical formulae, spreadsheet syntax, real-world examples, and common error conditions.

COMBIN vs. COMBINA: Mathematical Foundations

Both functions take two arguments: the total pool size (n) and the number of items selected (k):

Function	Model	Repetition Allowed?	Mathematical Formula
COMBIN(n, k)	Combinations without repetition	No	n! / (k! × (n − k)!) (also called “n choose k”)
COMBINA(n, k)	Combinations with repetition	Yes	(n + k − 1)! / (k! × (n − 1)!)

1. Using the COMBIN Function (Without Repetition)

The COMBIN function evaluates standard combinations where elements cannot be reused:

=COMBIN(number, number_chosen)

• number (Required): Total number of distinct items in the pool (n). Must be an integer ≥ 0.
• number_chosen (Required): Number of items to select in each combination (k). Must be an integer between 0 and number.

Practical Example: Committee Formation and Lottery Odds

Suppose you need to select a 4-person project review board from a team of 12 eligible specialists:

=COMBIN(12, 4)

Excel evaluates 12! / (4! × 8!) = 495 distinct possible committee teams.

Similarly, calculating the total possible combinations in a standard lottery drawing 6 numbers from a pool of 49 numbers:

=COMBIN(49, 6)

Result: 13,983,816 possible combinations, indicating a 1 in 13.98 million probability of winning the jackpot with a single ticket.

2. Using the COMBINA Function (With Repetition)

Introduced in modern Excel versions, COMBINA calculates combinations where items can be selected repeatedly across choices:

=COMBINA(number, number_chosen)

• number (Required): Total number of available item types (n). Must be an integer ≥ 0.
• number_chosen (Required): Number of items to choose (k). Must be an integer ≥ 0. Unlike COMBIN, number_chosen can be greater than number because items can be picked multiple times.

Practical Example: Product Bundles and Multi-Pack Selections

Imagine a bakery offering 6 varieties of gourmet doughnuts. A customer purchases a box of 4 doughnuts and can choose any mix of flavours, including multiples of the same variety (e.g. 4 glazed, or 2 glazed and 2 chocolate):

=COMBINA(6, 4)

Excel evaluates (6 + 4 - 1)! / (4! × (6 - 1)!) = 9! / (4! × 5!) = 126 unique box configurations.

Summary Table: Permutations vs. Combinations in Excel

To avoid choosing the wrong formula in statistical modeling, refer to this selection matrix:

Order Matters?	Repetition Allowed?	Appropriate Excel Formula
Yes (Order matters)	No	=PERMUT(n, k)
Yes (Order matters)	Yes	=PERMUTATIONA(n, k)
No (Order irrelevant)	No	=COMBIN(n, k)
No (Order irrelevant)	Yes	=COMBINA(n, k)

Common Errors and Troubleshooting

When working with large factorial expansions, watch for these common errors:

• #NUM! Error in COMBIN: Occurs if number < 0 or number_chosen < 0, or if number_chosen > number. You cannot choose 7 unique items from a set of 5.
• #VALUE! Error: Occurs when either parameter contains non-numeric text, symbols, or space strings.
• Floating-Point Limits: Excel computes numbers up to approximately 1.79 × 10^308. If evaluating astronomical numbers (e.g. COMBIN(10000, 5000)), the factorial expansion exceeds floating-point storage and returns #NUM!.
• Non-Integer Arguments: Decimal numbers are automatically truncated to integers (e.g. =COMBIN(7.9, 3.2) evaluates as =COMBIN(7, 3)).