In this article, we will learn **how to build a lottery prediction algorithm in Excel**. In **Excel,** we can easily predict a lottery number by creating an algorithm. As it is built on probability it is not **100**% accurate but you can guess the combination of numbers we want to buy. So, without delay, letâ€™s explore the solutions.

**Table of Contents**hide

## Download Practice Workbook

To practice by yourself, download the following workbook.

## How Does Lottery Prediction Algorithm Work?

An algorithm that assists in selecting the appropriate number combinations is known as a lottery prediction algorithm. This algorithm will generate a large number of random numbers, and we will predict the numbers that are most likely to occur based on the random numbers. Here, we will repeat the tasks multiple times to increase the probability of prediction.

## Step-by-Step Procedures to Build Lottery Prediction Algorithm in Excel

To demonstrate the procedure of building a lottery prediction algorithm in Excel, we will use **2** columns. In **1st **column, there will be a series of lottery numbers. And in **2nd **column, we will calculate the weightage value of the serial number. Weightage value means the probability of selecting the serial number. So, we will select the serial according to the probability of selecting this number. So, follow the steps below to learn the procedure of building a lottery prediction algorithm in Excel.

### STEP 1: Make Dataset Ready

- Firstly, insert the serial number of the lottery.
- You can insert the values in the
**Serial Number**column manually. - But, we will do it automatically.
- To do so, insert
**1**and**2**serially in the**B5**and**B6Â**cells.

- Next, drag down the
**Fill Handle**to fill all the cells serially.

- We want to do our task up to
**50**number serial. - For this reason, we will drag down up to the
**B54Â**cell. - So, we have successfully inserted up to
**50**serial numbers. - You can select the range according to your number of tickets.

### STEP 2: Add Functions for Prediction

- In the following step, we will insert a formula to calculate the probability of the serial number.
- To do so, select the
**C5**cell and write the following formula using**COUNTIF**function:

`=COUNTIF($K$56:$AO$6000,B5)`

Here, the formula counts the occurrence of serial number **1** of the **B5** cell in the range of **K56** to **AO6000 **cells. We will create random numbers in the range of **K56** to **AO6000 **cells and count the number of occurrences. Yet we have not inserted random numbers. Thatâ€™s why the result is showing **0.**

- After that, drag down the
**Fill Handle**to fill all the cells with the formula.

- As a result, all the cells of the
**B**column are showingÂ**0.** - After that, select the
**BÂ**column. - After selection, press the
**HomeÂ**tab. - Then, press on the drop-down menu of
**Conditional Formatting**from the**StylesÂ**group. - Instantly, a wizard will come out.
- From the wizard, select
**Top/Button Rules**>>**Above Average**.

- Instantly, a dialog box will pop up.
- Now, select
**Light Red Fill**from the drop-down menu. - That means that the lottery numbers with a weightage of above average will show
**Light RedÂ**color. - Also, there are many options presented here.
- Here, you can choose as per your demand.
- Then, press
**OK**to proceed.

- In the next step, we will insert random numbers between
**1**to**50**. - So, select the
**K55**column and write the following formula to insert a random number.

`=RANDBETWEEN(1,50)`

Here, **the RANDBETWEEN function** will insert any random integer number between **1** to **50**.

- Now, copy the
**K55**cell as we want to paste the formula into the other cells. - To copy, select the
**K55**cell and press**Ctrl**+Â**C.** - After that, go to the
**Name Box**. - The
**Name Box**is a box situated left side of the**formula bar**where the cell number is shown. - In the
**Name Box**, write the following formula to select the range:

`K56:AO6000`

- As a result, we have selected the range.

- After selection, paste the formula in the range.
- Here, you can paste the formula by simply pressing
**Ctrl**+**V**. - As a result, random numbers will be generated in this range.

- After that, we need to delete the formula in the range and replace the formula with values.
- If you do not do so, then the values will be changed after every refresh.
- To do so, select the
**K56:AO6000**range and copy by pressing**Ctrl**+Â**C.** - After that, we will paste the range in the same range as the
**valueÂ**format. - So, click on the
**K56**cell and right-click on the mouse. - Instantly, a dialog box will open up.
- On the right side of the
**Paste Special**option, there is a drop-down menu available. - So, press the icon and select
**Paste Value**from there. - As a result, the formula of the random number is replaced by generated random numbers.

**Read More: How to Create Betting Algorithm in Excel (with Easy Steps)**

### STEP 3: Predict the Most Frequent Numbers

- In the following step, return to the
**CÂ**column. - Now, we can observe the
**red cells**. - Here,
**red cells**indicate that this serial number is above average. - That means that the probability of selecting these red cells is greater than normal cells.

### STEP 4: Repeating Process by Using Highlighted Numbers

- In the next step, we will create a similar
**ExcelÂ**worksheet. - In this new worksheet, there will be information about highlighted numbers.
- So, we will copy the
**Serial Number**and**Lottery Number**by pressing**Ctrl**+Â**C.** - After copying, we will paste these columns now.
- Therefore, click on the
**B5**cell under the**Serial NumberÂ**header. - Then, right-click on the mouse and a new menu bar will open up.
- After that, click on the icon beside
**Paste Special**and a wizard will open up. - Then, select the
**Values and Source Formatting**icon to paste the cells keeping the same format.

- As a result, we have successfully pasted the
**2**columns into the new**ExcelÂ**sheet.

- But, we need to keep only the red highlighted serial numbers.
- We have to do the work manually.
- In the
**Serial Number**column, we have kept the most probable serial numbers. - Then, delete the next column.
- For deleting the column, select the elements of the column and press
**Delete**on the keyboard. - Similarly, we will do the same calculations for this newly formed
**Excel Sheet**. - So, we will insert a formula to calculate the probability of the serial number.
- To do so, select the
**C5**cell and write the following formula:

`=COUNTIF($K$56:$AO$6000,B5)`

- After that, drag down the
**Fill Handle**to fill all the cells with the formula.

- As a result, all the cells of the
**B**column are showingÂ**0.**

- In the next step, select the
**BÂ**column. - After selection, press the
**HomeÂ**tab. - Now, press on the drop-down menu of
**Conditional Formatting**from the**StylesÂ**group. - Instantly, a wizard will come out.
- From the wizard, select
**Top/Button Rules**>>**Above Average**.

- Instantly, a dialog box will pop up.
- Now, select
**Light Red Fill**from the drop-down menu. - Then, press
**OK**to proceed. - After that, we will insert random numbers between
**1**to**50**. - So, select the
**K55**column and write the following formula to insert a random number.

`=RANDBETWEEN(1,50)`

- Press
**Enter**to see the result.

- Now, copy the
**K55**cell by pressing**Ctrl**+Â**C.** - After that, go to the
**Name box**. - The
**Name Box**is a box situated left side of the**formula bar**where the cell number is shown. - In the
**Name Box**, write the following formula to select the range:

`K56:AO6000`

- As a result, we have selected the range.
- After selection, you need to paste the formula by simply pressing
**Ctrl**+**V**. - As a result, random numbers will be generated in this range.

- After that, we need to delete the formula in the range and replace the formula with values.
- If you do not do so, then the values will be changed after every refresh.
- To do so, select the
**K56**:**AO6000**range and copy by pressing**Ctrl**+Â**C.** - After that, we will paste the range in the same range as the
**valueÂ**format. - So, click on the
**K56**cell and right-click on the mouse. - Then, on the opened menu bar, there is a drop-down menu available on the right side of the
**Paste Special**option - So, press the icon and select
**Paste Value**from there. - As a result, the formula of the random number is replaced by generated random numbers.

- In the following step, return to the
**CÂ**column. - Now, we can observe the
**red cells**. - Here,
**red cells**indicate that this serial number is above average. - That means that the probability of selecting these red cells is greater than normal cells.

- Similarly, we need to make
**2**Excel sheets step-by-step and do the same calculation. - To do so, select the elements of the
**B**and**CÂ**columns. - In the next step, we will create a similar
**ExcelÂ**worksheet. - In this new worksheet, there will be information about highlighted numbers.
- So, we will copy the
**Serial Number**and**Lottery Number**by pressing**Ctrl**+Â**C.**

- After copying, we will paste these columns now.
- Therefore, click on the
**B5**cell under the**Serial NumberÂ**header. - Then, right-click on the mouse and a new menu bar will open up.
- After that, click on the icon beside
**Paste Special**and a wizard will open up. - Then, select the
**Values and Source Formatting**icon to paste the cells keeping the same format.

- As a result, we have successfully pasted the
**2**columns into the new**ExcelÂ**sheet. - But, we need to keep only the red highlighted serial numbers.
- We have to do the work manually.
- In the
**Serial Number**column, we have kept the most probable serial numbers. - Then, delete the next column.
- For deleting the column, select the elements of the column and press
**Delete**on the keyboard. - Similarly, we will do the same calculations for this newly formed
**Excel Sheet**.

### Final Output

- After repeating the procedures
**2**more times, our output will look like the following figure. - Here,
**Serial Numbers****25**and**38**are the most probable numbers. - As a result, our selected tickets are
**25**and**38**.

## How to Determine Lottery Probability in Excel

You can easily determine the probability of a specific lottery by using **the COMBIN function**. The **COMBIN **function will find the total possible number of groups from the given values. Follow the instructions below.

**STEPS:**

- Here, column
**B**is our lottery ticket number. - And, column
**C**is our output lucky lottery ticket number. - To find the probability, select the
**D5**cell and write down the following formula:

`=COMBIN(B5:C5)`

- After writing, press
**Enter**to exit from the editing mode. - Now, the result is showing
**0**.

- After that, drag down the
**Fill Handle**to fill all the cells with the formula.

- As a result, we can observe the probability of a lottery number.
- Here, the result is shown in probability form.
- That means that the probability of the
**23**serial number is**490314**:**1**.

## Conclusion

In this article, we have demonstrated step-by-step procedures for **building a lottery prediction algorithm in Excel**. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the **ExcelDemy **website. Last but not least, please use the **comment section** below to post any questions or make any suggestions you might have.