Tuesday, May 27, 2014

Permutations 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

 

Permutations 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 permutation describes a unique ordering of those k items. For example, the combination that contains the letters A, B, and C has six possible permutations (orderings) of that one combination (unordered collection) of letters. The six total different ways that the three letters A, B, and C can be ordered (ABC, ACB, BAC, BCA, CAB, CBA) represent the six total permutations of those three items (letters) that exist.

The total number of possible permutations equals the total number of possible combinations times the total number of permutations that can be derived from each combination. The preceding example shows that the set of four letters [ A, B, C, D ] has a total of four unique three-letter combinations. Six different permutations (orderings) can be derived from each one of these four combinations. The total number of three-letter permutations that can be created from the set of four letters [ A, B, C, D ] is 24.

A permutation can have the same items as another permutation but those items must be arranged in a different order to be considered a unique permutation.

Different arrangements or orderings of the same k items in a subset DO create different permutations. The number of possible permutations is the total number of ways that all of the possible subsets of k items can be ordered differently. The number of possible combinations is the total number of ways that k elements can be arranged from n total elements when order of the k objects DOES matter.

Re-arrangement of the same k elements within a subset creates a new permutation but does not create a new combination. There are always more permutations combinations of k objects taken from n total items because each combination of k elements can be re-ordered to create k! permutations. The total number of permutations of k items equals the following:

Total number of permutations of k items = (Total number of combinations of k items) * k!

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 Permutations

The Number of Permutations equals the number of times that n different objects taken k at a time sequentially is given by the following:

clip_image001

clip_image002

PERMUT ( n, k ) = Number of Permutations of n Objects Taken k at a time Sequentially

FACT ( n ) = n!

----> FACT() is a Math & Trig Excel function, not a Statistical category function like PERMUT().

For example, the number of permutations of 9 objects taken 4 at a time sequentially =

= nPk = 9P4 = n! / (n - xk)!

= 9! / (9 - 4 )!

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

= FACT(9) / FACT(5)

= 3,024

= PERMUT (n,k)

= PERMUT (9,4)

= 3,024

The Excel formulas for combinations and permutations are verified by referring back to the original example provided in this section as follows:

From the set of four letters [ A, B, C, D ], the following four unique three-letter combinations can be created:

A B C

A B D

D B A

D B A

This agrees with the result of the following Excel formula:

C(n,k) = COMBIN(n,k) = COMBIN(4,3) = 4

Six permutations can be derived from each one of the above three-letter combinations. The number of permutations that can be derived from a combination of k items equals k!. In this case k = 3. k! = 6.

The six different ways that the three letters A, B, and C can be ordered (ABC, ACB, BAC, BCA, CAB, CBA) represent six unique permutations of those three letters.

Total number of permutations of k items = (Total number of combinations of k items) * k!

Total number of permutations = 4 * 6 = 24

This agrees with the result of the following Excel formula:

P(n,k) = PERMUT(n,k) = PERMUT(4,3) = 24

clip_image002[1]

C(n,k) = 4

P(n,k) = 24

k! = 6

Permutation Problems in Excel:

Problem 1: Permutations of Delivery Routes

A milkman makes 7 deliveries on his route. How many different sequences can he make to complete all 7 stops?

This is a permutation problem because the stops are done sequentially. After each stop, there is one less stop to make.

n = 7 = total number of objects initially available for inclusion in each permutation group

k = 7 = number of objects that will sequentially fill the permutation group

The number of permutations of n = 7 different stops taken k = 7 at a time sequentially equals:

nPk = 7P7 = PERMUT(n,k) = PERMUT(7,7) = 5,040

 

Problem 2: Permutations of Seating Arrangements

How many ways can 5 people be seated on a sofa if only 3 seats are available and the 3 seats are filled sequentially by the available 5 people?

This is a permutation problem because the elements of the permutation group are filled sequentially. When each person is seated, there is one less person available to be seated.

n = 5 = total number of objects initially available for inclusion in each permutation group

k = 3 = number of objects that will sequentially fill the permutation group

The number of permutations of n = 5 different people seated k = 3 at a time sequentially equal:

nPk = 5P3 = PERMUT(n,k) = PERMUT(5,3) = 60

 

Problem 3: Permutations of Executive Groups

A group of 9 people needs to appoint 1 person to be group president, another person to be group vice president, and a third person to be group treasurer. If the group first votes for the president, then votes for the vice president, and finally votes for the treasurer, how many different executive groups can be created from the original 9 people?

This is a permutation problem because the elements of the permutation group are filled up sequentially. When each position is filled, there is one less person available for a position.

n = 9 = total number of objects initially available for inclusion in each permutation group

k= 3 = number of objects that will sequentially fill the permutation group

The number of permutations of n = 9 different people elected k = 3 at a time sequentially equals:

nPk = 9P3 = PERMUT(n,k) = PERMUT(9,3) = 504

 

Problem 4: Permutations of Book Arrangements

How many ways can 3 books be placed next to each other on a shelf one at a time?

This is a permutation problem because the elements of the permutation group are filled sequentially. When each book is placed, there is one less book to place.

n = 3 = total number of initially objects available for inclusion in each permutation group

k = 3 = number of objects that will sequentially fill the permutation group

The number of permutations of n = 3 different books placed k = 3 at a time sequentially equals:

nPk = 3P3 = PERMUT(n,k) = PERMUT(3,3) = 6

 

Problem 5: Permutations of Letter Groups

From the following six letters: A, B, C, D, E, F, how many groups of 3 letters can be created if none of the letters from the original 6 are repeated in any group?

This is a permutation problem because none of the letters can be repeated. When the first letter of one of the permutation groups is chosen, there are only five remaining letters to choose from. Thus, the elements of the permutation group are filled sequentially.

n = 6 = total number of objects initially available for inclusion in each permutation group

k = 3 = number of objects that will sequentially fill the permutation group

The number of permutations of n = 6 different letters placed k = 3 at a time sequentially equally:

nPk = 6P3 = PERMUT(n,k) = PERMUT(6,3) = 120

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at

4 comments:

  1. My Home HandymanFebruary 25, 2021 at 2:43 AM

    Informative.

    ReplyDelete
  2. Rum TanMarch 9, 2021 at 11:03 PM

    Very useful formulas you shared here. Permutations and combination concept in mathematics is very interesting, but students find it little tricky to solve. Also, this topic is very important in many streams whether it is scientific or business mathematics. One more important aspect of your blog is that it is all about 'Excel', which is again one very powerful tool. In business, any function in any industry can benefit from those with strong Excel knowledge, whether for analyzing stocks or issuers, budgeting, or organizing client sales lists, or any kind of analysis of data. Thanks for sharing such a beneficial post.

    ReplyDelete
  3. shopifyMarch 14, 2022 at 4:44 AM

    shopify app development company We identify the strongest language of communication and its placement to help you build trust and increase conversions.

    ReplyDelete
  4. Angel17June 17, 2023 at 8:19 AM

    What an interesting post. This is so helpful. roselle car accident attorneys

    ReplyDelete

Subscribe to: Post Comments (Atom)