View Question
Q: Probabilities of Bingo Game in a Spreadsheet (formula) with explanations ( No Answer,   1 Comment )
 Question
 Subject: Probabilities of Bingo Game in a Spreadsheet (formula) with explanations Category: Science > Math Asked by: ignorantbliss-ga List Price: \$10.00 Posted: 10 Oct 2006 09:33 PDT Expires: 09 Nov 2006 08:33 PST Question ID: 772303
 ```A 5x5 Bingo card has random selections from 1-15, 16-30, ..., 51-75 appear in the 1st column, 2nd column, ..., the 5th column. The middle cell in the 3rd row of 3rd column is Free (automatic match). The house draws one number (ball) at a time from 1-75 at random without replacement. To win, one's card must have cells on one's card with the drawn numbers forming certain pattern, say, a horizontal line or a diamond shape. The material question to ask is what is the probability that any random Bingo card will match X spots on (OR by) Y drawn numbers. I need the probabilities in a spreadsheet X by Y. The probabilitiy given can be for that event (ball number Y) only or for the cumulative probability of matching BY that event. The answers are out there but I would like to have a nice explanation and the formula in a spreadsheet. See eg http://www.durangobill.com/Bingo.html```
 ```It's always a good idea to start off simply. Let's start with the question: How likely is it that 0, 1, ..., 24 cells on my card are filled in after Y numbers have been drawn? This is pretty easy to answer. After each drawing you will have either the same number of hits as the previous one, or one more. The probability of getting an additional hit is (24 - number of previous hits) / (75 - numbers previously drawn). Accordingly, the probability of a miss is (51 - numbers previously drawn + number of previous hits) / (75 - numbers previously drawn). To find the probability of having X hits after Y draws P(X, Y), we therefore have P(X,Y) = P(X-1,Y-1) * (24-X+1)/(75-Y+1) + P(X,Y-1) * (52+X-Y)/(75-Y+1) for any X, Y >=1. For Y=0 we obviously have P(X, 0) = 0 for X > 0 and P(0, 0) = 1; for X=0 we can use the same formula, but as we always have at least 0 hits we drop the first term. Now, to make things a little harder (and more relevant for bingo), the next question to ask is the probability of getting X cells from a *specific* set of N cells after Y draws? Actually, though, we can use exactly the same formula - we just have to reduce the number of possible "hit" cells from 24 to N. This will give us P(X,Y) = P(X-1,Y-1) * (N-X+1)/(75-Y+1) + P(X,Y-1) * (75-N+1+X-Y)/(75-Y+1) as the recurrence relation and this can easily be put in a spreadsheet. For the cumulative probability of having all N cells filled, sum probabilities for X>=N for each value of Y. If you want to proceed to the probability of getting any of several winning patterns, you can use the Include/Exclude technique. But be warned, this gets cumbersome for more than a few possible winning patterns. For example, the probability of getting any of the four edges can be expressed as P(top) + P(left) + P(bottom) + P(right) - P(top+right) - P(top+left) - P(bottom+right) - P(bottom+left) - P(top+bottom) - P(left+right) + P(top+left+right) + P(top+right+bottom) + P(top+left+bottom) + P(left+right+bottom) - P(top+left+right+bottom) If we let P(N) represent the probability of getting a specific set of N cells by the given turn, we can in turn express this as 4P(5) - 4P(9) - 2P(10) + 4P(13) - P(16). Note that we have to distinguish between the cases where we have two intersecting sides, and two non-intersecting sides. Obviously, expanding this to the case of getting a normal win (any row or column or diagonal, some of which require 4 cells and some 5) would lead to a very nasty expression (which could however be simplified as above - this would only need to be done once). But this is adequate for exploring the probability of getting other patterns. See http://home.iprimus.com.au/scarletmanuka/bingo.xls for a spreadsheet giving the formulas. You can experiment with the number of target cells and the number of cells on the board (though to increase either beyond the standard you'll have to add additional rows or columns). I've also included a sample column for the cumulative probability of having a given number (which you can change) of target cells filled after Y turns. Hopefully you can take it from there.```