Probability Win x Races, Win Exactly x Races, Etc.

[RiTSo _]

Situation:

There are a maximum of eight 'races', in each race there are a number
of different drivers. The problem we are looking at focuses on one
particular driver and their chance of winning x races, etc.

Throughout the day the driver may or may not drive in all of the races.

Requirements:

From the above the following probabilities are required:

No Heads
Exactly 1 Win
Exactly 2 Wins
Exactly 3 Wins
Exactly 4 Wins
Exactly 5 Wins
Exactly 6 Wins
Exactly 7 Wins
Exactly 8 Wins

At least 1 Win
At least 2 Wins
At least 3 Wins
At least 4 Wins
At least 5 Wins
At least 6 Wins
At least 7 Wins

Problem:

Up to now it has been done a bit manually and only for the 'exactly'
situation. By that I mean it has been worked out as 'Win Race 1/Lose
Others', 'Win Race 2/Lose Others', 'Win Race 3/Lose Others'.....etc.,
then the same for Win Race 1 and Race 2, Win Race 2 and Race 3, but
lose others, problem is by the time you get to four races the number of
combinations becomes rather large.

I'm sure doing it the above way would give the results required but
also quite confident that there must be an easier way to solve the
issue in excel.

Also, to add another problem, the driver might not drive in all eight
races. Therefore there has to be some way in the calculation to factor
in that the driver is not in all eight races. So if they drove in six
the 'exactly 7 wins' and 'exactly 8 wins' would not be an option, but
the other options below this would factor in that there were not eight
races and adjust accordingly.

Would appreciate any help on this and/or pointers in the right
direction for formulas or tutorials which may help.

Thanks.

 

[Bernie Deitrick]

RiTSo,

Do you consider the races to be, basically, random drawings?

HTH,
Bernie
MS Excel MVP

 

[RiTSo]

Bernie Deitrick wrote:

:: Do you consider the races to be, basically, random drawings?

Hi Bernie,

Not too sure how you mean by random drawings?

Do you mean that the chances could differ race to race? Unlike a coin
toss were the probability would be the same?

 

[Bernie Deitrick]

Basically, I need to know if there is any way to predict the outcome with
more accurately than just guessing. If there are 6 racers, does each have
an equal chance of winning, or are some racers so much better that the
weaker racers essentially have 0 chance of winning. Think of rolling fair
dice, or flipping a fair coin, versus an olympic sprinter racing with a
preschooler... Obviously, the chance will change as the number of racers
change, but if there is a skill or 'strength' rating that affects the
outcome, that will determine how the problem might be addressed - kind of
like a handicap in golf, or in bowling.

Bernie

 

[RiTSo]

Bernie Deitrick wrote:

:: Basically, I need to know if there is any way to predict the
outcome
:: with more accurately than just guessing. If there are 6 racers,
:: does each have an equal chance of winning, or are some racers so
:: much better that the weaker racers essentially have 0 chance of
:: winning. Think of rolling fair dice, or flipping a fair coin,
:: versus an olympic sprinter racing with a preschooler... Obviously,
:: the chance will change as the number of racers change, but if there
:: is a skill or 'strength' rating that affects the outcome, that will
:: determine how the problem might be addressed - kind of like a
:: handicap in golf, or in bowling.

Bernie,

Although there are a number of drivers in the race we are looking at
just one and their chances of winning/not winning. So if they have a
75% chance of winning then they have a 25% chance of losing (one of
the other drivers wins). So although there are a number of drivers,
the outcomes we are looking at only have two possibilities, win or
lose. As I stated in the original question though, there may be
particular times when the driver in question does not compete in a
race.

The chance of the driver winning will already be determined, it's the
totalling up all the races and getting the results I am struggling
with.

Thanks.

 

[Bernie Deitrick]

I understand that there are only two outcomes for your particular driver:
either (s)he wins or looses. That doesn't affect the probability of
winning. If there are four drivers, and each has an equal probability of
winning, then there are four possible outcomes of equal (0.25) likelihood:
Driver 1 wins, all others lose; Driver 2 wins, all other lose.... etc.

So, is your driver's likelihood of winning determined by the number of
racers (a one in six chance of winning when there are six total drivers) or
is it some other basis, determined by historical results, perhaps - For
example, your driver has won 43% of 4 driver races, so that is the
probability that you want to use....

In either case, because of the nature of probabilities, the probability of
losing is always (1 - the prob of winning), where prob are always between 0
and 1. For the simple case, you could set the Pwin to 1/Nracers, and so
Plosing is (N-1)/N.

But Excel can handle only a few cases easily: Prob. of winning NONE, ALL,
exactly ONE, and all but one. The Prob of winning them ALL is the Product
of all the Pwins, the Prob of winning NONE is the Product of all the Plose,
and the Prob of winning exactly one and of winning all but one is a bit more
complex but can be done with one array entered PRODUCT formula. The other
Prob's (Winning exactly two, etc) require combinations that must be handle
within VBA (for cases were the Probabilities are not the same for every
race).

So, let me know...

Bernie

 

[Mike Middleton]

RiTSo -

For a specific total number of "trials," use the BINOMDIST worksheet
function to determine number of "successes" or the cumulative number.

To include uncertainty about the total number of "trials," you will have to
specify the probability of each total number, and you will have to use
worksheet formulas to weight the BINOMDIST results.

- Mike
http://www.mikemiddleton.com

 

[Bernie Deitrick]

Mike,

Can BINOMDIST take into account varying probability?

Thanks,
Bernie

 

[Mike Middleton]

Bernie -

For the binomial distribution, the underlying process must have trials that
(1) have dichotomous outcomes, (2) have constant probability of "success,"
and (3) are independent.

- Mike

 

[RiTSo]

Bernie,

See below:

:: So, is your driver's likelihood of winning determined by the number
:: of racers (a one in six chance of winning when there are six total
:: drivers) or is it some other basis, determined by historical
:: results, perhaps - For example, your driver has won 43% of 4 driver
:: races, so that is the probability that you want to use....

The probability will be based on historical date, etc. All drivers
have a different chance of winning bearing in mind their previous
form, skill, etc.

:: In either case, because of the nature of probabilities, the
:: probability of losing is always (1 - the prob of winning), where
:: prob are always between 0 and 1. For the simple case, you could
set
:: the Pwin to 1/Nracers, and so Plosing is (N-1)/N.
::
:: But Excel can handle only a few cases easily: Prob. of winning
NONE,
:: ALL, exactly ONE, and all but one. The Prob of winning them ALL is
:: the Product of all the Pwins, the Prob of winning NONE is the
:: Product of all the Plose, and the Prob of winning exactly one and
of
:: winning all but one is a bit more complex but can be done with one
:: array entered PRODUCT formula. The other Prob's (Winning exactly
:: two, etc) require combinations that must be handle within VBA (for
:: cases were the Probabilities are not the same for every race).


That's the problem. We know the way it can be worked out, it's just
that when you actualy come to putting this into the spreadsheet the
numbers of different formulas/calculations when you start adding in
the extra races gets a bit worrying!

 

[RiTSo]

Mike Middleton wrote:

:: For a specific total number of "trials," use the BINOMDIST
worksheet
:: function to determine number of "successes" or the cumulative
number.

Looking at the details of the BINOMDIST function it would seem to be
along the right line for what we require, although I have to admit
that it was somewhat over my head!

I think I might have to have a read over it and see what I can do.

Thank you for the pointer.

 

[RiTSo]

Mike Middleton wrote:

:: For the binomial distribution, the underlying process must have
:: trials that (1) have dichotomous outcomes, (2) have constant
:: probability of "success," and (3) are independent.

Arrrrr, this might be the stumbling block.

Although parts 1 & 3 will be correct point 2 will not be. In Race 1
the driver might have a 0.25 probability of winning, but come Race 2
the driver might have changed to a 0.8 probability of winning.

 

[Lori]

I think you would need a math or stats program for this...

If the probabilities of winning individual races are different
(0.2,0.3,..,0.5) then the probability of winning n races p(n) would be
given by:

(0.8 + 0.2x)(0.7 + 0.3x)...(0.5 + 0.5x) = 1 + p(1)x + p(2)x² + p(3)x³
+ ...

 

[iandjmsmith]

I think you would need a math or stats program for this...

If the probabilities of winning individual races are different
(0.2,0.3,..,0.5) then the probability of winning n races p(n) would be
given by:

(0.8 + 0.2x)(0.7 + 0.3x)...(0.5 + 0.5x) = 1 + p(1)x + p(2)x² + p(3)x³
+ ...

Mike Middleton wrote::: For a specific total number of "trials," use theBINOMDIST
worksheet
:: function to determine number of "successes" or the cumulative
number.

Looking at the details of theBINOMDISTfunction it would seem to be
along the right line for what we require, although I have to admit
that it was somewhat over my head!

I think I might have to have a read over it and see what I can do.

Thank you for the pointer.

Here's a simple VBA routine for just that.

If r is the range holding the probabilities of winning each individual
race and it is assumed that winning race i is independent of winning
race j, then testc3(r,upto) will return the probability of winning
from 0 to upto races.

Public Function testc3(r As range, upto As Long) As Double
Dim dist() As Double, p As Double, q As Double
Dim i As Long, j As Long
Dim Val
ReDim dist(0 To r.Count)
dist(0) = 1#
i = 0
For Each Val In r.Value
p = Val
q = 1# - Val
i = i + 1
dist(i) = p * dist(i - 1)
For j = i - 1 To 1 Step -1
dist(j) = q * dist(j) + p * dist(j - 1)
Next j
dist(0) = q * dist(0)
Next
testc3 = dist(0)
For i = 1 To upto
testc3 = testc3 + dist(i)
Next i
End Function

Example. If a1:a3 holds the numbers 0.1 0.2 0.3 then testc3(a1:a3,1)
returns .902


Ian Smith

 

[Harlan Grove]

There are a maximum of eight 'races', in each race there are a number
of different drivers. The problem we are looking at focuses on one
particular driver and their chance of winning x races, etc.

Throughout the day the driver may or may not drive in all of the races.

....

If you have the odds for every combination of drivers, say, historical
wins for each driver, and odds for a particular driver equal to their
own historical wins divided by the sum of historical wins for all
drivers in that race, then you have the probabilities of any of those
drivers winning that race (assuming all of the drivers have some
historical wins). If winning any race is independent of winning other
races, and driver A's odds of winning races i and j were p[A,i] and
p[A,j], respectively, then driver A's odds of winning both races would
be p[A, i] * p[A, j].

If the drivers in any given race were a random sample of all the
drivers, then each driver's probability of winning any given race is
the average of that driver winning given any combination of other
drivers. Once you have those average probabilities, you can assume a
binomial process. OTOH, if you know which drivers are in which races,
you have to calculate the probabilities for each race separately, and
you have to calculate the probabilities of all wining/losing
combinations separately. If driver A is in races 1, 2 and 3, the
probability of driver A winning at least 2 races is

p[A,1] p[A,2] (1 - p[A,3]) + p[A,1] (1 - p[A,2]) p[A,3] + (1 - p[A,1])
p[A,2] p[A,3]
+ p[A,1] p[A,2] p[A,3]