Lottery simulation in Excel

Contents

The lottery is not a hunt for luck,

it’s a hunt for losers.

With enviable regularity (and more often lately), people write to me asking for help in various calculations related to lotteries. Someone wants to implement their secret algorithm for selecting winning numbers in Excel, someone wants to find patterns in the numbers that have fallen out of past draws, someone wants to catch the organizers of the lottery in a dishonest game.

В этой статье мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие из которых, кстати, могут пригодиться и в более прозаических рабочих ситуациях.

Task 1. Probability of winning

Let’s take the classic Stoloto 6 out of 45 lottery as an example. According to the rules, only those who guessed all 10 numbers out of 6 receive a super prize (45 million rubles or more if the balance of the prize fund has accumulated from previous draws). If you guessed 5, you will receive 150 thousand rubles, if 4 – 1500 rubles. , if 3 numbers out of 6, then 150 rubles, if 2 numbers – you will return 50 rubles spent on the ticket. Guess only one or none – get only endorphins from the game process.

The mathematical probability of winning can be easily calculated using the standard function NUMBERCOMB (COMBINE), which is available in Microsoft Excel for such a case. This function calculates the number of combinations of N numbers out of M. So for our “6 out of 45” lottery it would be:

=ЧИСЛКОМБ(45;6)

… which is equal to 8, the total number of all possible combinations in this lottery.

If you want to calculate the probability for a partial win (2-5 numbers out of 6), then you will first have to calculate the number of such options, which is equal to the product of the number of combinations of guessed numbers out of 6 by the number of unguessed numbers out of the remaining (45-6) = 39 numbers. Then we divide the total number of all possible combinations (8) by the received number of wins for each option – and we get the winning probabilities for each case:

By the way, the probability, for example, of dying in a plane crash in Our Country is estimated at about 1 in a million. And the probability of winning in a casino at roulette, betting everything on one number is 1 to 37.

If all of the above did not stop you and you are still ready to play further, continue.

Task 2. Frequency of occurrence of each number

To begin with, let’s determine with what frequency certain numbers fall out. In an ideal lottery, given a sufficiently large time interval for analysis, all balls should have the same probability of being in the winning sample. In reality, the design features of the lottery drum and the weight-shape of the balls can distort this picture, and for some balls the probability of falling out may well be higher/lower than for others. Let’s test this hypothesis in practice.

Let’s take, for example, data on all 2020 out of 21 lottery draws that took place in 6-45 from the website of their organizer Stoloto, designed in the form of such a “smart” table, convenient for analysis, with the name tabArchive Circulation. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

To calculate the frequency of occurrence of each number, use the function COUNTIF (COUNTIF) and add a function to it TEXT (TEXT)to add leading zeros and asterisks before and after to single-digit numbers, so that COUNTIF looks for the occurrence of a number anywhere in the combination in column B. Also, for greater clarity, we will build a chart by results and sort the frequencies in descending order:

On average, any ball should fall 1459 draws * 6 balls / 45 numbers = 194,53 times (this is exactly what is called in the statistics математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые (10, 21, 6…) наоборот заметно реже (-15%), чем основная масса. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.

Task 3. What numbers have not been drawn for a long time?

Another strategy is based on the idea that with a sufficiently large number of draws, sooner or later each number from all available from 1 to 45 should fall out. So if some numbers have not appeared among the winners for a long time (“cold balls”), then it is logical to try bet on them in the future.

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за 2020-21 год по убыванию даты и использовать функцию MORE EXPOSED (MATCH). It will search from top to bottom (i.e. from new to old runs) to search for each number and give out the serial number of the run (counting from the end of the year to the beginning) where this number was last dropped:

Задача 4. Генератор случайных чисел

Another game strategy is based on eliminating the psychological factor when guessing numbers. When a player chooses numbers by making his bet, he subconsciously does this not entirely rationally. According to statistics, for example, numbers from 1 to 31 are chosen 70% more often than the rest (favorite dates), 13 is chosen less often (damn dozen), numbers containing the “lucky” seven are more often chosen, etc. But we are playing against a machine (lottery drum) for which all numbers are the same, so it makes sense to choose them with the same mathematical impartiality in order to equalize our chances. To do this, we need to create a generator of random and – most importantly – non-repeating numbers in Excel:

To do this:

Let’s create a “smart” table named tableGenerator, where the first column will be our numbers from 1 to 45.
In the second column, enter the weight for each number (we will need it a little later). If all numbers are equally valuable to us and we want to choose them with equal probability, then the weight can be set equal to 1 everywhere.
In the third column we use the function SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) a new set of 45 random numbers will be generated, taking into account the weight for each of them.
Let’s add a fourth column, where using the function RANK (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

Now it remains to make a selection of the first six numbers by rank 6 using the function MORE EXPOSED (MATCH):

При нажатии на клавишу F9 the formulas on the Excel sheet will be recalculated and each time we will get a new set of 6 numbers in green cells. Moreover, the numbers for which a larger weight was set in column B will receive a proportionally higher rank and, thus, appear more often in the results of our random sample. If the weight for all numbers is set to the same, then all of them will be selected with the same probability. This way we get a fair and unbiased random number generator of 6 out of 45, but with the ability to make adjustments to the randomness of the distribution if necessary.

If we decide to play in each draw not with one, but, for example, with two tickets at once, in each of which we will choose non-repeating numbers, then we can simply add additional lines from the bottom to the green range, adding 6, 12, 18, etc. to the rank. d. respectively:

Task 5. Lottery Simulator in Excel

As an apotheosis of this whole topic, let’s create a full-fledged lottery simulator in Excel, where you can try out any strategies and compare the results (in optimization theory, something similar is also called the Monte Carlo method, but it will be simpler for us).

To make everything as close to reality as possible, imagine for a moment that it is January 1, 2022 and we have this year’s draws ahead of us, in which we plan to play. I entered the real dropped numbers in the table tablTiraži2022, separating the additionally drawn numbers from each other into separate columns for the convenience of subsequent calculations:

On a separate sheet Game create a blank for modeling in the form of a “smart” table with the name tabIgra the following form:

Here:

In the yellow cells above, we will set for the macro the number of draws in 2022 in which we want to participate (1-82) and the number of tickets we play in each draw.
The data for the first 11 columns (AJ) will be copied by the macro from the 2022 draw sheet.
Data for the next six columns (KP) the macro will take from the sheet Generator, where we have implemented a random number generator (see problem 4 above).
In column Q, we count the number of matches between the dropped numbers and those generated using the function SUMPRODUCT (SUMPRODUCT).
В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если выиграли, то приз — 50 р. за билет)
In the last column S, we consider the overall result of the entire game as a cumulative total in order to see the dynamics in the process.

And to revive this whole structure, we need a small macro. On the tab developer (Developer) choose a team Visual Basic or use keyboard shortcut Alt+F11. Then add a new empty module via the menu Insert – Module and enter the following code there:

Sub Lottery()      Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer            'объявляем переменные для ссылки на листы      Set wsGame = Worksheets("Игра")      Set wsNumbers = Worksheets("Генератор")      Set wsArchive = Worksheets("Тиражи 2022")            iGames = wsGame.Range("C1")     'количество тиражей      iTickets = wsGame.Range("C2")   'количество билетов в каждом тираже      i = 5                           'первая строка в таблице таблИгра            wsGame.Rows("6:1048576").Delete   'очищаем старые данные            For t = 1 To iGames          For b = 1 To iTickets              'копируем выигравшие номера с листа Тиражи 2022 и вставляем на лист Игра              wsArchive.Cells(t + 1, 1).Resize(1, 10).Copy Destination:=wsGame.Cells(i, 1)                            'копируем и вставляем специальной вставкой значений сгенерированные номера с листа Генератор              wsNumbers.Range("G4:L4").Copy              wsGame.Cells(i, 11).PasteSpecial Paste:=xlPasteValues                            i = i + 1          Next b      Next t    End Sub

It remains to enter the desired initial parameters in the yellow cells and run the macro through Developer – Macros (Developer — Macros) or keyboard shortcut Alt+F8.

For clarity, you can also build a diagram for the last column with a cumulative total, reflecting the change in the money balance during the game:

Comparison of different strategies

Now, using the created simulator, you can test any game strategy on real draws in 2022 and see the results that it would bring. If you play 1 ticket in each draw, then the overall picture of the “plum” looks something like this:

Here:

Generator is a game where in each draw we choose random numbers created by our generator (with the same weight).
Favorites is a game where in each draw we use the same numbers – those that have most often fallen out in draws over the past two years (27, 32, 11, 14, 34, 40).
Outsiders – the same, but we use the most rare drop-down numbers (12, 18, 26, 10, 21, 6).
Cold – in all draws we use numbers that have not fallen out for a long time (35, 5, 39, 11, 6, 29).

As you can see, there is no big difference, but the random number generator behaves a little better than the other “strategies”.

Можно также попробовать играть большим количеством билетов в каждом тираже, чтобы перекрыть большее количество вариантов (иногда для этого несколько игроков объединяются в группу).

Playing in each draw with one ticket with randomly generated numbers (with the same weight):