I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds of permutations, with repetition and without repetition.

### Permutations with repetition

I explained in my last post that phone numbers are permutations because the order is important. But phone numbers may also contain duplicate numbers or repeated numbers like 11 234, here number 1 is repeated. A digit in a phone number has 10 different values, 0 to 9. A five digit phone number has 10x10x10x10x10 or 10^5 equals 100 000 permutations.

Another example with repetitive numbers are bits and bytes. A bit is a single binary number like 0 or 1. A byte is a sequence of bits and eight bits equal one byte. A byte contains 256 different permutations and repetition is allowed.

Here is how you calculate the number of permutations. There are two different values 0 and 1 (binary) and a byte has 8 binary values. 2x2x2x2x2x2x2x2 or 2^8 equals 256 permutations.

**Did you know?** The UTF-8 is a character encoding scheme using 8 bits to encode all possible characters, it is the most used encoding system on world wide web today.

**Constructing a permutations array with binary values**

Excelxor showed us how to build a permutations array using only excel functions, in one of his blog posts.

The picture to the left is an array made by excelxor's formula, this example uses four cells or 4 bits. 2x2x2x2 or 2^4 equals 16 permutations.

With this type of array excelxor finds which numbers add up to a total.

**Array formula:** =MOD(INT(( ROW( INDIRECT("1:"&2^ROWS($A$2:$A$5)))-1 )/2^( TRANSPOSE( ROW( INDIRECT("1:"&ROWS($A$2:$A$5))))-1 )), 2)

I have made a blog post a few years ago how to calculate permutations with repetition with a custom function.

### Permutations without repetition

Imagine constructing an anagram of a word "*police". *You are allowed to rearrange the letters but you can´t repeat a letter. See picture to the right.

Another example is how many times can you rearrange 6 people around a table? Most people will get annoyed if you ask them to change seat more than once but in this example you can rearrange as many times as you like. How many permutations are there? You can´t have a person on two chairs at the same time, repetition is not allowed.

Here is a link to a udf I made a few years ago: Excel udf: List permutations without repetition

**Tip! **Use the PERMUT function to calculate permutations. The word *police* has 6 letters, how many permutations without repetition are there if you choose 6 out of 6 letters?

=PERMUT(6,6) equals 720 permutations.

### Functions in this post

**MOD(***number*, *divisor***)**

The Mod function returns the remainder after a number is divided by divisor.

**INDIRECT(***ref_text*, *[a1]***)**

Returns the reference specified by a text string.

**ROW(**reference**)**

Returns the row number of a reference

Place the curser in cell A8 (your attached spreadsheet).

Right click and select from the menu: "Pick_From Drop-Down List..." - works only for text.

Rarely used, even by the pro's.

//Ola.S

http://www.pcreview.co.uk/threads/pick-from-drop-down-list-gives-empty-or-erroneous-result.4026822/

Ola.S

I am not sure I understand.

Right click and select from the menu: "Pick_From Drop-Down List..." - works only for text.To be honest, I didn't know you could. That drop down list is not the same thing as a data validation rule - drop down list. Try it yourself, numbers work fine in my first example.

The drop down list is automatically copied to the next cell below when the table grows. Example, select the last cell in the table, cell C7 and press TAB key. A new row is inserted and cell A8 has a drop down list.

There are other ways to insert new table rows. Right click on a cell and hover over "Insert". Click "Insert Table rows above" or "Insert Table rows below"

Thank you for commenting.

Thanks' Oscar for sharing this trick.

I wonder what are the advantages of using tables?

mma173,

- Sort and filter

- Sum by adding a row for total

- By entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column.

- Structured references

- Easy to reference a table or table column

- Formatting

- Easy to insert or delete table columns or rows

I got a question by email:

Is it possible to make a drop down list autocomplete if you have hundreds names instead of scrolling down into drop down list?Yes, almost. It works only if the values in the drop down list are sorted.

Example

1. Type E in the cell

2. Click "Drop down list" arrow

3. You are now on letter E in the drop down list

4. Select a value