How to create a table with all the combinations of 0 and 1

In Excel, I need to generate a table with all the combinations of 1 and 0 for 12 spaces.

1 1 1 1 1 1 1 1 1 1 1 1

0 1 1 1 1 1 1 1 1 1 1 1

0 0 1 1 1 1 1 1 1 1 1 1

0 0 0 1 1 1 1 1 1 1 1 1

and so on and so forth, getting all the combinations such as

0 1 0 1 0 1 0 1 0 1 0 1

How can I do this?

Solution 1:

Because 2^12=4096, you need 4096 cells (12 for your 12 bits).

In principle, you put into A1 to A4096 this command:

=Right("00000000000" & Dec2Bin(Row()-1),12)

That would be it, but it works only for 0...511 (9 bits). So we apply a trick: we split the number into a 3 bits and a 9 bits part and calculate the two strings separately, then concatenate them.

Hence you have:

=RIGHT("00" & DEC2BIN((ROW()-1)/512),3) & RIGHT("00000000" & DEC2BIN(MOD((ROW()-1),512)),9)

Edit: I was not aware of the optional number of digits argument. Using it will give this function:

=DEC2BIN((ROW()-1)/512,3) & DEC2BIN(MOD((ROW()-1),512),9)

Put this into cells A1 to A4096.

Edit 2: As per Lưu Vĩnh Phúc's comment, it is possible the OP wanted 12 columns with one binary digit each. In this case, put

=MID( DEC2BIN((ROW()-1)/512,3) & DEC2BIN(MOD((ROW()-1),512),9) ,COL(),1)

into all cells A1 to L4096.

Solution 2:

Just copy-paste the following formula inside A1:

=MOD(QUOTIENT(ROW()-1,2^(COLUMN()-1)),2)

Then drag-fill up to L4096.

Explanation:

• The formula extracts the n^th bit of a number
• ROW() represents the number (number >= 0)
• COLUMN() represents n (n >= 0)
• Just integer-divide the number by 2 ^ n, then calculate modulus 2 of the result

Solution 3:

First enter the following User Defined Function in a standard module:

Public Function BigBinary(r As Range) As String
    Dim addy As String, s1 As String, s2 As String

    addy = r.Address(0, 0)
    s1 = "=DEC2BIN(INT(A1/2^27),9)&DEC2BIN(INT(MOD(A1,2^27)/2^18),9)&DEC2BIN(INT(MOD(A1,2^18)/2^9),9)&DEC2BIN(MOD(A1,2^9),9)"
    s1 = Replace(s1, "A1", addy)
    s = Evaluate(s1)
    BigBinary = s
End Function

This returns a string of 36 "bits". Then in A1 enter:

=ROW()-1

and copy down through A4096

In B1 enter:

=RIGHT(bigbinary(A1),12)

and copy down through B4096:

User Defined Functions (UDFs) are very easy to install and use:

1. Alt-F11 brings up the VBE window
2. Alt-I, Alt-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Solution 4:

Another way I've used:

• Fill from A1 to L1 with zeroes
• In A2 write =1-A1
• In B2 write =IF( AND( A1=1, A2=0), 1-B1, B1)
• Copy B2 formula to C2:L2
• Copy row A2:L2 formulas to rows 3:4096

This produces all binary strings in order, with least significant bits on first column. Last row (4096) is all ones.

This does not rely on ROW() (so it can be freely moved), you can increase the length directly, and it's straighforward to generalize to non-binary strings. It also works with LibreOffice Calc.