# How to Build Lottery Prediction Algorithm in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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.

## How to Build Lottery Prediction Algorithm in Excel (With Easy Steps)

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 the 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

### STEP 3: Predict the Most Frequent Numbers

• 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.

• 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. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF