# How to Create a Lottery Prediction Algorithm in Excel – Easy Steps

The sample dataset contains 2 columns. The 1st one with a series of lottery numbers and a 2nd column, in which the probability of selecting a serial number will be calculated.

### STEP 1: Create a Dataset

• Enter the serial number of the lottery in the Serial Number column (values can be manually inserted).
• To do it automatically,Â insert 1 and 2Â in B5 and B6.

• Dag down the Fill Handle to fill the rest of the cells.

• 50 is the last serial number.
• Drag the Fill Handle down to B54.
• The 50 serial numbers are inserted.
• Select the range.

### STEP 2: Add Functions for Predictions

• Enter a formula to calculate the probability of a serial number.
• Select C5 and enter the following formula using the COUNTIF function:

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

The formula counts the occurrence of serial number 1 in B5 in the range K56:AO6000.

• Drag down the Fill Handle to fill all the cells.

• All cells in column B are showingÂ 0.
• Select Â column B.
• Go to the HomeÂ tab.
• InÂ  Styles, choose Conditional Formatting.
• Select Top/Button Rules >> Above Average.

• In the dialog box will,Â  select Light Red Fill from the drop-down menu.
• Lottery numbers with a weightage above the average will show Light RedÂ color.
• Click OK.

• Enter random numbers between 1 to 50.
• Select Â column K55 and enter the following formula..

`=RANDBETWEEN(1,50)`

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

• Press Ctrl +Â C, to copyÂ  K55 and drag the Fill handle to fill the other cells.
• Go to Name Box on theÂ left side of the formula bar, where the cell number is shown.
• Enter the following formula to select the range:

`K56:AO6000`

• This is the selected range.

• Press Ctrl + V to paste the formula in the range.
• Random numbers will be generated.

• Delete the formula in the range and replace it with values.
• Select K56:AO6000Â  and copy by pressing Ctrl +Â C. Values will change after every refresh.
• Paste the range in the same range as the valueÂ format. Click K56 and right-click.
• In the dialog box, choose Paste Special.
• Select Paste Value.
• The formula is replaced with generated random numbers.

Read More: How to Create Betting Algorithm in Excel

### STEP 3: Predict the Most Frequent Numbers

• In column C,Â red cells indicateÂ that the probability of selecting these red cells is greater than normal cells.

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

• Open a new worksheet.
• Copy the Serial Number and Lottery Number columns by pressing Ctrl +Â C.
• Paste these columns.
• Click B5 under the Serial NumberÂ header.
• Right-click and chooseÂ Paste Special.
• Select Values and Source Formatting to paste the cells and keep the same format.

• This is the output.

• The Serial Number column keeps the most probable serial numbers.
• Press Delete to delete the second column..
• Enter a formula to calculate the probability of the serial number.
• Select C5 and enter the following formula:

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

• Drag down the Fill Handle to fill all cells with the formula.

• Cells in Â columnÂ B are showingÂ 0.

• Select column B.
• Go to the HomeÂ tab.
• In Styles, chooseÂ Conditional Formatting.
• Select Top/Button Rules >> Above Average.

• In the dialog box, select Light Red Fill from the drop-down menu.
• Click OK.
• To enter random numbers between 1 and 50: select K55Â  and enter the following formula.

`=RANDBETWEEN(1,50)`

• Press Enter to see the result.

• Copy K55 by pressing Ctrl +Â C.
• Go to Name box on theÂ left side of the formula bar where the cell number is shown.
• Enter the following formula to select the range:

`K56:AO6000`

• Paste the formula by pressing Ctrl + V.
• Random numbers will be generated in this range.

• Delete the formula in the range and replace it with values.
• Select K56:AO6000Â  and copy by pressing Ctrl +Â C. Values will change after every refresh.
• Paste the range in the same range as the valueÂ format. Click K56 and right-click.
• In the dialog box, choose Paste Special.
• Select Paste Value.
• The formula is replaced with generated random numbers

.

• InÂ column C., red cells indicateÂ that the probability of selecting these red cells is greater than normal cells.

• Create 2 Excel sheets.
• Select columns B and C .
• Create a similar ExcelÂ worksheet.
• In this new worksheet, copy the Serial Number and Lottery Number by pressing Ctrl +Â C.

• Click B5Â  under the Serial NumberÂ header to paste these columns.
• Right-click and choose Paste Special.
• Select Values and Source Formatting to paste the cells and keep the same format.

• In the Serial Number column, we have kept the most probable serial numbers.
• Delete the next column by pressing Delete.
• Follow the above procedure for the new Excel Sheet.

### Final Output

• After repeating the procedure twice, this will be the output.
• Serial Numbers 25 and 38 are the most probable numbers.

## How to Determine Lottery Probability in Excel

the COMBIN function will find the total possible number of groups from given values.

STEPS:

• Column B is our lottery ticket number.
• Column C is our output lucky lottery ticket number.
• To find the probability, selectÂ  D5 and enter the following formula:

`=COMBIN(B5:C5)`

• Press Enter.
• The result is 0.

• Drag down the Fill Handle to fill all cells with the formula.

• The probability of the 23 serial number is 490314:1.

## Related Articles

<< Go Back to Algorithm in ExcelÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF