I am trying to make a table that has every combination of 1 and 0, in lists of 5. For example,
11111 11110 10111 10011,
and so on. The order does matter. I am trying to do it in Excel currently but all the videos I am watching on the topic are using Power Query or another way that my Excel seems to be missing.
Is there a simple way to do this in Excel or even R? I have some R experience but would prefer it to be in Excel. Thanks so much for any guidance!
What you're describing could be thought of as "all the 5 digit numbers in binary", which can describe the integers from 0 to 31. There's a built-in function in one of the packages that comes with R (R.utils) that converts from integer to binary. So:
R.utils::intToBin(0:31)
Result
[1] "00000" "00001" "00010" "00011" "00100" "00101" "00110" "00111" "01000" "01001" "01010" "01011" "01100"
[14] "01101" "01110" "01111" "10000" "10001" "10010" "10011" "10100" "10101" "10110" "10111" "11000" "11001"
[27] "11010" "11011" "11100" "11101" "11110" "11111"
In Excel, make a column with the numbers from 0 to 31. Then next to that, put in DEC2BIN(A1,5) and drag down.
The expand.grid() function in R is designed to "[c]reate a data frame from all combinations of the supplied vectors or factors", in this case:
> expand.grid(lapply(numeric(5), function(x) c(0,1)))
Var1 Var2 Var3 Var4 Var5
1 0 0 0 0 0
2 1 0 0 0 0
3 0 1 0 0 0
4 1 1 0 0 0
5 0 0 1 0 0
6 1 0 1 0 0
7 0 1 1 0 0
8 1 1 1 0 0
9 0 0 0 1 0
10 1 0 0 1 0
11 0 1 0 1 0
12 1 1 0 1 0
13 0 0 1 1 0
14 1 0 1 1 0
15 0 1 1 1 0
16 1 1 1 1 0
17 0 0 0 0 1
18 1 0 0 0 1
19 0 1 0 0 1
20 1 1 0 0 1
21 0 0 1 0 1
22 1 0 1 0 1
23 0 1 1 0 1
24 1 1 1 0 1
25 0 0 0 1 1
26 1 0 0 1 1
27 0 1 0 1 1
28 1 1 0 1 1
29 0 0 1 1 1
30 1 0 1 1 1
31 0 1 1 1 1
32 1 1 1 1 1