You are not logged in.

- Topics: Active | Unanswered

**JoshH****Member**- Registered: 2013-04-25
- Posts: 31

Thank you bob I am still plugging away at it when you come back post up something here and I'll let you know if I ended up hitting gold! good luck at the dance show!

Offline

**JoshH****Member**- Registered: 2013-04-25
- Posts: 31

No luck in figuring it out I tried to come up with formulas to tie it together and no dice.

Offline

**anonimnystefy****Real Member**- From: Harlan's World
- Registered: 2011-05-23
- Posts: 16,018

Hi JoshH

Welcome to the forum!

Here lies the reader who will never open this book. He is forever dead.

Taking a new step, uttering a new word, is what people fear most. ― Fyodor Dostoyevsky, Crime and Punishment

The knowledge of some things as a function of age is a delta function.

Offline

**bob bundy****Moderator**- Registered: 2010-06-20
- Posts: 7,706

JoshH wrote:

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

Children are not defined by school ...........The Fonz

You cannot teach a man anything; you can only help him find it within himself..........Galileo Galilei

Offline

**bob bundy****Moderator**- Registered: 2010-06-20
- Posts: 7,706

hi Josh,

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

Children are not defined by school ...........The Fonz

You cannot teach a man anything; you can only help him find it within himself..........Galileo Galilei

Offline

**ShivamS****Member**- Registered: 2011-02-07
- Posts: 3,648

Hello glisfton; welcome to the forum.

Offline