Want to show ALL possible outcomes of 10 coin flips

[sacajamo]

I need to show all possible outcomes (1,024, I think) of flipping a coin 10 times, such as:

H H H H H H H H H H
H H H H H H H H H T

all the way down to:

T T T T T T T T T T

Does anyone know a way I can do that on Excel without reinventing the wheel?

 

[mikerickson]

You could put this in a cell and drag it down and right until its large enough for your need.

=MOD(INT( (ROW(A1)-1) / (2^(COLUMN(A1)-1)) ), 2)

 

[mirabeau]

Just run this VBA code which I found in the attic, albeit a bit overkill, it should do what you ask.

Sub H_and_T()
Dim valz, y(), n&, v&
Dim a&, b&, c&, d&
valz = Array("H", "T")
n = 2: v = 10
ReDim y(1 To n ^ v, 1 To v)
For a = 1 To v
    For b = 1 To n ^ v Step n ^ a
        For c = b To b + n ^ (a - 1) - 1
            For d = 1 To n
                y(c + n ^ (a - 1) * (d - 1), v - a + 1) = valz(d - 1)
Next d, c, b, a
Range("A1").Resize(n ^ v, v) = y
End Sub

 

[lcampbell91]

but how would i explain the VBA in simpler terms?

 

[mikerickson]

Another solution would be to put this formula in a cell and drag down until done.
=SUBSTITUTE(SUBSTITUTE(DEC2BIN(ROW(A1),10),"0","H"),"1","T")

How to explain it? "All possibilities of N choices is the same as counting, base N."

 

[lcampbell91]

Thank you but I meant how do I explain the VBA as if I were telling it to a child. I only understand what some parts of the code mean. I don't know what all of it means. that is where I am struggling.

Sub Outcomes()
Dim valz, y(), n&, v&
Dim a&, b&, c&, d&
valz = Array("H", "T")
n = 2: v = 10
ReDim y(1 To n ^ v, 1 To v)
For a = 1 To v
For b = 1 To n ^ v Step n ^ a
For c = b To b + n ^ (a - 1) - 1
For d = 1 To n
y(c + n ^ (a - 1) * (d - 1), v - a + 1) = valz(d - 1)
Next d, c, b, a
Range("A1").Resize(n ^ v, v) = y
End Sub

 

[mikerickson]

Its been three years since Mirabeau posted that code.
I would do it differently.
The Redim block is just setting things up. (This is a bit more generalized than flipping a two sided coin)
The For Next loops are just mechanically adding one to the preceding "numeral"

Sub test()
    Dim LetterCount As Long, NumFlips As Long
    Dim Letters() As String
    Dim Output() As String
    Dim rNumber As Long, cNumber As Long
    Dim Flag As Boolean
    LetterCount = 2
    NumFlips = 10
    
    ReDim Letters(0 To LetterCount - 1)
    Letters(0) = "T": Letters(1) = "H"
    ReDim Output(1 To LetterCount ^ NumFlips, 1 To NumFlips)
    For cNumber = 1 To NumFlips
        Output(1, cNumber) = Letters(0)
    Next cNumber

    For rNumber = 2 To UBound(Output, 1)
        Flag = False
        For cNumber = 1 To NumFlips
            If Flag Then
                Output(rNumber, cNumber) = Output(rNumber - 1, cNumber)
            Else
                If Output(rNumber - 1, cNumber) = Letters(LetterCount - 1) Then
                    Output(rNumber, cNumber) = Letters(0)
                Else
                    Output(rNumber, cNumber) = Letters(WorksheetFunction.Match(Output(rNumber - 1, cNumber), Letters, 0))
                    Flag = True
                End If
            End If
        Next cNumber
    Next rNumber

    Range("A1").Resize(UBound(Output, 1), UBound(Output, 2)).Value = Output

End Sub

 

[lcampbell91]

Wow this is awesome and I think I understand it better than the other VBA. But I still don't understand all the parts of this Module.

But thank you so much for your help, you are a great person!! Could you possible still help me understand what some of the VBA terms mean. for example What does the following mean if I were to put it into words?
Dim LetterCount As Long, NumFlips As Long
Dim Letters() As String
Dim Output() As String
Dim rNumber As Long, cNumber As Long
Dim Flag As Boolean

 

[mikerickson]

Dim LetterCount as Long

"The variable LetterCount is a variable that holds values that are of the Long data type."

Dim Letters() As String

"Letters is an array of undetermined size, that holds Strings"

 

[lcampbell91]

Thank this was very help, I did so more research on my own to try and understand the VBA, could you possibly help me translate the other parts that I still dont understand?
· Dim rNumber As Long, cNumber As Long =
· ReDim Output (1 to LetterCount ^ NumFlips, 1 To NumFlips)
· For cNumber = 1 to NumFlips
· Output (1, cNumber) = Letters(0)
· Next cNumber
· For rNumber = 2 To UBound (Output, 1)
· Flag cNumber = 1 To NumFlips
· If Flag Then Output (rNumber, cNumber) = Output(rNumber - 1, cNumber)