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 5 comments:

Monday, January 9, 2012

Looking Up a Quantity Discount In a Distant Excel Table With VLOOKUP

This is one of the following two articles on VLOOKUP in Excel

VLOOKUP - Just Like Looking Up a Number in a Telephone Book

VLOOKUP To Look Up a Discount in a Distant Database

 

Looking Up a Quantity

Discount In a Distant Excel

Table With VLOOKUP

More often than not, VLOOKUP is used to look up values in a distant Excel table.

For example, a frequent application of VLOOKUP is to obtain a quantity discount from a table located on a distant part of the same spreadsheet. This brief training session will show exactly how to do that. Directly below is a completed example of using VLOOKUP to locate a quantity discount from a distant table:



In this case, VLOOKUP correctly pulls up a quantity discount of 10% for a quantity ordered of 1035. The quantity of 1035 ordered was input by the user.

3 equivalent VLOOKUP commands are shown in the spreadsheet. Each produced the correct Quantity Discount of 10% for a Quantity Ordered of 1035 units. The difference between each of the 3 VLOOKUP commands is the way that the location of the table was specified. 2 of these methods will work in every situation. One of the methods will not work in every situation and should not be used.

To understand how this VLOOKUP function worked, let's first review the inputs for VLOOKUP:


VLOOKUP Inputs

The VLOOKUP function has 4 inputs and follows this format:

VLOOKUP ( Lookup_value, Table_array, Col_Index_num, Range_lookup )

Lookup_value: This is a user-input value for Quantity Ordered and is what will be initially looked up the 1st (leftmost) column of the distant table.

Col_Index_num: Indicates the column number (2) in which VLOOKUP will seek the Quantity Discount that is associated with the user-input Quantity Ordered.

Range_lookup: FALSE if requiring an exact match in 1st column, TRUE or omitted if requiring a similar, but not exact, value in the 1st column. In this case, TRUE will be specified because the quantity ordered might not match the exact increments in the Quantity Ordered column.

Table_array: Location of the distant table is between cells A11 and B17. Table_array location can be specified in 3 ways. These 3 methods are illustrated in the spreadsheet with blue highlighting. They are as follows:

Absolute Addresses have $ placed in front of each row letter and column number. This method will work every time.

Named Ranges will work every time and are created in Excel 2010 by:

1) Highlighting all cells to be included in the range,

2) Formulas / Define Name and then type in the new name of the cell range.

Relative Addresses list an address on the spreadsheet only relative to the current cell. Any changes to the spreadsheet which shift the location of the current cell could cause a Relative Address to point to an incorrect location. This method will not always work.

Tip Of The Day – Always Specify the Table_array location With An Absolute Address or a Named Range. Don’t Use a Relative Address. An Absolute Address or a Named Range will ensure that the location for Table_array will be correct every time. A Relative Address will produce an incorrect result as soon you move anything around on your spreadsheet.

Note that when only a similar match is needed and Range_lookup is therefore set to "TRUE" (the case here), VLOOKUP will select the first lower Quantity Discount if the Quantity Ordered is in between increments listed on the table.

The Quantity Ordered in this case was 1035. Since there is no specific Quantity Discount for the purchase of exactly 1035 units, VLOOKUP matched the 1035 units purchased with the next lower listed increment of Quantity Ordered, which is 1000. The Quantity Discount of 10% is the correct discount for any Quantity Ordered between 1000 and 2000.

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 6 comments:
Labels:

Wednesday, January 4, 2012

VLOOKUP - Just Like looking a Number Up In a Telephone Book

This is one of the following two articles on VLOOKUP in Excel

VLOOKUP - Just Like Looking Up a Number in a Telephone Book

VLOOKUP To Look Up a Discount in a Distant Database

 

VLOOKUP - Just Like

Looking a Number Up In a

Telephone Book

Using VLOOKUP is just like looking a number up in a telephone book. You first find the name and then find the telephone number on the same row as the name, but in a column to the right of the name.

First, a Few Basics About VLOOKUP

VLOOKUP works inside an Excel table. You specify a value occurring in a cell in the leftmost column (in this example, a person's name in the left column of a telephone book page) and VLOOKUP finds an associated piece of data to the right of that value (in this case, the telephone number directly to the right of that name).
The VLOOKUP function has 4 inputs and follows this format:

VLOOKUP ( Lookup_value, Table_array, Col_Index_num, Range_lookup )


Each of these 4 inputs can be explained as follows:


Lookup_value: The value to be found in the 1st (leftmost) column of the table. In the telephone look-up example below, the Lookup_value would reference the name to be initially looked up. As soon as you open up the telephone book, you look up the name first. That is the Lookup_value for our example below.

Table_array: Location of the table.

Col_Index_num: The column number of the table from which VLOOKUP will return the value. In the telephone look-up example below, Col_Index_num would be the number of the column which holds the telephone number that VLOOKUP is locating. Since our data table only has 2 columns of data, the telephone number would be in the 2nd (rightmost) column.

Range_lookup: FALSE if requiring an exact match in 1st column, TRUE or omitted if requiring a similar value in the 1st column. When looking up a specific item, such as a telephone number, an exact match is usually required and the Range_lookup will be set to FALSE in this case.

Let's go right into our example which shows VLOOKUP being used to look up a telephone number, just as you would do in a telephone book.


Using VLOOKUP to Locate a Telephone Number

Below is the spreadsheet representing a page of a telephone book. VLOOKUP looks up the telephone number in the same way that you would: locate the name and then look to the correct column on the right to get the telephone number.

In this case, VLOOKUP is locating the telephone number of Andre Johnson. The two equivalent VLOOKUP commands used below produce the same correct result (760-4829). The only difference between the two commands is how each looks up Andre Johnson's name.

The first VLOOKUP command looks up cell A4, which contains Andre Johnson's name. The second VLOOKUP command searches for "Andre Johnson" in the first (leftmost) column of the table.

Using VLOOKUP in Excel Is Just

Like Looking Up a Telephone

Number In a Phone Book


Note in the Telephone Book Lookup example above that the two VLOOKUP commands located the same telephone number (760-4829):


=VLOOKUP(A4,A2:B5,2,FALSE)

=VLOOKUP("Andre Johnson",A2:B5,2,FALSE)

Here is an explanation of the inputs for the VLOOKUP command as they pertain to this example:

Lookup_value: The value to be found in the 1st (leftmost) column of the table. We are looking up the value “Andre Johnson” in cell A4.

Table_array: Location of the table. The table is located between cells A2 and B5.

Col_Index_num: The column number of the table from which VLOOKUP will return the value. VLOOKUP will locate the telephone number in column 2 directly to the right of “Andre Johnson” in cell A4.

Range_lookup: FALSE if requiring an exact match in 1st column, TRUE or omitted if requiring a similar value in the 1st column. We do require an exact match of “Andre Johnson” in the 1st column so we set Range_lookup to FALSE.

VLOOKUP works just like looking a phone number in the phone book. Locate the name first, and then the phone number to the right in a specified column, on the same row as the name.In fact, the telephone lookup is probably the simplest and most intuitive use of VLOOKUP. Let’s provide an example of using VLOOKUP to do exactly that.

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 3 comments:
Labels: , ,
Subscribe to: Posts (Atom)