I need help w/ a formula

  • Thread starter Thread starter Timothy Consolo
  • Start date Start date
T

Timothy Consolo

I am taking a stat class. The professor gave a challenge to any one who
can come up with a furmula for the lottery. Is this possible to in xl?
I am pretty proficent in it, and I am fairy certain it can be done, but
the peramters by which to conceive such a formula are beyond my reach.
This formula needs to work using the mega ball format. It also needs to
take into account past numbers drawn w/ their statistical frequency of
appearance. Any help is welcome. Thank you. :confused:
 
The professor is making fun of you. Each drawing is totally independent of
previous results, so looking at the statistical frequency of earlier drawing
is a fools ploy.

The probability of winning the megamillions, for example, is the number of
combinations of sets of 5 numbers drawn from 52 (numbers 1 - 52 are possible
and non-repeating) times the number of power power/mega numbers (again, 52
as I recall ; 1 - 52).

=combin(52,5)*52

This produces 135,145,920

Just like is advertised on

http://www.megamillions.com/howtoplay/game_faq.asp pick how to win
 
I see your point, but what i got from her challenge was to create some
sort of number generator.
 
Here is what i have so far. What I need to do is come up with a number
gererator that will spit out a set of numbers based on each number's
statistical frequency of past appearance.

Total Number of Drawings: 104

First 5 numbers % of each number's fequency of draw over 104 drawings

35=1.90%
13=4.80%
25=4.80%
30=5.80%
38=5.80%
4=6.70%
11=6.70%
18=6.70%
24=6.70%
45=6.70%
46=6.70%
9=7.70%
12=7.70%
17=7.70%
19=7.70%
23=7.70%
33=7.70%
36=7.70%
41=7.70%
3=8.70%
21=8.70%
34=8.70%
42=8.70%
43=8.70%
44=8.70%
7=9.60%
22=9.60%
27=9.60%
29=9.60%
1=10.60%
5=10.60%
8=10.60%
14=10.60%
15=10.60%
37=10.60%
39=10.60%
47=10.60%
48=10.60%
50=10.60%
52=10.60%
6=12.50%
32=12.50%
40=12.50%
49=12.50%
2=13.50%
28=13.50%
20=14.40%
31=14.40%
16=15.40%
26=15.40%
51=15.40%
10=16.30%

Mega Ball same perameters as mentioned above

2=0.00%
9=0.00%
16=0.00%
19=0.00%
30=0.00%
41=0.00%
45=0.00%
49=0.00%
51=0.00%
4=1.00%
11=1.00%
12=1.00%
13=1.00%
17=1.00%
23=1.00%
25=1.00%
28=1.00%
36=1.00%
40=1.00%
42=1.00%
44=1.00%
47=1.00%
50=1.00%
6=1.90%
14=1.90%
15=1.90%
20=1.90%
24=1.90%
26=1.90%
27=1.90%
31=1.90%
37=1.90%
38=1.90%
46=1.90%
48=1.90%
1=2.90%
7=2.90%
18=2.90%
29=2.90%
32=2.90%
33=2.90%
39=2.90%
43=2.90%
5=3.80%
10=3.80%
22=3.80%
35=3.80%
52=3.80%
8=4.80%
21=4.80%
34=4.80%
3=6.70%
:confused:
 
Add a third column that accumulates the percentagies

35 1.90 .019
13 4.80 .067
25 4.80 .115

You can then use index and match to pick a number.

the challenge will be picking 9 numbers that are not redundant.

You have an additional challenge in that your numbers add up to greater than
500%

They should add to 100%.
 
so that third column will give my bias verus 100%
thankyou very much. One last question, wahat formula do I us to get
that 3rd column.
 
Assume i have a probability distribution

1 .05
2 .25
3 .15
4 .35
5 .20


and i want to randomly pick one of the numbers between 1 and 5 inclusive
using the probability distribution

In c1 I put

0
in C2 i put
=B1

in C3 put in
=B2+C2


then drag this down the column to C5

now in D1 put in a formula like

=INDEX(A1:A5,MATCH(RAND(),C1:C5,1),1)

A1:C5 looks like:

1 0.05 0
2 0.25 0.05
3 0.15 0.3
4 0.35 0.45
5 0.2 0.8
 
i tried the equation and then thought this might eliminat duplicates but
instead i get all zero's why?
=INDEX(A1:A5,MATCH(RAND(),C1:C5,1),1)*(D1<=D2:D5)*(D1>=D2:D5)
 
Back
Top