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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
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.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
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.
 

nuclear

New Member
Joined
May 4, 2015
Messages
3
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>
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
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:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,027
Messages
5,466,114
Members
406,467
Latest member
bpat83

This Week's Hot Topics

Top