Tuesday, May 27, 2014

Combinations in Excel 2010 and Excel 2013

This is one of the following two articles on Combinations and Permutations in Excel

Combinations in Excel 2010 and Excel 2013

Permutations in Excel 2010 and Excel 2013

 

Combinations Overview

Both combinations and permutations represent all the possible ways to arrange k items that are randomly and repeatedly selected from a larger set of n items.

A single combination is an unordered collection of k unique items. One combination is different from another combination if at least one of the items in one collection is different than any of the items in all other collections. Re-arrangement of the same items in a collection does not create a new combination.

For example, from the set of four letters [ A, B, C, D ], the following four total unique three-letter combinations exist:

A B C

A B D

D B A

D B A

Different arrangements or orderings of these same k items in a subset do not create different combinations. For example, [A B C] is not a different combination than [B A C] because both collections contain the same three letters, just in a different order. The number of possible combinations is the total number of ways that k elements can be arranged into n total elements when order of the k objects DOES NOT matter.

 

Simultaneous vs. Sequential Selection

Combinations - Elements are picked simultaneously, all at once.

Permutations - Elements are picked sequentially, one after another, i.e., in a specific order.

If there is no order to the arrangement, it is a combination.

If there is any specified order to the arrangement, it is a permutation.

 

Excel Functions For Combinations

The Excel formula for the total possible number of combinations of k elements into n total elements is given by the following:

excel, excel 2010, excel 2013, statistics, combinations

excel, excel 2010, excel 2013, statistics, combinations is sometimes referred to as “n choose k.” This can be interpreted as follows: “Choose a subset of k items from a total of n items available.”

excel, excel 2010, excel 2013, statistics, combinations is called the binomial coefficient and the is source of the name of the binomial distribution. The binomial coefficient is equal to the total number of subsets of k items that can be taken from the larger set of n items if it does not matter how the subset of k items is arranged.

COMBIN(n,k) = Number of Combinations of n Objects Taken k at a Time Simultaneously

FACT ( n ) = n!

----> FACT() is a Math & Trig Excel function and not a Statistical function such as COMBIN().

For example, the number of Combinations of 9 objects taken 4 at a time simultaneously

= nPk / k!

= 9P4/ 4!

= n! / [ k! * (n - k )! ]

= 9! / [ 4! * (9 - 4 )! ]

= FACT(n) / ( FACT(k) * FACT(n - k) )

= FACT(9) / ( FACT(4) * FACT(5) )

= 126

= COMBIN (n,k)

= COMBIN (9,4)

= 126

 

Combination Problems in Excel

 

Problem 1: Combinations of Investment Proposals

A company is evaluating 6 investment proposals. If the company selects as many different groups of3 proposals simultaneously, how many different groups of three investment proposals can be selected?

This is a combination problem because the groups of three investment proposals are selected simultaneously. The only difference between each group are the items in each groups but not how the items are ordered.

n = 6 = total number of investment proposals available for inclusion in each combination group

x = 3 = number of investment proposals that will be simultaneously selected to fill each combination group

The number of combinations of n = 6 different investment proposals selected x = 3 at a time (simultaneously) equals:

nPk / k! = 6P3 / 3! = COMBIN (n,k) = COMBIN (6,3) = 20

 

Problem 2: Combinations of Newly-Opened Offices

A consultancy wants to open 4 offices in 10 northern states. Each new office will be in a different state. The offices will open all at the same time. How many different ways can these four offices be situated among the 10 possible northern states?

This is a combination problem because the groups of four different states are selected as locations simultaneously. The only difference between each group is the individual items in each group but not the ordering of the items.

n = 10 = total number of states available for inclusion in each combination group

k = 4 = number of states that will simultaneously be selected to fill each combination group

The number of combinations of n = 10 different states available to selected at k = 4 at a time (simultaneously) equals:

nPk / k! = 10P4 / 4! = COMBIN (n,k) = COMBIN (10,4) = 210

 

Problem 3: Multiple Combinations of Newly-Opened Offices

A consultancy wants to open 4 offices in 10 northern states, 3 offices in 9 southern states, and 2 offices in 8 eastern states. Each new office will be in a different state and all offices will be opened at the same time. How many different combinations does the company have to evaluate?

Total number of combinations =

(all possible combinations of northern combinations)*(all possible combinations of southern combinations)*(all possible combinations of eastern combinations)

= COMBIN(10,4) * COMBIN(9,3) * COMBIN(8,2) = 493,920

= 210 * 84 * 28 = 493,920

 

Problem 4: Combinations of Committees

From a group of 10 men and 8 women, a committee is formed. The committee will have 3 men and 4 women. How many different ways can this committee of 3 men and 4 women be formed from the overall group of 10 men and 8 women? All committee members are picked at the same time.

This is combination problem because all committee members are picked at the same time. The problem asks how many ways can all possible combinations of 3 out of 10 men be combined with all possible combinations of 4 out of 8 women?

Total number of combinations =

= (All possible combinations of men) * (All possible combinations of women)

= COMBIN(10,3) * COMBIN(8,4) = 8,400

= 120 * 70 = 8,400

 

Problem 5: Combinations of Sub-Groups

How many ways can a group of 12 people be divided into one group of 7 and another group of 5?

This is a combination problem because all members of any one group can be picked simultaneously.

One way to solve the problem would be to determine the total number of 7-person combinations that can be formed from 10 people and then multiply that number by the number of 5-person combinations that can be formed from the remaining 5 people.

Total number of combinations =

= (all possible combinations of 7 out of 12) * (all possible combinations of 5 out of remaining 5)

= COMBIN(12,7) * COMBIN(5,5) = 792

= 792 * 1 = 792

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

Posted by at

3 comments:

  1. jenny88thMay 29, 2021 at 2:28 PM

    Before you register with online casinos and start spending your money, it surely is a good idea to understand what defines an online casino. Because playing in an online casino for the first time, whether live or online, could be a bit overwhelming. And even if you’re just looking for some online fun and excitement, sòngbạc-trựctuyến.com
    it’s important to know what these websites are all about and how to get the most out of them.

    ReplyDelete
  2. shopifyMarch 14, 2022 at 4:29 AM

    our high-end Penetration testing services are unlike any other penetration testing company across Toronto, Canada

    ReplyDelete
  3. Angel17June 17, 2023 at 8:15 AM

    I find this post so useful. Keep us updated! south shore tree removal

    ReplyDelete

Subscribe to: Post Comments (Atom)