How to create a coin tossing combination matrix

galatius · Mar 28, 2009

Thank you in advance to anyone who can help me with this conundrum!

I want to create a spreadsheet which allows the user to input a variable number of coin tosses (ranging from 1 to 15). Once the user has made their selection, the spreadsheet automatically prepares a matrix of all possible head-tail combination sequences given the number of coin tosses selected.

I would like the rows in the matrix to represent the various coin tossing combination sequences while the columns represent the different coin tosses. H or T is inputted in the matrix cells to represent heads or tails respectively.

So, for example, if 1 coin toss was selected, the matrix would look like this:

H
T

If 2 coin tosses were selected, the matrix would look like this:

HH
HT
TH
TT

If 3 coin tosses were selected, the matrix would look like this:

HHH
HHT
HTH
THH
HTT
THT
TTH
TTT

It's obviously very easy working these matrices out for small coin tosses. But given that the total number of combination sequences doubles each time, the matrix very quickly becomes unfeasible to create manually!

Is there any way to prepare a spreadsheet that could prepare matrices like these given the input of a single value representing the desired number of coin tosses?

gardnertoo · Mar 28, 2009

The DEC2BIN function works up to 10, but beyond that we'll have to do something else.

Book2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

10

Number of tosses

2

3

4

5

6

Sheet1

gardnertoo · Mar 28, 2009

Mistake in earlier post: the DEC2BIN works up to 9 tosses, not 10. Here's a working set of formulas for up to 15. Copy the column A:P formulas down to row 32786.

Book2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

H

00000000000000

14

Number of tosses

2

3

Sheet1

mikerickson · Mar 28, 2009

What do you want to do with these charts?
There a probably better, faster, easier ways than listing all the possibilites.

gardnertoo · Mar 28, 2009

...and you sure don't want to print it. The full list for 15 tosses takes 631 sheets of paper at my default settings.

mikerickson · Mar 28, 2009

Just for fun.
Put this formula in A2
=IF(ROW(A2)<=(2^(COLUMN(A2)-1)),0, IF(SUM(OFFSET(A1,0,0,-(2^(COLUMN(A2)-1)),1))=(2^(COLUMN(A2)-1))*A1,IF(A1=0,1,0), A1))

Drag down and to the right as far as you like.
format all cells with the custom format T;;\H

(significant pause while my MacBook calculates 17 X 65536 cells to create my screen shots.)
The way it works is 2^Column(A2)-1 gives the sequence 1,2,4,8,...

Each cell looks above it self, if the 1,2,3,8,... cells above it are not all the same, show the value of the cell above (0 or 1). If they are all the same, change values.

That's all wrapped in a check to verify that there are 1,2,4,.. cells above it.

<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=15>A<td align=center width=15>B<td align=center width=15>C<td align=center width=15>D<td align=center width=15>E<td align=center width=15>F<td align=center width=15>G<td align=center width=15>H<td align=center width=15>I<td align=center width=15>J<td align=center width=15>K<td align=center width=15>L<td align=center width=15>M<td align=center width=15>N<td align=center width=15>O<td align=center width=15>P
<tr><td align=center bgcolor="#aaaaaa">1<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">2<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">3<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">4<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">5<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">6<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">7<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">8<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">9<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr>
<tr><td align=center bgcolor="#aaaaaa">10<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H</tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td>A1:P1<td align=center>A1 <td align = left >=IF(ROW(A1)<=(2^(COLUMN(A1)-1)),0,IF(SUM(OFFSET(#REF!,0,0,-(2^(COLUMN(A1)-1)),1))=(2^(COLUMN(A1)-1))*#REF!,IF(#REF!=0,1,0),#REF!))</tr>
<tr><td>A2:P10<td align=center>A2 <td align = left >=IF(ROW(A2)<=(2^(COLUMN(A2)-1)),0,IF(SUM(OFFSET(A1,0,0,-(2^(COLUMN(A2)-1)),1))=(2^(COLUMN(A2)-1))*A1,IF(A1=0,1,0),A1))</tr></table>
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=15>A<td align=center width=15>B<td align=center width=15>C<td align=center width=15>D<td align=center width=15>E<td align=center width=15>F<td align=center width=15>G<td align=center width=15>H<td align=center width=15>I<td align=center width=15>J<td align=center width=15>K<td align=center width=15>L<td align=center width=15>M<td align=center width=15>N<td align=center width=15>O<td align=center width=15>P<td align=center width=15>Q<td align=center width=15>R<td align=center width=15>S<td align=center width=15>T
<tr><td align=center bgcolor="#aaaaaa">65531<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">65532<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">65533<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">65534<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">65535<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">65536<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > T<td bgcolor="#FFFFFF" > H<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td>A65531:Q65536<td align=center>A65531 <td align = left >=IF(ROW(A65531)<=(2^(COLUMN(A65531)-1)),0,IF(SUM(OFFSET(A65530,0,0,-(2^(COLUMN(A65531)-1)),1))=(2^(COLUMN(A65531)-1))*A65530,IF(A65530=0,1,0),A65530))</tr></table>

ZVI · Mar 28, 2009

It is not clear should the result be in a single column or not?
If not, should it be justified to the left or to the right column?

ZVI · Mar 28, 2009

And just for VBA fun

Rich (BB code):

<font face=Courier New>
' ZVI:2009-03-29 http://www.mrexcel.com/forum/showthread.php?t=380785
' Set to the range [A1:O32768] the array formula (Ctrl-Shift-Enter):
' =HeadsTails(Q1)
' where Q1 is the cell with number of coin tosses
Function HeadsTails(Tosses As Integer)
  Dim arr$(), m&, n&, r&, c&, rs&, cs&
  Const T = "T", H = "H"
  With Application.Caller
    ReDim arr(1 To .Rows.Count, 1 To .Columns.Count)
  End With
  m = 2 ^ Tosses
  rs = UBound(arr, 1)
  cs = UBound(arr, 2)
  For r = 1 To rs
    If r > m Then Exit For
    n = r - 1
    For c = cs To 1 Step -1
      If cs - c < Tosses Then
          If (n Mod 2) Then arr(r, c) = T Else arr(r, c) = H
          n = n \ 2
      End If
    Next
  Next
  HeadsTails = arr
End Function</FONT>

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

H

4

Number of tosses

2

H

T

3

H

T

H

4

H

T

5

H

T

H

6

H

T

H

T

7

H

T

H

8

H

T

9

T

H

10

T

H

T

11

T

H

T

H

12

T

H

T

13

T

H

14

T

H

T

15

T

H

16

T

17

18

19

20

Sheet1

Select cells A1:O32768
Put this formula =HeadsTails($Q$1)
Finish with CTRL-SHIFT-ENTER for entering of a single array formula to all range mentioned above.
Result is justified to the right (O) column

galatius · Mar 29, 2009

Wow. Just wow.

THANK YOU!

I am seriously blown away not only by the rapid responses but the sheer ingenuity as well!

I am going with your solution mikerickson - works perfectly!

Again, THANK YOU!

How to create a coin tossing combination matrix

galatius

New Member

Excel Facts

gardnertoo

Well-known Member

gardnertoo

Well-known Member

mikerickson

MrExcel MVP

gardnertoo

Well-known Member

mikerickson

MrExcel MVP

ZVI

MrExcel MVP

ZVI

MrExcel MVP

galatius

New Member

Similar threads

Forum statistics

Share this page

How to create a coin tossing combination matrix

New Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock