Coin Toss macro to show all possible outcomes of 10 flips.

[Post Deleted]

In A1, enter =IF(MOD(INT( (ROW(F1012)-1) / (2^(COLUMN(F1012)-1)) ), 2)=1, "Head", "Tail"). Drag across 10 columns, and drag it down to row 1024

Originally Posted by 2311ski

I've been given this task for an extra credit assignment for a non-cs related class. [....] I need to create a macro that shows all the possible outcomes of 10 coin flips (1,024) in the following format
H H H H H H H H H H
H H H H H H H H H T
....
all the way through to
T T T T T T T T T T

This is my first time coding anything in excel vba and I have more experience with C++ and JavaScript and I'm not sure where to begin with tackling this problem.

Since it is a class assignment -- and for extra credit -- I hope you understand that we should not provide turnkey solutions (i.e. the complete program). But we are happy to answer your how-to questions regarding the use of VBA.

[Edit] Never mind! I see that JieJenn decided to deny you the opportunity to learn. If you are more interested in learning than getting credit for JieJenn's work, let us know how we can assist you further, perhaps by addressing my comments below.

First, why don't you start by showig us how you would tackle the problem with C++ or JavaScript. Then perhaps we could help you "translate" that into VBA.

Hint: Note that you can generate all head-tail sequences by incrementing a variable from 0 to 1023 and looking at its binary representation.

Second, tell us what you don't know about "where to begin" with VBA.

For example, do you know how to create VBA macros in general; or is that the first thing you would like to know? That's a fair question for us to answer.

For another example, do you know the basics of VBA (no pun intended): how to declare a macro and variables; and how to write assignment statements and control statements (if, for, case, etc)? That's a lot to help you with. But we might point you to some online and offline resources.

Finally, do you know how to put the results into a spreadsheet? That's a fair question for us to answer. But do you want the results in a spreadsheet; or do you need help in writing results into the Immediate Window or a text file?

Originally Posted by 2311ski

Thanks, that is exactly what I'm looking for, an understanding of the code and solution.

Great!

Originally Posted by 2311ski

First off if I was to code this in c++ I would use the following to generate a heads/tails result and after create an array of 1024 elements to hold the data to access and use that to put in a spreadsheet.

Please Login or Register  to view this content.

Well, that would be wrong(!).

According to you, the assignment is "create a macro that shows all the possible outcomes of 10 coin flips".

Moreover, the assignment requires that the result be "in the following format", which appears to be from HHHHHHHHHH, HHHHHHHHHT to TTTTTTTTTT. I interpret that (perhaps incorrectly) to mean: in some reasonable order. (Do you agree with that interpretation?)

Your C implementation would generate a random sample of 10 coin flips. That does not guarantee you will generate all 1024 combinations; in fact, statistics tells us there is a high probability you will not. And it does not guarantee the combinations are generated in "some reasonable order".

Instead, the C implementation is much simpler. Think of H as 0 and T as 1. Then HHHHHHHHHH is the binary value 0000000000; HHHHHHHHHT is the binary value 0000000001; and TTTTTTTTTT is the binary value 1111111111.

As a computer science student, if you have been introduced to binary representation, that should suggest a novel way to represent all of the combinations of 10 ones and zeros -- I mean H and T. ;-) Are you familiar with binary representation?

(Actually, I already gave you that much as a "hint" in my previous posting.)

Originally Posted by 2311ski

I've learned how to create a macro and assign variables from these pages:
http://www.excel-easy.com/vba/create-a-macro.html
http://www.excel-easy.com/vba/variables.html
but that's as far as it goes. Also, I don't know how to put it in a spreadsheet and that's what I need it in.

Great! You are a good self-starter.

Also take a look at http://www.excel-easy.com/vba/loop.html.

But as you realize, that set of files is seriously lacking in sufficient detail.

Once you present a credible C implementation, we can help you translate it into VBA.

(Let us know if you are totally stuck.)

PS.... Previously, you wrote this is for a non-CS class. What class is it for? Are you really expected to learn VBA on your own?

PS....

Originally Posted by joeu2004

Once you present a credible C implementation, we can help you translate it into VBA.

Sorry, I got a little pedantic. The following might help you get started with VBA, if your C implementation is anything like mine. ;-)

C	VBA
statement;	each statement on a separate line; no semicolon terminator
// comment	' comment
int i;	dim i as integer ' or long (preferred)
char s;	dim s as string*1
for (i = 0; i <= 1023; i++) {...}	for i = 0 to 1023 ' step 1 (implied) ... next i
for (j = 10; j >=1; j--) {...}	for j = 10 to 1 step -1 ... next j
x /= 2;	x = x \ 2
s = (i & 1) ? 'T' : 'H';	s = iif(i AND 1, "T", "H")
	cells(i,j)=s ' store into Excel row i, column j
if (expr) {...} else {...}	if expr then ' see if-then-else help page for more ... else ... end if

In any language, I would think the simplest way would be to nest 10 loops, each one having a value of 0 or 1.

Use the value of the loop counter to index an array containing "H" and "T" as its elements.

At the inner level, concatenate the 10 values into a result variable, increment a row counter and output the result to column A indexed by the row counter.

The values from HHHHHHHHHH to TTTTTTTTTTTT would be in single cells

Please Login or Register  to view this content.

Regards, TMS

Just thought: instead of indexing the array, you could just concatenate the values and then replace "0" with "H" and "1" with "T"

Doesn't make it shorter, just doesn't need the array.

@JieJenn: you confused me a little.

In your post #3, the formula should be:

A1:

Formula:

Please Login or Register  to view this content.

It is correct (obviously) in the workbook but a random cell formula has been copied into the post. Wondered why I couldn't understand the output

Regards, TMS