# How to create a coin tossing combination matrix

#### galatius

##### New Member
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?

### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### mikerickson

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

#### gardnertoo

##### Well-known Member
...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

##### MrExcel MVP
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><b>A</b><td align=center width=15><b>B</b><td align=center width=15><b>C</b><td align=center width=15><b>D</b><td align=center width=15><b>E</b><td align=center width=15><b>F</b><td align=center width=15><b>G</b><td align=center width=15><b>H</b><td align=center width=15><b>I</b><td align=center width=15><b>J</b><td align=center width=15><b>K</b><td align=center width=15><b>L</b><td align=center width=15><b>M</b><td align=center width=15><b>N</b><td align=center width=15><b>O</b><td align=center width=15><b>P</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>9</b><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>10</b><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT></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><b>A</b><td align=center width=15><b>B</b><td align=center width=15><b>C</b><td align=center width=15><b>D</b><td align=center width=15><b>E</b><td align=center width=15><b>F</b><td align=center width=15><b>G</b><td align=center width=15><b>H</b><td align=center width=15><b>I</b><td align=center width=15><b>J</b><td align=center width=15><b>K</b><td align=center width=15><b>L</b><td align=center width=15><b>M</b><td align=center width=15><b>N</b><td align=center width=15><b>O</b><td align=center width=15><b>P</b><td align=center width=15><b>Q</b><td align=center width=15><b>R</b><td align=center width=15><b>S</b><td align=center width=15><b>T</b>
<tr><td align=center bgcolor="#aaaaaa"><b>65531</b><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>65532</b><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>65533</b><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>65534</b><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>65535</b><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>65536</b><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></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

##### MrExcel MVP
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?

Last edited:

#### ZVI

##### MrExcel MVP
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
ABCDEFGHIJKLMNOPQR
1HHHH4Number of tosses
2HHHT
3HHTH
4HHTT
5HTHH
6HTHT
7HTTH
8HTTT
9THHH
10THHT
11THTH
12THTT
13TTHH
14TTHT
15TTTH
16TTTT
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

Last edited:

#### galatius

##### New Member
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!

Replies
9
Views
5K
Replies
6
Views
374
Replies
9
Views
599
Replies
0
Views
223
Replies
5
Views
488

Threads
1,190,646
Messages
5,982,114
Members
439,756
Latest member
alice128

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back