Challenge #73: Plinko Probabilities

[JoeM]

After sitting in on the meeting at Inspire, my first challenge was to create a Pascal's Triangle building macro (maybe a future challenge?!?!?!?!). I'd love to see someone take this and see if it is useful to them!

[alex]

I think we all agree that this was a seriously hard challenge. Thanks @MattD!  I've enjoyed looking thru the other solutions and being Inspired by what I've seen.

I'm a little short on billable work right now so I took another crack at Plinko Probabilities.  In my previous post, I solved this challenge by using a pathways method and an iterative macro to calculate the number of paths to the bottom.  In this solution, I’m using the formula n! / r!(n-r)! to calculate Pascal’s triangle for the bottom row - no macro needed.  A number in Pascal’s triangle can be found by nCr (n Choose r) where n is the number of the row and r is the element in that row.  This workflow allows you to solve for a variety of ranges in number of slots and levels to the bottom.  Same goes for my previous workflow.  Again, the tricky issues come up with the edges and how to fold them back to get the correct numbers at the bottom row.  Excel to the rescue in terms of modeling the solution and then figuring out how to translate it to Alteryx.  It's amazing what you can accomplish using both programs.  The workflow does not have instructions or descriptions and currently only solves for one drop at a time. I tested it for many, but not all scenarios, and it provided the correct %'s each time.  I believe both these solutions are like my girls - beautiful, challenging, inspiring, frustrating (at times) and I’m equally proud of both.   Please feel free to PM me If you have any questions on either solution.

Note:  I added an output to a csv file to the app and is now working fine.

Spoiler

• Select number of slots, decision points and which slot to drop it in.
• Create table with row and column info.
• Create factorials using multi-row tool for the maximum range - if [n1]=1 THEN 1 ELSE [Row-1:Factorial]*[n1] ENDIF
• Use factorials and n! / r!(n-r)! formula to build out Pascal’s Triangle
• Use bottom row to start calculating odds.
• Adjust for columns that fall off the edge.
• Calculate adjusted totals, %, and output.
  
  Pascal's triangle to 14th levelOdds when starting in second of 10 slots and 14 pegsFactorial to 14th level
  Excel Modeling - green represents triangle when puck dropped in 5th out of 10 slots and 14 decision points (15 rows). 
  Visual PatternNumerical Pattern
  
  

[Auditor]

I actually learned a lot about Pascal's Triangle from this weekly challenge.

Calculation of the probabilities is based on the 9th line of the Triangle. Please see below result.

I am an Alteryx beginner, but hopefully the result is correct.

Interpretation:

If the chip starts in slot #4 (4th slot from the left hand side at the top):

• the probability of landing in position # 1 (first position from the left hand side at the bottom) is 3.125 %
• the probability of landing in position # 2 is 11.71875 %
• the probability of landing in position # 3 is 25 %
• the probability of landing in position # 4 is 38.28125 %
• the probability of landing in position # 5 is 21.875 % 

[SeanAdams]

Hey all,

My solution is a little different than those posted (seems that many folk have been inspired by Inspire to use Pascal's triangle). 

Here's the dope:

Spoiler

If you divide the board into left/right regions or lanes (picture below) then at each step as the ball progresses down, it either moves left or right by one lane.
I've also assumed that pin-rows come in pairs.   The first row has 1 more pin than the second row, and that's one row pair.    That is helpful because the behaviour of these two rows is different:
- the first row is controlled so any ball will be in lane 2,4,6 etc (always even numbers).
- as you transition to the second row, you can't go out of bounds (because even if you're in lane 2, you can go to lane 1)
- in the second row, you're always in odd-numbered lanes
- as you transition back to the first row again you DO have to check for going out of bounds - e.g. if you're in lane 1, and the random generator wants to go left, that would take you out of bounds.


So - if you iterate through every row of the board, using an iterative macro, and a random number generator that either goes left or right - then you can put several thousand cases through this and get a reasonably robust answer.

Here's the iterative macro piece:




When you call this from a harness - you can specify the number of rows; columns; where the goal chute is; and how many balls to drop to test this

[MattD]

Here's a solution!

Spoiler

[estherb47]

Still working on the flexibility of my solution -- I'd like it to be an app where the user can insert the # columns and # rows. But, for now, this works for the 9 row, 5 column Plinko board that was at Inspire 17.

A little behind, but 75 achieved!!! Now to catch up to 76....

[ggruccio]

Struggled to understand this one - tried a macro - then tried multiple iterations using a formula.  Assumed a pattern of alternating 5 and 6 slots.

12345

123456

12345

I guess the answer depends on how many iterations it runs thru....

[samN]

I guess i approached this slightly differently than others. Used a few nested macros.

Spoiler

Used one to simulate a direction choice(right or left) and then counting the number of times that a chip went a certain way. Simple lookup table that finds an end value based on number of times it goes a certain way and the start location. Then just wrapped that in a loop that goes a thousand times and then simple summarize.  My outermost macro gives the ability to choose the starting gate and then calculates the probability of landing at each end gate. 

Fun stuff!

[jasperlch]

Solution attached.

[Natasha]

This is by far is the most difficult challenge I've done. I struggled a lot with math and I got stuck trying to to find a more visual solution, and it is hard to top @PhilipMannering's beautifully constructed Plinko board. 

Spoiler

I first went down similar path as @alex and @SeanAdams with constructing a visual board with possible and impossible moves of the ball. Since I wanted to provide a more dynamic solution, I got stuck trying to figure out some dynamic combination of multi-field and multi-row formulas. I massively overcomplicated everything and put this challenge on hold for a while. I didn't build an app but the input consists of just number of slots and rows, so it is easily changed if need be. 


At the end I got rid of columns, and calculated the path in one column with a multi-row formula, depending on a random  left/right direction with checks for the end of the board. 

My probabilities are a bit different from other's without smooth distributions of ~ 30%. However I did quickly checked my results in Tableau, and every chart reminds me of a histogram or a half-histogram, which makes sense to me.