You are not logged in.

- Topics: Active | Unanswered

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

Unfortunately it will not let me post links to the screen cap of it.

Offline

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

There's a time delay before new members can post images. Bit of a nuisance, but it's needed to make abuse etc less frequent.

If you have somewhere you can post an image you can tell me the address here.

Do it like this

htt etc

ww etc

link name

I'll re-build it and post it properly, which may help some other soul who is trying to follow this thread.

Bob

Offline

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

/albums/cc214/Hayabusakota/ScreenShot2013-04-27at15702PM.png

Offline

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

Offline

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

hi

Just been looking at the formulas. Let's see if I've got it.

B2 contains the zero/one number from ten rolls. eg. 1010111101

B3 initially contains zero.

FIND(1111,B2) looks to see if 1111 occurs and if it does B3 is incremented by 1

If it doesn't then IFERROR traps the value error and puts B3 back in B3, so B3 is not incremented.

So what happens if you put 1010111101 in B2 ?

Does B3 get incremented ?

Bob

Offline

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

the B3 cell seams to remain at zero and never changes.

Offline

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

in b3 my cell reads =IFERROR(IF(FIND(1111,B2)>0,B3+1,B3),B3)

Offline

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

try this:

=IFERROR(IF(FIND(1111,B2)>0,"yes","no"),"no")

That works for me.

Bob

Offline

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

With your b3 stuff in the formula I get circular reference error. Not surprising as the formula is sitting in the space you want to store a number.

thinking....................

Bob

Offline

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

ok the yes and no one worked.

Offline

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

now would there be a way to run number tally's in the same bracket and after 10k games have it reset ?

Offline

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

in the same bracket

?? not following you ??

Offline

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

the cell underneath the random coin flips I was trying to get number of wins in there and I can't figure out how to have that work. The yes/no worked but I am still kinda lost on how to get the number of wins to come out.

Offline

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

something else I did was on a separate sheet I did the same problem but took a different approach I made ten separate sells across a =RANDBETWEEN(0,1). I was thinking maybe there would be a way to have all those cell's join in a formula to figure out the average. I'll post a link of what I did in the separate cell. I am terrible at explaining what I was thinking lol! sorry

Offline

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

albums/cc214/Hayabusakota/ScreenShot2013-04-27at30008PM.png

Offline

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

How about this:

columns A to J hold the ten coin flips.

column K builds the ten digit o/1 value

column L has the IFERROR...... formula returning YES or NO

Copy 20 times down the page.

At the bottom of the L column use COUNTIF to count the YES values.

If this works, extend to 10000 rows.

Bob

Offline

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

i218 .photobucket. com

Offline

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

just wondering how do I build the ten digit 0/1 value into one cell?

Offline

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

This is messy but it will do it I think

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

Offline

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

Better idea. Don't use numbers at all.

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

Offline

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

No luck with that formula up there I was trying to build a string but I don't know how to have the string represent the number in each cell while grouped in the cell I am adding the totals in.

Offline

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

alright sounds good to me going to see if I know anyone locally that can help me figure this one out.

Offline

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

Hey Bob here you go a screen cap of the current problem almost solved I think.

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

this is part one.

Offline

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

i218 .photobucket. com/

Offline

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

hi Josh,

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

Offline