EXCEL FUNCTIONS-COMBINA

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.

EXCEL:COMBINA FUNCTION ANIMATED EXAMPLE

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.