Excel combination function Combin

Source: Internet
Author: User
Tags numeric

The syntax of Combin combinatorial functions

function function: Calculates the number of combinations that extract several objects from a given number of object collections.

Function Combin can be used to determine all possible combinations of a group of objects.

Grammar

COMBIN (Number,number_chosen)

Number is the total quantity of the object.

Number chosen is the quantity of objects in each combination.

Description

Numeric parameter truncated rounding.

If the parameter is Non-numeric, the function COMBIN returns an error value #VALUE!. If number < 0, Number_chosen <0 or number < Number_chosen,combin returns the error value #NUM!. An object combination is any set or subset of an object's entirety, regardless of its internal order. combinations are different from permutations, and the number of permutations is related to the internal order of objects. The formula is calculated as follows: Number = N, Number_chosen = k.

Second, Combin function example

For example, to choose from 6 players, 2 players to participate in the competition, then there are a total of several combinations, that is, the total of several possible?

We can use the function formula: =combin (6,2) can get the number of combinations, function return 15;

In other words, from 6 players, two players were selected to participate in the competition, the total of 15 kinds of matching.

Next, let's say a simple example:

There are three balls, respectively, red, green, blue, now from these three balls to choose two balls, there are several combinations, combination?

Use the function to get: =combin (3,2) function returns the result is 3;

Note that the Combin function only returns the total number of combinations, and does not give a specific combination of exactly what, below, we manually give a combination of three balls of the scheme, respectively:

1, red, Green 2, Red, Blue 3, green and blue on these three kinds.

Iii. knowledge expansion in Combin

If you want to know or get every combination of combinations, use VBA, and here are a few codes for your reference.

Combination of ①combin (6,2)

Sub Listcombin ()

Dim x, y as Integer

For x = 1 to 5

For y = x + 1 to 6

ACTIVECELL.FORMULAR1C1 = x & "," & Y

ActiveCell.Offset (1, 0). Select

Next y

Next x

End Sub

Combination of ②combin (8,6)

Sub Listcombin ()

Dim h, I, J, K, L, M as Integer

For h = 1 to 3

For i = H + 1 to 4

For j = i + 1 to 5

For k = j + 1 to 6

For L = k + 1 to 7

For m = l + 1 to 8

ACTIVECELL.FORMULAR1C1 = h & "-" & I & "-" & J & "-" & K & "-" & L & "-" & M

ActiveCell.Offset (1, 0). Select

Next m

Next L

Next K

Next J

Next I

Next h

End Sub

Related Keywords:
Related Article E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD