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

Question

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?

Answer 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.

Answer 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. For example the 5th bit of 1234 (10011010010b) would be calculated as follows:
  • (1234 \ 2 ^ (5 - 1)) % 2
  • = (1234 \ 16) % 2
  • = 77 % 2
  • = 1

Answer 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!

Answer 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.

Answer 5

Put the following formula in each cell from A to L, for all rows from 1 to 4096

=IF(MOD(ROW() - 1, 2^(13 - COLUMN())) < 2^(12 - COLUMN()), 0, 1)

If you want the whole thing in a string with spaces like what you asked, put this in the last column

=A1 & " " & B1 & " " & C1 & " " & D1 & " " & E1 & " " & F1 & " " & G1 & " " & H1 & " " & I1 & " " & J1 & " " & K1 & " " & L1

Then drag the rows all the way until M4096

For a more general solution, put the number of bits in some cell, like Z1, or named cell like NumOfBits and use the following formula

=IF(MOD(ROW() - 1, 2^(NumOfBits + 1 - COLUMN())) < 2^(NumOfBits - COLUMN()), 0, 1)

It can also be easily modified to use any cell as the starting cell by changing the row and column offset

Optimized version using bitwise operations instead of powers:

=IF(BITAND(ROW() - 1, BITLSHIFT(1, 13 - COLUMN()) - 1) < BITLSHIFT(1, 12 - COLUMN()), 0, 1)

=IF(BITAND(ROW() - 1, BITLSHIFT(1, NumOfBits + 1 - COLUMN()) - 1) < BITLSHIFT(1, NumOfBits - COLUMN()), 0, 1)

---

Quickest way:

• Copy either of the above formulas
• Press F5 (or Ctrl+G) and enter A1:L4096 to select the whole range
• Press F2 then Ctrl+V to paste
• Press Ctrl+Shift+Enter. Boom. You're done. No need to drag

It's an array formula which is much faster to calculate and produce a far smaller file

---

Explanation:

If we write all binary representations in rows from top to bottom, the flipping/toggling cycle of the nth-bit (counting from the lsb) is 2ⁿ. In each cycle the first half (from 0 to 2^n-1-1) will be 0 and the last half will be 1. For example the lsb (first bit from the right) will alternate every 2^1-1 = 1 bit, the second bit will toggle every 2^2-1 = 2 bits...

As a result we'll take modulo 2ⁿ to get number's current position in the cycle, if it's less than 2^n-1 it's a zero bit, else it's a one.

Answer 6

Since what you are looking for is every 12 digit binary number your best bet is to use the "DEC2BIN" function on every number from 0 to 4095 (2^12-1). Unfortunately DEC2BIN only works up to 8 digits so the final formula looks a bit tricky because it of the concatenation:

 =DEC2BIN(ROUNDDOWN(A1/256,0),4)&DEC2BIN(A1-256*ROUNDDOWN(A1/256,0), 8)

DEC2BIN takes the number to convert and the number of digits you want to output. I combined 4 and 8 to get 12. To shift the first 4 digits up to the highest value I divide by 256 (2^8) and round down to ignore the other lower value digits. For the lower value digits subtract this value so that they will continue counting past 255.

Search for decimal to binary conversion and bit shifting to understand how this works.

Answer 7

An answer that creates the data outside of excel, but creates a .csv that can be opened to get the table in excel.

This uses python, but could be done in a shell language too. Just run it in cmd.exe if python is installed.

python -c "for i in range(0,2**12): print (','.join((j for j in '{:b}'.format(i).zfill(12))))" > binary.csv

This creates a file (binary.csv) which contains the desired content, each digit in seperate cells as (I think) is desired from the question.

explanation:

python -c                    < call python passing in a string for the program to run
for i in range(0,2**12):     < for each value from 0 to 12 bits
print (                      < print...
','.join(                    < comma separated...
(j for j in                  < each character from...
'{:b}'.format(i)             < binary representation of current value
.zfill(12))))"               < padded with '0' to length 12
> binary.csv                 < and output to 'binary.csv'