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

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.