Coin Flip

[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've been able to get most of it, but the "two out of any three consecutive tosses" has got me stumped.

Thanx,

Ken

 

[Chihiro]

It would help, if you can upload what you've got so far. Otherwise, it would be difficult to construct formula.

I'd imagine, that you can use COUNTIFS or FREQUENCY function.

 

[AliGW]

There are already answers on another forum that the OP has not acknowledged. I have reported this thread (several hours ago).

 

[Luke M]

Hi @klind

As AliGW has pointed out, this is an example of a cross-post, where you post the same question on multiple threads. While asking the same questions in multiple places is both okay and encouraged, we ask that when you do so, include links in your post to the other locations so that those who come to answer your question can see what others have already suggested, and/or see if the question is already solved.

Link to other thread:
https://www.excelforum.com/excel-formulas-and-functions/1197999-icon-flip.html

Thanks.

 

[Hui]

Ken

Firstly, Welcome to the Chandoo.org Forums

Your post has been cross-posted, which means it has been seen on other websites. This is considered poor practice, as it can waste peoples time, which could be spent elsewhere, especially if you get a solution and don't notify us.

I encourage you to please read the site rules at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/

In regards your question, If it has been answered please acknowledge this on all sites where you have asked the question.

 

[klind]

Please close this thread as I have solved the situation myself using pseudocode. In addition, I have learned that neither Excel nor Numbers is capable with either VLOOKUP or INDEX/MATCH. Both can provide a solution using convoluted nested IF statements, but that does not allow for ease of maintenance.

thanx,

Ken

 

[Hui]

What about using this technique:


see attached file:

 

[klind]

Thanx Hui... That has merit except that when it doesn't reset it needs to increment... Anyway I think I'm going to give up as I was simply attempting to create a spreadsheet that could test some very complicated conditions. Seems it might be too difficult for a spreadsheet.

 

[Hui]

It does increment?

Your original words were "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)"

Download the file and press F9 and examine the results

Starting at 10 means you get a lot of results at 5

 

[NARAYANK991]

It does increment?

Your original words were "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)"

Download the file and press F9 and examine the results

Starting at 10 means you get a lot of results at 5

Hi ,

You need to change the signs around ; every Tail should increment the value.

Narayan

 

[Hui]

Did I put them the wrong way around?
Oh yes, but only in C3 & C4, the rest are ok

See attached file:

I have also added a Data table to run the model up to 50 times and summarise the results

 

[klind]

That looks better... thanx!