Challenge #73: Plinko Probabilities

[MattD]

The solution to last week's challenge is HERE!

This year at Inspire, our attendees’ ability to win games of chance will be tested – and not just because of the Las Vegas venue! The ability to obtain the hottest Alteryx swag will be decided by a round of Plinko at the Community hub. Can you use Alteryx to break down the probabilities of our board and ensure your odds are the best possible?

There are no start files to this week’s challenge - any workflow or app that assigns relative probabilities to the final outcomes of the board above will suffice!

Hints below:

Spoiler

Formulaic approach

Reshaping approach

[patrick_digan]

I've taken a very rudimentary approach...

Spoiler

I numbered each row in the board starting with 1 at the top and assigned the slots numbers starting with 1 at the left. So to start, you can put it in row 1 and slots 1-5. If you start in slot 1, then row2 could be either slot 1 or 2 with equal chance. If you end up in slot 1 or 6 in row2, then row3 must be slot 1 or 5 respectively based on how the board is designed. This pattern then continues all the way down to the bottom.

For my workflow, I used generate rows to handle the different possibilities. To ensure all the numbers work out, I had to use a formula tool as well. Here is my final output:

Start	1	2	3	4	5
1	49%	33%	14%	4%	0%
2	33%	30%	22%	11%	4%
3	14%	22%	27%	22%	14%
4	4%	11%	22%	30%	33%
5	0%	4%	14%	33%	49%

This is why the best prize is always in the middle. If you put the best prize in slot 1, then 49% of the time, people could start in slot 1 and it would end in slot 1. But to finish in slot3, your best bet is to start in slot3 with only a 27% chance of ending where you want. Oddly enough (pun intended), if you randomly selected a starting slot, you would have equal odds of ending up in any bucket (i.e. you would have a 20% chance of ending up in any of the 5 ending slots).   

[alex]

Needless to say I learned a lot more about Pascal's Triangles with this week's challenge.  After some false starts I decided to use an iterative macro to get my results.  I'm posting my solution now, but will clean it up a bit, add details and then repost it.  My %'s are different than @patrick_digan so we'll need some additional responses to get some sort of consensus on the %'s.  There is a 13.4% chance of puck starting in the 4th slot and ending up in the 2nd bucket as per the picture.

Spoiler

I went with creating coordinates on a grid and then counting pathways to get to my Pascal Triangle. One of the tricky parts is bumping up against the edges. For this workflow 2 corresponds to the left most slot, 6 to the middle and 10 the slot on the far right.
StartResults
This is a view of the pathways if you started in slot 2.

workflowPlinko Macro

This was definitely a challenge worthy of being presented during Inspire17!

[NicoleJohnson]

Alright, @MattD - I will admit that I felt like it would be cheating to sit in on the Weekly Challenge session at Inspire this week. But then we started getting into Pascal's Triangles and API's and lions and tigers and bears... oh my. Not nice. Then @estherb47 and I spent the better part of the rest of Tuesday trying to figure out how to recreate Pascal's Triangle from scratch in Alteryx... So thanks for that :)

Anyway. Here's my solution! Bit of a different approach from @alex and @patrick_digan, I went with an app that prompts you for the number of Plinko slots & rows (so it's variable, you know, for bigger Plinko boards... because bigger boards = more cool prizes!). Then you select which slot you want to place your coin into, and it will spit out a report that tells you the probability of winning each prize. 

Spoiler

To achieve this, I needed to recreate Pascal's triangle, so I brought back "College Freshman Statistics Class Nicole" back from the dead. Then I generated a "template" for the columns I would need (one for each slot, with "spacers" between each slot and at each end). Then I filled in the first "1" based on the slot selected in the app prompts, and wrote that info to an Excel file.

Then I created an iterative macro that took one row at a time.and determined the values in each column based on the values in the previous row, using some pivoting and a multi-row tool. With every iteration, the new row is appended to the original Excel file, and then the next iteration starts with that new row when determining the values for the next, up to the number of rows needed based on prompt selections.



Finally, I had to turn that lovely Pascal's triangle into probability %'s, so I brought it back into the original workflow after the macro was finished running, and then calculated the probabilities and final results. Packaged it all up at the end in a report telling you the odds of landing in each of the slots based on your selection.

Things that could still be improved upon/lingering questions:
1. I'm not convinced it will work from a report display perspective for combos that have lots of rows AND lots of slots, since there is a chance if you put a coin into the slot on the far right end, it won't be physically possible to make it all the way to the left side if there aren't enough rows... but the way I renumbered the columns for presentation purposes has it starting at #1 for the "left" column in every case. If someone wants to tinker with that idea & has suggestions, please do share :)

2. I was trying to figure out a way in the reporting tools to have a row rule that would highlight the top value(s) so that you could visually see where the highest probability final slot(s) might be, but didn't really see where you could do something like that in the tools. Does anyone know of a trick for that type of conditional formatting?

Phew. 6 days in Vegas + Challenge 73 = skin AND brain are officially fried! Thanks for the great challenge!

PS. @JoeM - Look! I did an iterative macro!!... But I still want to pick your brain, because I'm afraid this may have just been an "I just accidentally clicked the right combo of things" situation... :)

Cheers, all!

NJ

[alex]

I've cleaned up the original workflow and macro plus added descriptions of the formulas and processes I used to get my results.  I also added options to change the number of slots and decision points (pegs).  I couldn't have done it without modeling it and testing my thought process in Excel.  I often have to first think about how I would create solutions in Excel to be able to create them in Alteryx.

Spoiler

I decided on a solution based on a grid pattern and counting paths.  I assigned a coordinate to each cell based on the row and column and gave it a value of 1.  The grid for the picture provided in the challenge would look like this. The green and yellow cells are drop slots and the red cells are the buckets in the bottom.  The grey cells are open spots and the white cells are the pegs (includes the top of the buckets since they can deflect left or right). 

If dropped in cell 106 (3rd slot) then it can only feed cells 205 and 207.  Cells 205 can only be feed from cell 104 and 106, so on and so on.  The workflow then assigns the cell 104 and 106 (subtracts 101 and 99 from 205) to the row that cell 205 is assigned to.  The macro starts at row 3 and looks up one row to pull in the data for the cells assigned to to them.  Cell 306 sums the path value from 205 and 207 (1+1=2).


The cells that are not available (you can't be at coordinate 203 if you are starting at 106), out of bounds (puck starts far left and may not reach far right bucket) or that are edges were filtered out in the workflow so there is nothing to add inside the macro.

The process after the macro is pretty straightforward.  I used the option of creating a Total Column inside the first cross tab tool to use in calculating the percentages.  I built in some ways to visually inspect the answers to make sure the model was working properly.  I audited it for various scenarios and I didn't find one that wasn't handled properly by the model.

Hope you find this solution helpful.  Thanks.

Alex

[LordNeilLord]

After driving myself crazy trying to build a macro to do this, I finally gave up and took an Old School approach...not my finest Alteryx workflow but I got there in the end...

Spoiler

[PhilipMannering]

_METHOD 1_

Use the old combinatorial mathematics to workout the number of combinations for each slot and then divide by the total number of possible routes (2^8) for the probability. If there were no boundary the number of combinations would be 8C4 (=8!/4!/4!) for any slot directly under the placement, 8C3 for the slot left of the placement, 8C2 for the slot 2 places left of the placement, etc. Taking account of the boundary you need to add the combinations of the slot opposite the current slot if no boundary existed. So, for example, a counter placed in position 1 and landing in slot 1 the total probability would be (8C4 + 8C3)/2^8 and for position 1 and slot 2 the probability is (8C5 + 8C2)/2^8... Unfortunately I found it tricky to generalise for all cases. So I resorted to Method 2...

_METHOD 2_

Build your own Plinko Game in Alteryx and run it multiple times...

I simulate the 50/50 probability of the ball falling one way or the other using a filter tool with the expression rand() <0.5 and just union it all together in the right places. I set it as an iterative macro to run 1000 times and run in this workflow (see below) to get...

This method is numerical so results are approximate!

[MattD]

In case anyone is taking the formulaic approach and needs help calculating factorials! https://gallery.alteryx.com/#!app/Wolfram-Alpha-Connector/59405f3ea18e9e02406720db

We're leaving this exercise open until next Monday, so keep the solutions coming! :)

[david_fetters]

So i went with a low labor approach after thinking about ways to needlessly over complicate this.  First challenge for me, so lets see how it plays out!

Spoiler

I started by observing that you can break the plinko board into 4 identical groups.  Each group has 5 slots of input, goes through 6 slots in the middle, and then outputs through 5 slots in the bottom.  The 5 output slots are also the 5 input slots of each next group. Then, assume that any input with two lower outputs (e.g. every input in the 5 slot row, and all but the two outside edges in the 6 slot row) has a 50% chance of going into either output, and that the edges in the 6 slot row have a 100% chance of going into the lower row.  This means something entering in slot 1 of a 5 slot row has a 75% chance of exiting in slot 1 of the next 5 slot row, and a 25% chance of exiting in slot 2, and something entering slot 2 has a 25% chance of exiting in slot 1, 50% chance of exiting in slot 2, and 25% chance of exiting in slot 3.

I built a formula in excel for each of the 4 groups of 5-6-5 slots, and used a random value for each group to determine what the output would be.  Each groups output is the input of the next group.  I actually built this two ways, one way performed all of the math in a single formula node, while the second way used four separate formula nodes.

By using create rows, I was able to run a simulation of 1 million attempts for each starting position per workflow run.  Method 1 took about 27.2 seconds, and method 2 took about 32 seconds.  For the sake of comparing my results with other attempts, in both of my methods, a chip dropped into slot 4 at the top had a 10.9% chance of landing in slot 2 at the bottom.

As a side note: i used only greater/less thans, without anything to capture random numbers equaling .25 or .75.  I set any of those results to a 9, and like to think of that as the weird case where it balances on the pin and doesn't fall all the way through.  It seems to happens once every 5 million runs!

Edit: hey i figured out spoilers!

[NicoleJohnson]

Turns out, I never posted my macro & workflow, and for some reason it won't let me edit my original post... soooo here you go. 

PS. Super impressed with the varied solutions so far on this one. I'm guessing Vegas lost some money on these savvy odds-calculating wizards last week... ;)