Google Answers Logo
View Question
Q: Probabilities of Bingo Game in a Spreadsheet (formula) with explanations ( No Answer,   1 Comment )
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

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
There is no answer at this time.

Subject: Re: Probabilities of Bingo Game in a Spreadsheet (formula) with explanations
From: manuka-ga on 06 Nov 2006 20:26 PST
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 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.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy