Search code examples
excelexcel-formulaworksheet-function

How to generate all arrangements of two values in 5 columns using Excel?

I have two values: 1 and 0. And I have 5 columns. I need to generate all possible arrangements in Excel.

For example, I have 2 columns and 2 values: 0 , 1. There are only 4 possible arrangements (with repetitions):

1 | 0
0 | 1
0 | 0
1 | 1

I need to generate all posible arrangements of 1 and 0 for 5 columns. Number of possible arrangements with repetition is defined by formula: n^k. So, for 5 columns and 2 values it is 2^5 = 32 arrangements.

In Excel:

enter image description here

and so on

Is it possible to automate it without typing ones and zeros manually?

Solution

  • You basically want to count from 0 to 31 in binary and then split the binary result out over the columns. You can do it like this:

    enter image description here

    • Column A - just the number i.e. 0, 1, 2, 3, 4
    • Column B - =DEC2BIN(A2,5)
    • Columns C to G - =MID($B2,C$1,1) and then drag down and across

    For example - for the formula to get the binary digit in the correct column:

    enter image description here