Probability formula based on a numbers since last drawn number

[Billiebones]

Hi to all, my name is Dean and I’m new to this group. As I’m not an experienced user at excel I’m needing help regarding a probability formula, that allows me to calculate the probability of a numbers since last drawn number. I use numbers from 1 – 45 for my Australian Saturday lotto draw, I have an excel spreadsheet that calculates the number of draws since a number was drawn last. I want to use this number to calculate the probability and see whether or not it will be drawn next. Please see below as an example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 and so on until 45……
0 14 10 5 8 23 0 13 4 3 10 20 5 6 13 0

The top numbers are my 1 – 45 numbers used for the lotto game.

The bottom numbers are the number of draws since the number above it was last drawn, 0 meaning it was drawn in the last lotto game.

Using number 2 ball above as an example, I would like a probability formula that will calculate the probability using the since last drawn number (14), to determine if ball number 2 will be drawn in the next lotto draw.

I hope my explanation above is not to confusing, I will appreciate any help I can get and will post the spreadsheet after I have finished for all to use.

Thanking you kindly

Regards

Dean

[AliGW]

Welcome to the forum.

There are instructions at the top of the page in the yellow banner about how to attach your sample workbook to your next post.

[Billiebones]

Hi Ali, thank you for that information, I don't have a spreadsheet as yet for the probability question I posted until I have a formula to put into my spreadsheet. If I did a spreadsheet it would only have numbers 1 - 45 and the number since last drawn number below my 1 - 45. If it helps the forum I could do a spreadsheet up with those numbers only.

Thanks for your reply

Regards Dean

[AliGW]

Yes, please - nobody here will want to have to create one in order to be able to help you. Provide what you have and manually mock up what you want.

[Billiebones]

Thanks Ali, I will knock something up.

Regards Dean

[MrShorty]

While you are putting something together, be sure to explain (preferably with a worked example) how you want to calculate probabilities (any math/statistics equations you are using, etc.). I find that I must first understand the math/statistics/probability behind the problem before I can program the solution into the spreadsheet. If I assume a "fair" draw, then every number has an equal probability of being drawn no matter what has happened historically. I'm specifically looking to understand how the previous draw would change the probabilities from "every number has equal probability" of being drawn.

[Billiebones]

Hi Ali, I have knocked a spreadsheet up I hope it helps to answer my question.

Thanks Dean

[Billiebones]

Hi Ali, I flunked maths at school so I'm not that good at understanding hoe probability works other than what I have researched. In a nut shell I'm just trying to find a way that I can use the number since last drawn to calculate the likely hood of a number to come out. I know balls are randomly picked, but was wondering mathematically, if a a formula for probability could help determine if a ball will come out given the number of draws since it was last drawn.

Thanks for any help as this is my first post and not sure if I sound stupid asking this question

[AliGW]

I am no mathematician, however I believe you will need more data to work out probability. For instance, you'd need to know how many times any ball has been drawn over a period of time, not just the last time it was drawn.

But it's all theoretical, anyway - it's not going to help you (or anybody) to predict the outcome of tonight's Euromillions draw!!!

[CK76]

@Billiebones

As MrShorty explained, historical draw has no influence here on probability of any given number being drawn.

Probability of any given number being drawn, is only influenced within individual draw (Ball 1 is first picked, then probability changes for remaining balls in the draw).
Previous draw (distinct event) won't influence any future draw.

[AliGW]

Thanks for the mathematical heads-up - I said I was no mathematician!!!

[MrShorty]

In lotto analysis, I have encountered the idea of "hot" and "cold" numbers. In this analysis, one counts the frequency over say the last 50 draws for each number, compares that frequency to the theoretical "fair" frequency (2.2% for 45 numbers), and assigns "hot" or "cold" status to numbers depending on how far they deviate from the fair frequency.

Your data, however, is "days since last drawn". I expect a similar "hot/cold" analysis could be done. You would need some estimate of the "average" number of days between draws for any number (I don't know how to best get that estimate). With that estimate, you could then say that numbers where the days since last draw is much less than the average are "cold" and numbers where the days since last draw is much longer than average are "hot". But (and this is important to remember) this tells you nothing about the probability of each number for the next draw, because the probability for each draw (assuming a fair lottery) is 1/45 or 2.22%.

[Billiebones]

Hi MrShorty,

Thanks for your reply, I will attach my full file which kinda gives you hot and cold numbers. I'm trying to find a way of predicting the likely hood if a number is cold would it be drawn over a number that is hot. please see my full spreadsheet attached to this post below.

Thank you for your help.

[CK76]

I'm trying to find a way of predicting the likely hood if a number is cold would it be drawn over a number that is hot.

In short, there is no way to do that.

Probability can only be calculated for likelihood of any number being drawn in consecutive event/draw. But that does not tell you the probability of that number being drawn in the next. It remains unchanged for each draw and is independent of the probability of hot/cold streak.

[MrShorty]

I'm trying to find a way of predicting the likely hood if a number is cold would it be drawn over a number that is hot.

Short answer -- no. The probability of drawing a number is 1/45 or 2.22%. Whatever historical analysis you do might be interesting, but there is nothing in that historical analysis that can change the probability -- assuming a fair draw. I know there are sophisticated algorithms out there (probably well above what we want to delve into in this thread) for detecting when an allegedly random draw is not truly random (Before Excel 2003, Excel's random number generator would fail these tests). Unless you have an insight into the data that I don't (and admittedly, I have not spent very much time looking at it), I see nothing in this data that would allow me to claim that the draws are not fair.