15

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?

  • which version of Excel do you use? – Máté Juhász May 17 '17 at 11:34
  • 1
    He can split the binary number into a high-order and low-order portion, then use Concatenate to stitch the two parts together. – I say Reinstate Monica May 17 '17 at 11:49
  • 8
    From your representation of the desired output, am I to take it that each digit should be in a seperate cell and not in one string? – Baldrickk May 17 '17 at 14:32
  • 4
    For future reference, the way to thank people on Stack Exchange is using that ▲ button. For the answer that worked for you (and gave the right result), use that ✔ button. If more than one answer works, choose the one that you like the best. – wizzwizz4 May 18 '17 at 18:43
27

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.

23

Just copy-paste the following formula inside A1:

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

Then drag-fill up to L4096.

Screenshot

The formula extracts the nth bit of a number (n >= 0): the number is integer divided by 2 ^ n, then calculate modulus 2.

9

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:

demo

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!

  • Surely one could just use =RIGHT(bigbinary(ROW()-1),12)? – wizzwizz4 May 18 '17 at 18:38
  • @wizzwizz4 You are correct...with a slight change to the UDF() – Gary's Student May 18 '17 at 19:17
  • I've just read the macro and noticed that it's overkill - you could just paste the formula directly into the box then use the click / drag functionality of Excel to change all of the cell references or - better still, just use ROW() - 1 instead of a cell reference. In a file with macros it's fine, but in one without it creates an irritating and scary-looking popup. – wizzwizz4 May 18 '17 at 19:23
  • 1
    @wizzwizz4 I used this UDF() because I already had it in my VBA toolbox – Gary's Student May 18 '17 at 19:31
3

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.

3

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 2n. In each cycle the first half (from 0 to 2n-1-1) will be 0 and the last half will be 1. For example the lsb (first bit from the right) will alternate every 21-1 = 1 bit, the second bit will toggle every 22-1 = 2 bits...

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

1

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.

  • 1
    Nice job "code golfing" (creating shorter text) compared to @Herb 's answer, although it seems you hard-code A1 where he uses a more flexible ROW(). Why does he say it works up through 511 but you say 255. Is one of you off, or is it a difference in different versions of Excel? – TOOGAM May 17 '17 at 13:55
  • instead of ROUNDDOWN(A1/256,0) you can just use QUOTIENT(A1, 256), or even better BITRSHIFT(A1, 8). The latter part can also be replaced with BITAND or MOD – phuclv May 17 '17 at 14:02
-1

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'
  • 4
    I'm not the down voter, but "In Excel, I need to generate...". This is not in excel. Importing a CSV and copying the cells to the right place any time you want to modify it (e.g. more/less rows) would be a pain. – Tom Carpenter May 17 '17 at 16:52
  • This is for Python <3 but >=2.3. For forwards-compatibility, please add brackets so that the print keyword is called like a function - that way it will be compatible with Python 2.3+ and Python 3.0.0+ – wizzwizz4 May 18 '17 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.