MIF is transferring to a new server, so I'm not sure how well this will go. Please check to see if you are getting the new server message, and report back. (see top of page announcement)

Now to business. I think it helps to keep things as simple as possible. So I'm going to use H and T rather than 1 and 0.

Step (i) A1 = IF(RANDBETWEEN(0,1)=1,"H","T")

This should give you either an H or a T in the cell, at random.

Step (ii) copy into cells B1 to J1.

Now you should have a line of H and T results.

Step (iii) K1 = CONCATENATE(A1,B1,....J1)

This should give you a single 'word' or 'string' with the ten trials in correct order.

Step (iv) L1 = IFERROR(IF(FIND("HHHH",K1)>0,"yes","no"),"no")

That should put a yes or no according to whether there's a HHHH. I think there are other ways to do this step, but that one is yours and it works so I've stuck with it.

Now copy all those cells for ,say, 10 rows, just to test it.

You should have lines of H and T cells, with a ten letter word correctly showing and then either yes or no. I found there were typically about 2 or 3 yes.

To count the yes, step (v) = COUNTIF(L1:L10,"yes") should give you the total.

So if you've got that far, all you need to do is copy it for 10000 rows. I found values between 2000 and 3000, but the coins are random so there's bound to be some variation.

I'm a bit unhappy about having to create 10000 lines. It will tie up lots of RAM and it looks a bit messy. And what will you do if you're asked for a million throws?

So I've had a bit of help getting an iterative process working. I hit a problem with the way Excel works out cell values but I managed to find a fix. There may be a way to get Excel to do the required job, but I haven't found it yet.

When you're happy with the above, post back and I'll teach you how to do it. (Final version needed just 13 cells!)

Bob

]]>I tried to come up with formulas to tie it together and no dice.

Well, that's no problem; you are supposed to use coins!

Don't worry. I finally have it licked. Excel has an annoying feature (introduced to save time) that actually means I couldn't get it to re-calculate everything. After many false trails, I found a solution. But it will take a while to type up so I'll post again when I have it.

Bob

]]>Welcome to the forum!

]]>It's 8 am here in the UK. I'm out all morning at a dance show rehearsal so I'll have to leave this until this afternoon. That last image looks like you are just about there.

Bob

]]>albums/cc214/Hayabusakota/ScreenShot2013-04-27at30008PM-1.png

this is part one.

]]>IF(RANDBETWEEN(0,1)=1,"H","T") to get an H or T.

Then use CONCATENATE to build a ten letter word.

FIND HHHH instead.

I'm going to see if I can get my head around how you avoid 10000 rows of formulas.

It's 20.40 BST so I'm going to log off for now and come back to this tomorrow evening.

Bob

]]>k = 1000000000*A + 100000000*B + 10000000*C + 1000000*D + 100000*E + 10000*F + 1000*G + 100*H + 10*I + J

Or you could build a string version as you can 'add' letters without all those powers of ten.

Bob

]]>