Flip Coin

[klind]

I need Excel to select a value depending on a random coin flip. The values are: 5, 10, 15, 20, 25, ... through 95. The beginning number is always 10. If the coin returns tails the number returned will always increment one level (plus 5, but never greater than 95). If the coin returns heads the number returned will always decrement one level (minus 5, but never less than 5). However, if the coin returns heads two out of any three consecutive tosses the number will be set to 10 and everything starts over.
I'd like to perform this with INDEX/MATCH or VLOOKUP if possible. I'm able to get most of it, but the "two out of any three consecutive tosses" has got me stumped.
Thanx,
Ken

 

[Rick Rothstein]

Show us the values that should be next to each of these flips...
Tails
Tails
Heads
Heads
Tails
Tails
Heads
Tails
Heads
Heads
Tails
Heads
Tails
Tails
Heads

 

[klind]

Here it is:

Show us the values that should be next to each of these flips...
Starting Value 10
Tails 10
Tails 15
Heads 10
Heads 10
Tails 15
Tails 20
Heads 15
Tails 20
Heads 15
Heads 10 <not because -5 but because 2 heads out of 3 tosses = starting value
Tails 15
Heads 10
Tails 15
Tails
Heads

 

[klind]

Missed the last two... they would be:

Tails 20
Heads 10

 

[Rick Rothstein]

Tails 10
Tails 15
Heads 10
Heads 10
Tails 15
Tails 20
Heads 15
Tails 20
Heads 15
Heads 10
Tails 15
Heads 10
Tails 15
Tails 20
Heads 10

The blue number is 10 because there are two heads within the three consecutive cells, right? So why isn't the red number 10 instead of 15 (there are two heads in the three consecutive cells)? Or am I misunderstanding your rules?

By the way, I am not sure there will be a formula solution possible given your reset rule (I cannot think of anyway, yet, to hold onto that reset when it happens). If I cannot come up with a formula, and no one else posts one, I'll give you a VBA solution for you to consider.

 

[SyedUsman]

here is what i have got , Paste the formula in cell B3

=MIN(IF(SUM(--(IF(A1:A3="heads",TRUE,FALSE)))<>2,MAX(IF(A3="heads",B2-5,B2+5),10),10),95)

Use with Ctrl + Shift + Enter

Data Starts from A3 WITH b2 having the initial 10

cheers

 

[klind]

Good question Rick. I don't know the answer, I only know the question. Perhaps the blue 15 should be 10. I just wrote the numbers down as to what I thought they might should be. Here's the rules:

1. Given a set of whole numbers less than 100 that are evenly divisible by 5 simulate a series of 100 random coin flips that increment and decrement a pointer, if possible, according to the following conditions.
   
2. Set the pointer at the number 10.
   
3. If the result is tails increment the pointer one level and continue incrementing one level as long as the result is tails. As soon as the result is heads go to rule 2.
   
4. If the result is heads increment the pointer one level and continue:

1. 1. If the result is tails go to rule 2.
      
   2. If the result is heads increment the pointer one level and continue:
      (1) Increment the pointer one level as long as the result is heads otherwise decrement the pointer one level.
      (2) If any two of the preceding three results are tails go to rule 2.

 

[Rick Rothstein]

Good question Rick. I don't know the answer, I only know the question. Perhaps the blue 15 should be 10. I just wrote the numbers down as to what I thought they might should be. Here's the rules:

1. Given a set of whole numbers less than 100 that are evenly divisible by 5 simulate a series of 100 random coin flips that increment and decrement a pointer, if possible, according to the following conditions.
   
2. Set the pointer at the number 10.
   
3. If the result is tails increment the pointer one level and continue incrementing one level as long as the result is tails. As soon as the result is heads go to rule 2.
   
4. If the result is heads increment the pointer one level and continue:

1. 1. If the result is tails go to rule 2.
      
   2. If the result is heads increment the pointer one level and continue:
      (1) Increment the pointer one level as long as the result is heads otherwise decrement the pointer one level.
      (2) If any two of the preceding three results are tails go to rule 2.

Is this a homework assignment? If so, what kind of class are you taking that would asked this kind of question?

 

[klind]

No Rick, it is not a homework assignment. I was born in 1945, I am 72 years of age and I left school many years ago. I have retired three times. I retired as a US Army officer way back in 1985 and from the Department of the Interior in 2007. You can catch up with me at Linkedin here: https://www.linkedin.com/in/kenlindiskesl/

These days I sit around and endeavor to keep myself busy, sometimes learning new stuff. Actually the problem I am attempting to solve would not work with a coin, unless the coin would sometimes (about 14%) land on it's edge, as there are to be three possibilities, not two. However, I figure that if I can get what I described above (they are my words, not a teacher's) I might be able to learn Apple Numbers better. It does, though, remind me of school. Years ago, back in the mid-80's, I was seeking my masters in computer science. The teacher gave us a situation to program in Pascal. It dealt with a female gymnastics thing where groups of girls would score simultaneously while performing at different gymnastics functions... yada, yada, yada. I sought help from my son who provided me with several solutions in several different computer languages including Basic, Fortran, and Assembly... but not Pascal... When confronted, my son simply stated that he needed to leave something to me. Believe me, an Excel (or Numbers) solution to the situation I presented is of my own concoction and is only a small part of my personal spreadsheet learning process.