How to List all Combinations with the COMBINAT Function in Excel (Example)

Excel has a wide variety of functions to obtain results that are very useful in the field of mathematics and finance to mention only two categories. For this reason, we recommend you install Microsoft Office and read this article with which you will learn How to list all combinations with COMBINAT function in Excel

What is a combination?

In probability, a combination is a counting technique used to get the number of groups of elements which can be taken from a set. It is important to note that the elements are not repeated, the order does not matter and numerical values ​​or characters can be used and is expressed by the formula C n, k = n! / (nk)! * k!

The COMBINAT function in Excel

The COMBINAT function is an Excel formula that allows you to obtain the number of combinations for a set of items. In this way, the COMBINAT function in Excel provides the total of groups that can be achieved from a specific number of items.

microsoft excel workspace

The COMBINAT function in Excel only supports numeric values ​​and if text is included in the cells that will be the arguments of the formula, a truncated result will be obtained and the output #VALUE! If any of the arguments contain a value less than 0, the function will return the #NUM! Error.

How to list all combinations with COMBINAT function in Excel

If you want to list all the combinations with the COMBINAT function in Excel it is necessary know the syntax which requires this formula and apply an additional procedure in case the group list includes text instead of numeric values.

What is the syntax of the COMBINAT function in Excel?

The COMBINAT function syntax in Excel includes two arguments. The first argument refers to the total number of elements and the second refers to the number of elements allowed in each combination. Both arguments are required and they are expressed in this way: = COMBINAT (number, size)

List the number of possible combinations with the COMBINAT function in Excel

To list the number of possible combinations using the COMBINAT function in Excel you must insert a list of items which will be used as a range of cells. Suppose you have a list of eight students and you want to group them in pairs. To see the list of all possible combinations we will need the COMBINAT function.

Write the name of each student from A1 to A8. To do this you can create a pivot table. Access the formula bar and select the category “Mathematics and trigonometry”. Locate the COMBINAT function and press “OK.” In “Number” press the red arrow icon to select the range A1: A8 and in “Size” type 2 and press “OK”.

List all combinations with COMBINAT function in Excel

If you need to list all the combinations with the COMBINAT function in Excel you need to create a macro in order to run the procedure. To do this, divide the names into two columns (A and B) and press “Alt + F11”. This way you will access the Visual Basic editor. Then, click on “Insert” and on “Module”.

office worker

There, paste the following lines of code so that you can visualize the combinations (You must bear in mind that the points at the beginning of each line do not go in the code):

  • Sub combines ()
  • Dim i As Integer, j As Integer, LastRA As Integer, LastRB As Integer, Aux As Integer
  • Aux = 1
  • LastRA = Range (“A4”). End (xlUp) .Row
  • LastRB = Range (“B4”). End (xlUp) .Row
  • For i = 1 To LastRA
  • For j = 1 To LastRB
  • Range (“C” & Aux) = Range (“A” & i) & Range (“B” & j)
  • Aux = Aux + 1
  • Next j
  • Next i
  • End Sub

How the macro works to perform the merge

For the combination to be carried out, you must indicate that out of a total “n” elements (8 names of students) it is possible get groups of elements without repetition and without taking into account the order in which they appear.

When selecting the range or the set of elements, column “C” will obtain the possible combinations by means of a two-loop tour for columns A and B. It is convenient to highlight that if you decided to download and install LibreOffice on your computer, you will not be able to develop the macro , since this software is not compatible with Visual Basic Applications.

Source by

How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

I'm sorry this content was not useful for you!

Let me improve this content!

Tell me, how can I improve this content?

Deja un comentario