how to create a sequence of combinations of numbers in excel

nuclear

New Member
Joined
May 4, 2015
Messages
3
As my final project for my statistics class, I want to prove that using a combination of the most frequent drawn lottery numbers can lead to a better chance of winning the lottery (Hot Numbers method). Now, the sequence ranges from 0-50 with only 5 numbers drawn. I will not use all of the drawn numbers form 0-50, I will only use those numbers that are drawn more frequently than others. Does anybody now how I can create a random sequence of number combinations from these most frequent used numbers? Now, I know that the list will be huge given all of the possible random combinations possible. So, I need to narrow them down to 15 and I need them to be random and non repetitive. Like, I can draw 6 twice in the five numbers I am allowed to draw. I can get 6-11-25-33-45 or 6-25-33-35-45, etc. but not 6-6-33-35-45.

I tried doing so by generating random drawings but they are not sequences of combinations. They are random numbers from the highest picked numbers. For instance, I get, 34-5-16-49-1. I first filled in the numbers that are most drawn on table C; assigne them absolute random values on A and give the absolute values on B using this formula: =RANK($A2,$A$2:$A$22)+COUNTIF(B$1:B1,B1)-1.

Does anybody have a better way to generate random sequence of combinations, rather than random combinations?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Does anybody have a better way to generate random sequence of combinations, rather than random combinations?
Welcome to the forum.

You are not headed toward a good grade in this class.
 
Upvote 0
You could treat this as a weighted lottery, shown here for 20 choose 6:

Row\Col
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
P​
Q​
R​
S​
T​
U​
V​
W​
X​
2​
Draw​
Ball Number​
Do Not Use​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​
16​
17​
18​
19​
20​
3​
19​
5​
9​
19​
13​
3​
14​
14​
1​
20​
19​
14​
4​
15​
7​
16​
4​
16​
11​
19​
4​
53​
5
0​
19​
24​
33​
52​
65​
68​
82​
96​
97​
117​
136​
150​
154​
169​
176​
192​
196​
212​
223​
5​
90​
10
0​
19​
24​
33​
52​
55​
69​
83​
84​
104​
123​
137​
141​
156​
163​
179​
183​
199​
210​
6​
131​
14
0​
19​
24​
33​
52​
55​
69​
83​
84​
103​
117​
121​
136​
143​
159​
163​
179​
190​
7​
82​
8
0​
19​
24​
33​
52​
55​
69​
83​
84​
103​
117​
121​
128​
144​
148​
164​
175​
8​
80​
11
0​
19​
24​
33​
52​
55​
69​
70​
89​
103​
107​
114​
130​
134​
150​
161​
9​
134​
19
0​
19​
24​
33​
52​
55​
69​
70​
84​
88​
95​
111​
115​
131​
142​

The numbers in row 3 are your perceived relative probabilities for each ball being drawn. The balls drawn for this example are shown in col C.

The workbook is at https://app.box.com/s/5a06b6he6sc9ce89bbfl, and the Win Once tab is the relevant sheet.

NB: The workbook has names in row 2 (Alan, Barb, Cain, ...), and I changed them to numbers -- but because of the way the dynamic ranges are defined, they MUST be formatted as text.
 
Upvote 0
Yeah I know! Luckily, I already have an A on the class, this is for extra points for the final. Anyways, thanks for the chart and help. I don't understand what the Draw column means? So I gave all the names a value (#) and I added the frequencies to those names. However, I get a lot of empty columns. BTW, what is the difference between =rand and =randbetween?
DrawWinnerDo Not UseAlanBarbCainDanaEricFranGaryHanaIvanJaneKentLeahMarkNinaOttoPeriQuinReneSethTinaUlisVeraWadeXana
182120182222181918232021232222181919191917171717
342Quin 01839597799121139158176199219240263285307325344363382401418435452
248Mark 01839597799121139158176199219240263285307 325344363382399416433
64Dana 01839597799121139158176199219 240262284 302321340359376393410
53Cain 01839 5981103121140158181201 222244266 284303322341358375392
199Leah 018 396183101120138161181 202224246 264283302321338355372
9Alan 018 396183101120138161 181203225 243262281300317334351
232Rene 0 21436583102120143 163185207 225244263282299316333
294Vera 0 21436583102120143 163185207 225244263280297314
232Seth 0 21436583102120143 163185207 225244263 280297
151Kent 0 21436583102120143 163185207 225244 261278
214Tina 0 21436583102120 143165187 205224 241258
251Xana 0 21436583102120 143165187 205 222239
133Jane 0 21436583102120 143165187 205 222
11Barb 0 21436583102 120142164 182 199
0Eric 022446281 99121143 161 178
52Hana 0224059 7799121 139 156
114Peri 022 40 5880102 120 137
101Otto 022 40 5880 102 119
77Nina 022 40 58 80 97
69Ulis 022 40 58 75
70Wade 022 40 58
9Fran 022 40
27Ivan 0 18
4Gary 0

<colgroup><col><col><col><col><col span="24"><col></colgroup><tbody>
</tbody>
 
Upvote 0
Look at line 4. Alan has tickets 0 to 17, Barb has 18 to 38, ...

342 is a random number between 0 and 400 and something. Whoever holds that ticket (Quin, in this case) wins that round.

However, I get a lot of empty columns.

Once a given player wins (or here, a given ball is drawn), it is removed so it can't be picked again. That's why the columns are become blank.

what is the difference between =rand and =randbetween

You read Help for both functions, and the part you don't understand is ...?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top