Predict lotto numbers using excel

Have you ever thought of generating lottery numbers in Excel, or make a lottery number generator in Excel by yourself? In this article, I provide three methods for you to get it done easily.

This method will walk you through applying several Excel functions to generate lottery numbers. And you can do as follows:

**1**. In a blank worksheet, type column headers in Range A1:C1 as below screen shot shown:

**2**. Enter formulas in Column A, Column B, and Column C:

(1) In Cell A2, enter the formula =RAND() , and then drag the Fill handle to the range A2:A50.

(2) In Cell B2, enter the formula =RANK($A2,$A$2:$A$50)+COUNTIF(B$1:B1,B1)-1 , and then drag the Fill Handle to the Range B2:B50. (In the formula, A2 is the random number left to current cell, $A$2:$A$50 are all random numbers we got with the first formula, B$1 is the first cell in Column B, and B1 is the cell above current cell).

(3) In Range C2:C50, enter sequence numbers from 1 to 49.

Then you will get a table as left screen shot shown:

Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future! Read more… Free trial |

**3**. Find a blank range, type sequence numbers horizontally in a row as below screen shot shown:

**4**. Below the first sequence number of 1, enter the formula =VLOOKUP(G1,$B$1:$C$50,2,FALSE) into it, and the drag the Fill Handle to the Range you need.

**Note**: In this formula, G1 is the cell with the first sequence number of 1, $B$1:$C$50 are Unique Rank column and Ball Number column, 2 means find values in the second column of Range $B$1:$C$50.

Then you will get the lottery numbers as below screen shot shown:

**5**. It’s optional. To prevent the lottery numbers from changing automatically when current worksheet refreshes, you can click the **Formula** > **Calculation Options** > **Manual**.

##### Easily list all combination of several series of data in Excel

Kutools for Excel’s **List All Combinations** utility is designed to general all possible combinations from several given series of data, and list these combinations in a column as below screenshot shown. **Full Feature Free Trial 30-day!**

**Kutools for Excel **– Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! **Get It Now**

This method will guide you to apply a VBA macro to generate lottery numbers in Excel. Please do as follows:

**1**. Press **Alt** + **F11** keys to open the Microsoft Visual Basic for Applications window.

**2**. Click **Insert** > **Module** to open a new module window, and paste following VBA code into it:

**VBA: Lottery Number Generator in Excel**

**3**. Press the **F5** key or click the **Run** button to run this VBA macro.

**4**. Now in the popping up dialog box, specify a cell to output the lottery numbers, and click the **OK** button.

Then you will see 6 unique lottery numbers are generated and displayed in the specified cells.

If you have Kutools for Excel installed, its Insert Random Data utility can help you generate lottery numbers in Excel at once.

**Kutools for Excel **– Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! **Free Trial Now!**

**1**. Select 6 adjacent cells, such as A1:F1, and then click the **Kutools** > **Insert** > **Insert Random Data**.

**2**. Now the Insert Random Data dialog box comes out. Go to the **Integer** tab, type **1** in **From** box, type **49** in **To** box, check the **Unique values** option, and click the **OK** button. Now you will see 6 unique lottery numbers are generated and displayed in the selected cells immediately.

**Kutools for Excel **– Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! **Get It Now**

## Predict lotto numbers using excel

I have been working with the lotto for the last 3 years and there is a way to forecast with a 20% to 25% accuracy what the numbers will do.

Some people might think that is not possible, but I know for sure that there is a way for an educated guess of what numbers are supposed to appear.

So far I have been hitting:

80% of the time 2 out of 3 on Pick 3.

40% of the time 2 out of 4 on Pick 4.

20% of the time 3 out of 5 on Pick 5.

I have not hit the big pay-off “Yet” but it’s very promising for somebody that 3 years ago would not spend a penny on the lotto.

Good luck to everybody ( LUCK. )

Hi K.C. – those figures do look a little guessed – 80%, 40%, 20%.

The important thing here if you’re evaluating what you’re doing, is to know what you’re comparing with. What results would you expect from random luck. And how does that compare with what you’re getting. Otherwise you can get hit by the the infrequency illusion

It’s hard to comment further on the figures you’re quoting as there isn’t enough data here. I mean, it’s easy to 3 out of 5 every single time on Pick 5 – if you play a large enough wheeling system.

Try using Excel function LINEST(chronological list of the lowest numbers drawn), combined with SLOPE and Y-INTERCEPTION.

This way I predict 4-5 out of 6 numbers to be drawn. I also include adjacent numbers (i.e. if predicted number is 11, I include 10 and 12 also).

When done above for all 6 numbers, I get about 15-20 numbers to be wheeled.

I usually hit 4-6.

Hey Al. I wasn’t clear on how many lines you’re actually playing, or what your win rate is.

It’s not wrong to be predicting lottery numbers from past draws with Excel. And hitting 4-6 sounds great.

But presumably you mean 4-6 of your pool of ’15-20′ numbers that you’re wheeling, right?

Which doesn’t necessarily mean you’re winning anything. Or that those numbers are doing any better than random luck (depending on the size of the wheel/how many combinations you’re playing).

1. Forgetting all Excel maths a moment – if you compare the actual number of combinations you are playing against the odds for the game you’re playing – how do you compare to ‘average luck’?

2. If you’re predicting a number 11, what’s the thinking behind picking 10 and 12 too (the balls don’t ‘know’ they are adjacent numbers)?

Can you email me a copy of that spreadsheet that you use?

Thanks in advance.

I’m an excel guy myself and very interested in testing your system in the current lottery that I’m trying to crack, could you send me the spreadsheet that you have mentioned here, using LINEST combined with SLOPE and Y-INTERCEPT.

I was trying to find out the way how to use the LINEST function. Could you provide me with further details, please?

Please can I get a copy of using LINEST combined with SLOPE and Y-INTERCEPT.

Please can you explain in full details how you do it? You seem to be the only person making sense and promising. I have tried almost all mathematical formula and sequences I know of but I still couldn’t get it. Please, I’d be so glad and grateful if you can help me, I’m looking forward to hearing from you.

Thank you…

*“I usually hit 4-6”* has a lot more to do with the size of the wheel, than what you do with Excel 🙂

Hi there does this really work?

The past is just that – the past as far as exact repeats go. Yet, depending upon the type of pattern that you chase and record the past sets can yield information as to how a certain pattern was accomplished but not necessarily the exact data. Unless that you use an assorted amount of pattern predictability in your tool kit, you won’t understand what it is that I am saying.

Hi Teufel. So you’re saying you can use tools to predict patterns in the results, but not any actual results.

Sounds to me like you’re just predicting ‘patterns’ that randomness dictates will be there (like people who chase sums and deltas and such like). So apart from being of passing interest to us geeks who like numbers, it doesn’t actually give any benefit in terms of chances of winning?

This is a great blog and I agree 100%. We cannot say often enough that it is impossible to predict future lottery numbers based on past results. In fact there is no way of predicting lottery numbers, period.

However it still matters what lottery numbers you pick because some numbers lead to bigger prizes if you do win.

A rundown for the pick 3 game of …1… thru ..0.. which equals 30 digits by adding a fourth row or a total of 40 digits gives a pattern of predictability when I create a 5 x 6 rundown of the same numbers or a 6 x 5 of the same sets. The answer to each days win shows in these constructs. I know which day (s) to use and how to use them. They all came from a piece of past history and not in the order that the clues or patterns were derived. I do not “chase patterns” per se but the resulting end use of the created pattern shows me where the win will fall. Many people have brushed me off but I work with simplistic ideas and because they are so simple players tend to try and make rocket science of common abstract thinking. Those folks that have asked me usually blow the whole pattern instead of using the instructions that I give which are six items. I let the players choose their own numbers from the very short list that I give to work from. The pattern that I use sets the rundown and the rundown is converted to a …5 x 6… or a …6 x 5… setup. Once done this setup is good for seven days and all of this came from past history and logical thinking.

But surely Teufel whatever method you use to pick a subset of numbers – you’re going to find some of the winning numbers appear in that subset. That’s random numbers for you.

But bottom line… beneath the complexity of what you’re doing, does it actually make any difference to your win rate versus choosing numbers randomly?

The answer to that has to be ‘no’.

This is all good and well this win rate, blah blah blah but I bet none of you have scooped a jackpot. Keep working on it though, lol.

Not sure who you were commenting to. But jackpots obviously have tough odds (surprise, it’s a lottery!) but as I’m pointing out here – adding enormous complexity to selecting numbers does nothing to actually help you win. And that’s just the same if you’re doing it for your own ego, entertainment, or if you’re paying money to some charlatan to buy that ‘system’.

If you look closely there is a Delta Function. And that 90% of the numbers are 1 to 15.

Hi Sparx. It’s a bit confusing because there is an Excel function called DELTA, which actually just tells you if two numbers are equal or not.

But ‘deltas’ more generally refers to the difference between two numbers, i.e. the delta of ball 8 and ball 15 is 7 etc.

And this is the problem with spreadsheets… people often apply all sorts of numeric manipulation on lottery numbers, because, hey, they’re numbers right!

When they’re not really. They’re just balls bouncing around.

So it doesn’t really make any sense to start applying deltas to results analysis. The deltas/differences will tend to fall within a certain range, but only because there are a limited range of numbers in the game. It doesn’t mean anything useful 🙂

Hi, I have been playing using Excel with Lotto 649 and Super 7 in Canada for many years. I have the data from day one of each lottery. I must admit I have never won a significant lottery so far. However I am having fun and that is really the point of playing a lottery. I study time between numbers winning, the most frequent numbers winning and losing, how many numbers drawn in the current draw that were in the past 6 draws, charts and so on. Good luck to everyone and don’t forget if you are not having fun then it is not worth it!

I've been analyzing all my past lottery results over the years. I know these things are supposed to be random, but there is a pattern. Comments Page 1. ]]>