INTRODUCTION
COMBINA function comes under the MATH AND TRIG category in Excel.
The COMBINA function returns the number of possible combinations with the specified number of items with repetitions.
COMBINA function performs the calculation based on the COMBINATION concept of MATHEMATICS.
LET US UNDERSTAND THE COMBINATION CONCEPT FIRST.
Suppose , there are three different persons and we need to create a group of two persons.
Now if we need to find out ,in how many ways two out of three persons can be chosen to make a group of two.
Suppose there are three persons
A,B and C.
So different groups can be
AB
AC
BC
i.e. There can be three groups.
When we include the repetition also, AB and BA will be treated as two different cases.
In Mathematics the direct formula for the same calculation is
(n+r-1)! / (r! x (n-1)!) where n is the number of items. r is the size of group.
where n!=nx n-1 x n-2 x n-3x ….1
Now , let us put the above mentioned example into the formula.
In the above example
n=3
r=2
so Number of combinations= 4!/2!2!
=12/2
=6
which is exactly same result what we got when we tried to calculate the same manually.
PURPOSE OF COMBINA FUNCTION IN EXCEL
COMBINA function returns the number of possible combinations with the specified number of items including the repetition.
PREREQUISITES TO LEARN COMBINA
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the financial terms is an advantage for the use of such formulas.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX: COMBINA FUNCTION
The Syntax for the function is
=COMBINA(NUMBER OF ITEMS, NUMBER OF ITEMS IN SUBGROUP)
NUMBER OF ITEMS Number of total items
NUMBER OF ITEMS IN SUBGROUP Number of items which are to be chosen from the total items
EXAMPLE: COMBINA FUNCTION IN EXCEL
DATA SAMPLE
We will try to find out the different combination possible for two cases involving 3 numbers and 4 numbers and selecting
2 numbers from the group.
The analysis is done manually as well as using the COMBINA function.
STEPS TO USE COMBINA FUNCTION
To demonstrate the usage of the function in different ways, we have taken two examples which have 3 and 4 numbers respectively.
The groups are to be formed of 2 numbers each.
In manual examples, the grouping has been done manually and the groups are shown in the rows.
The same groups are put for testing under the function COMBINA.
The function used is
=COMBINA(3,2) and =COMBINA(4,2).
First case has 3 numbers and 2 is the size of the group.
Second case has 4 numbers and 2 is the size of the group.
which gives the same result as the manual cases.
KNOWLEDGE BYTES
COMBINATION USING COMBINA FUNCTION
PERMUTATION AND COMBINATION , you must be acquainted with this word in higher mathematics.
We calculate the permutations for the different possible arrangements of the given number of items. It is calculated by
(n+r-1)! / (r! x (n-1)!)
where n!= n x n-1 x n-2 x…..1
So, when we need to make groups, we need not take into consideration, the number of permutations between the same number of selection as AB and BA are same when we talk about combination.