Markus is looking for Excel help to pick numbers for the Euro Jackpot where you have to choose 5 numbers from 1 to 50. In this video, I share secrets from my late friend Dogtrack Bill.
To use the number picker in a free Excel Online, follow this link:
The Euro Jackpot payout for matching 5 numbers with no bonus numbers is a parimutuel system. A fixed percentage of the betting pool is split between all people that matched five numbers. To maximize the payout if you win, choose numbers that are not likely to be chosen by other people. Avoid drawing patterns on the betting card. And, most importantly, stop using dates to choose your lottery numbers.
Table of Contents
(0:00) Markus asks for Euro Jackpot 5/50 advice
(0:20) Formulas require latest Excel
(0:42) Movie: Jerry & Marge Go Large
(1:02) Dogtrack Bill
(1:19) Strategy for parimutiel betting
(2:05) Don't draw patterns on lottery betting slip
(2:50) Don't choose Dates
(5:12) Mix of high & low, odd & even
(5:58) Excel formulas to randomize picks
(6:28) How many numbers are Odd using Excel
(6:48) Avoid date picks
(7:20) Conditional formatting for green
(7:49) Choose 2 numbers from 1-10
(8:00) YouTube description won't allow less than symbol
(8:26) Explanation of all Excel Formulas
(9:42) Sort numbers so it is easier to mark the card
(9:55) Maximize the parimutuel winnings
(10:26) Thanks
Formulas used in the spreadsheet:
B3: =SORT(VSTACK(INDEX(SORTBY(SEQUENCE(25),
RANDARRAY(25)),SEQUENCE(3)),
INDEX(SORTBY(SEQUENCE(25)+25,RANDARRAY(25)),SEQUENCE(2))))
D3: =SUM(--ISODD(--B3#))
F2: =OR(D3=2,D3=3)
F3: =MAX(B3#)GT31
F4: =AND(F2:F3)
H3: =SORT(INDEX(SORTBY(SEQUENCE(10),
RANDARRAY(10)),SEQUENCE(2)))
To use the number picker in a free Excel Online, follow this link:
The Euro Jackpot payout for matching 5 numbers with no bonus numbers is a parimutuel system. A fixed percentage of the betting pool is split between all people that matched five numbers. To maximize the payout if you win, choose numbers that are not likely to be chosen by other people. Avoid drawing patterns on the betting card. And, most importantly, stop using dates to choose your lottery numbers.
Table of Contents
(0:00) Markus asks for Euro Jackpot 5/50 advice
(0:20) Formulas require latest Excel
(0:42) Movie: Jerry & Marge Go Large
(1:02) Dogtrack Bill
(1:19) Strategy for parimutiel betting
(2:05) Don't draw patterns on lottery betting slip
(2:50) Don't choose Dates
(5:12) Mix of high & low, odd & even
(5:58) Excel formulas to randomize picks
(6:28) How many numbers are Odd using Excel
(6:48) Avoid date picks
(7:20) Conditional formatting for green
(7:49) Choose 2 numbers from 1-10
(8:00) YouTube description won't allow less than symbol
(8:26) Explanation of all Excel Formulas
(9:42) Sort numbers so it is easier to mark the card
(9:55) Maximize the parimutuel winnings
(10:26) Thanks
Formulas used in the spreadsheet:
B3: =SORT(VSTACK(INDEX(SORTBY(SEQUENCE(25),
RANDARRAY(25)),SEQUENCE(3)),
INDEX(SORTBY(SEQUENCE(25)+25,RANDARRAY(25)),SEQUENCE(2))))
D3: =SUM(--ISODD(--B3#))
F2: =OR(D3=2,D3=3)
F3: =MAX(B3#)GT31
F4: =AND(F2:F3)
H3: =SORT(INDEX(SORTBY(SEQUENCE(10),
RANDARRAY(10)),SEQUENCE(2)))
Transcript of the video:
The secret to maximizing your parimutuel lottery winning.
Today's question from Markus. “Would you please help me with a formula that can be used to select the winning combination playing Euro Jackpot 5/50?” Doesn't want to win the jackpot, just to match the correct five numbers.
Now, I haven't done Euro Jackpot before.
Before you get too deep into this, I'm using some really recent formulas that were released in Microsoft 365.
Don't watch this video until you go to Excel and try this formula =VSTACK(1,2) in cell A1.
You should get this, one and two.
If instead you get a #NAME?
error, just stop watching right now, because it won't work in your version of Excel.
And by the way, if you're a fan of lottery theory, check out this great movie I just watched, Jerry & Marge Go Large.
Bryan Cranston plays this mathematician who actually discovered a true anomaly in the state lottery and won big.
Just a great story.
And a shout out to my late friend Dogtrack Bill.
He would dream up all sorts of clever systems and hire me to program them in Excel.
With his permission and his blessing, I've used those systems to come out ahead lifetime.
But he's the guy who taught me this strategy for parimutuel betting.
In the Euro Jackpot, it's all the people who have the winning five numbers split a fixed percentage of the winnings.
And if you choose numbers that other people have chosen, then they're split amongst more people.
Here's just three recent drawings.
So in this drawing, six people matched five numbers and it was 149 euros, 149,000 euros.
But then a few drawings before that, only two people matched, and 363,000 euros.
And here, oh geez.
Imagine this.
You hit and there were 11 people.
So now you get only 110,000 euros.
I mean, it's a huge swing.
And one of the worst things you can do, and I've done it before, you get the little card and you draw any sort of a pattern in.
I've done the diamond.
I've done this slash.
The odds of getting one, two, three, four, five are the same as any random number, 6, 11, 16, 35, 44.
But because a lot of people are just drawing patterns, and if this would happen to hit, there's going to be a ton of winners.
So with an identical prize pool, here you're going to get 910 euros, not 910,000, just 910.
And here you're going to get 363,000 euros.
So that increases the payoff 400 times.
So don't draw patterns.
And I've done this one so many times.
I've done this so many times and I never realized just how bad it is.
So you know, here, wife birthday January 23rd, anniversary on June 17th, kid’s birthday on June 16th.
I look at those six is duplicated.
So I'm going to play 1, 6, 16, 17 and 23.
Really common way to pick numbers.
Maybe 40 - 50% of the people are using dates from their family to pick numbers.
That concentrates the numbers.
In really 1 to 12 you got a lot of those and then 13 to 30 and 31 and absolutely no one choosing any numbers out here in 32 and beyond.
Here's some math.
Using equal combine of 50 comma five there are 2,118,760 ways to choose five numbers out of 50.
If you limit yourself to the birthday pics, there are only 110,056 options there.
That's 5.2% of the universe of possible picks.
So half the people are going to be concentrated in a very small area.
To visualize this, like the people choosing dates are down here in this tiny little 5.2% corner of the universe of possible choices.
So let's say there's 1000 dots here.
That's 500 light blue dots and 500 dark blue dots.
Just look at how many times.
People are going to be picking the exact same numbers down here in this lower left corner.
Now, just in case you're wondering, I got 110,000 from, that could be a 20 minute video in and of itself.
I initially came up with a much larger number of 241,164.
But then I realized that there were duplicates.
And there's a whole lot of math here to come out to 110,056, including things like where It had a 31, but the months selected were months that didn't have 31 days.
I mean just an exhaustive video.
And maybe I'll post that video someday, but for right now, you're going to have to trust me that it's 110,056.
By avoiding date picks, you'll move yourself out of here into this very sparsely populated part of the universe of possible picks.
Now some history.
Found this on a website when they broke the numbers into one to 25 and 26 to 50, and the pattern of three low numbers and two high numbers is expected 32.57% of the time.
But over the last 503 draws, it's actually beating that by a lot 34.79 and we would expect 2 low and three high to show up 32% of the time.
And for whatever reason it's just it's not there, right.
So my formula strategy that I'm going to use on the next sheet is we're going to choose three numbers from 1 to 25 and two numbers from 26 to 50.
And then as a bonus, I'm also checking to see if we get two or three even, 3 odd or two even historically 62% of the draws matched that pattern.
Alright, so here is the formula.
Now don't try and type this formula.
They're all down in the YouTube description.
This formula is the one that is picking 3 numbers from the low numbers 1 to 25 and two from the high numbers 26 to 50 and stacking them together using the VSTACK function.
We'll cover all the Excel behind this.
Let me just for the people who are looking to use this spreadsheet to pick lottery numbers.
So this chooses the numbers from 1 to 50 and then right here this formula is checking to see how many of those numbers are odd.
And remember we want to mix with three odd and two even.
Or two odd in three even.
So we want these numbers to be two or three.
Out here a formula, the formula from F2 checks to see if the number of odd numbers is two or three.
And then for not a date - now this doesn't go into as much detail as that other 20 minute video would have gone into.
It just simply checks to make sure that one of the numbers we chose is greater than 31.
Otherwise, it assumes that it's potentially a date.
Now, I realize that this is going to disqualify some choices.
For example, 2, 4, 6,30, 31 wouldn't be possible, but this will say that it'll disqualify that.
And then this one just checks to make sure that both of those are True with a little conditional formatting there, to turn it green when it's True, False when it's not right.
So when you open this, you'll get a set of numbers.
You press the F9 key, just keep pressing F9.
If you get a red false there, then don't choose those numbers because you're going to end up down here in this densely populated 5.4% of the universe.
Keep pressing F9 and the green trues are the ones that are best to play.
To get the two numbers one to 10 right here, this shorter formula chooses 2 random numbers from that.
OK, so there's the formulas.
They're all down in the YouTube description.
Oh – the one gotcha here: YouTube will not let me post a greater than or less than sign because it thinks I'm trying to put HTML in the description.
So I'm going to put GT.
You're going to have to convert that back.
I'll probably just put the spreadsheet out there so you can download it and just try it, not have to try and build this yourself.
Although hey, if you if you hit - if you if you win huge - don't forget you should buy me lunch for this spreadsheet.
Now for my Excel friends let's talk about the how this formula is working.
The first thing I do is I need the low numbers 1 to 25.
So I use equal SEQUENCE of 25.
And then because I have 25 numbers there, I generate a random array of 25 numbers.
And then this awesome little bit of the formula sorts the numbers 1 to 25 by these numbers here.
And see that way every time I press F9, get a different ordering of the 25 numbers.
From here I want to choose just the top three numbers, so the SEQUENCE of three.
And then the INDEX.
Check that - I was pretty happy that I was able to do that.
The INDEX says, “hey, here's a bunch of numbers”.
And which row do I want?
I want 1, 2, 3 using that SEQUENCE function and it gets me 23, 24 and five.
Press F9, get a different random sort of the numbers.
Now I get 10, 5, and 13.
All right, so that's the first half - we're choosing 3 numbers from 1 to 25.
To get numbers from 26 to 50 – again, the sequence function plus 25.
The same formula to generate a new RANDARRAY.
Sort these 26 to 50 based on this random array.
This time we're looking for the top 2 using the SEQUENCE.
And again the INDEX function there finally.
So now I know I want 10, 5, 13, 34, and 28..
So I've stacked those together into one array.
And then just to make your life easier so you can mark the card easier.
SORT these five numbers, these five numbers here in the sequence so they're always easier to mark.
It's important to remember that any 5 numbers 14, 15, 21, 32, and 40 has the exact same chance of winning as 1, 6, 16, 17, 23 or even 1, 2, 3, 4, 5.
The whole thing here is because of the parimutuel nature of the Euro Jackpot, matching 5 out of five.
You want to make sure that if you hit, you want to make sure that you're out here in the sparsely populated section of the universe instead of down here where a lot of people are choosing dates.
Alright.
Hey, thanks to Markus for sending that question in.
You know, a lot of fun macro permutations there to come up with these ideas and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today's question from Markus. “Would you please help me with a formula that can be used to select the winning combination playing Euro Jackpot 5/50?” Doesn't want to win the jackpot, just to match the correct five numbers.
Now, I haven't done Euro Jackpot before.
Before you get too deep into this, I'm using some really recent formulas that were released in Microsoft 365.
Don't watch this video until you go to Excel and try this formula =VSTACK(1,2) in cell A1.
You should get this, one and two.
If instead you get a #NAME?
error, just stop watching right now, because it won't work in your version of Excel.
And by the way, if you're a fan of lottery theory, check out this great movie I just watched, Jerry & Marge Go Large.
Bryan Cranston plays this mathematician who actually discovered a true anomaly in the state lottery and won big.
Just a great story.
And a shout out to my late friend Dogtrack Bill.
He would dream up all sorts of clever systems and hire me to program them in Excel.
With his permission and his blessing, I've used those systems to come out ahead lifetime.
But he's the guy who taught me this strategy for parimutuel betting.
In the Euro Jackpot, it's all the people who have the winning five numbers split a fixed percentage of the winnings.
And if you choose numbers that other people have chosen, then they're split amongst more people.
Here's just three recent drawings.
So in this drawing, six people matched five numbers and it was 149 euros, 149,000 euros.
But then a few drawings before that, only two people matched, and 363,000 euros.
And here, oh geez.
Imagine this.
You hit and there were 11 people.
So now you get only 110,000 euros.
I mean, it's a huge swing.
And one of the worst things you can do, and I've done it before, you get the little card and you draw any sort of a pattern in.
I've done the diamond.
I've done this slash.
The odds of getting one, two, three, four, five are the same as any random number, 6, 11, 16, 35, 44.
But because a lot of people are just drawing patterns, and if this would happen to hit, there's going to be a ton of winners.
So with an identical prize pool, here you're going to get 910 euros, not 910,000, just 910.
And here you're going to get 363,000 euros.
So that increases the payoff 400 times.
So don't draw patterns.
And I've done this one so many times.
I've done this so many times and I never realized just how bad it is.
So you know, here, wife birthday January 23rd, anniversary on June 17th, kid’s birthday on June 16th.
I look at those six is duplicated.
So I'm going to play 1, 6, 16, 17 and 23.
Really common way to pick numbers.
Maybe 40 - 50% of the people are using dates from their family to pick numbers.
That concentrates the numbers.
In really 1 to 12 you got a lot of those and then 13 to 30 and 31 and absolutely no one choosing any numbers out here in 32 and beyond.
Here's some math.
Using equal combine of 50 comma five there are 2,118,760 ways to choose five numbers out of 50.
If you limit yourself to the birthday pics, there are only 110,056 options there.
That's 5.2% of the universe of possible picks.
So half the people are going to be concentrated in a very small area.
To visualize this, like the people choosing dates are down here in this tiny little 5.2% corner of the universe of possible choices.
So let's say there's 1000 dots here.
That's 500 light blue dots and 500 dark blue dots.
Just look at how many times.
People are going to be picking the exact same numbers down here in this lower left corner.
Now, just in case you're wondering, I got 110,000 from, that could be a 20 minute video in and of itself.
I initially came up with a much larger number of 241,164.
But then I realized that there were duplicates.
And there's a whole lot of math here to come out to 110,056, including things like where It had a 31, but the months selected were months that didn't have 31 days.
I mean just an exhaustive video.
And maybe I'll post that video someday, but for right now, you're going to have to trust me that it's 110,056.
By avoiding date picks, you'll move yourself out of here into this very sparsely populated part of the universe of possible picks.
Now some history.
Found this on a website when they broke the numbers into one to 25 and 26 to 50, and the pattern of three low numbers and two high numbers is expected 32.57% of the time.
But over the last 503 draws, it's actually beating that by a lot 34.79 and we would expect 2 low and three high to show up 32% of the time.
And for whatever reason it's just it's not there, right.
So my formula strategy that I'm going to use on the next sheet is we're going to choose three numbers from 1 to 25 and two numbers from 26 to 50.
And then as a bonus, I'm also checking to see if we get two or three even, 3 odd or two even historically 62% of the draws matched that pattern.
Alright, so here is the formula.
Now don't try and type this formula.
They're all down in the YouTube description.
This formula is the one that is picking 3 numbers from the low numbers 1 to 25 and two from the high numbers 26 to 50 and stacking them together using the VSTACK function.
We'll cover all the Excel behind this.
Let me just for the people who are looking to use this spreadsheet to pick lottery numbers.
So this chooses the numbers from 1 to 50 and then right here this formula is checking to see how many of those numbers are odd.
And remember we want to mix with three odd and two even.
Or two odd in three even.
So we want these numbers to be two or three.
Out here a formula, the formula from F2 checks to see if the number of odd numbers is two or three.
And then for not a date - now this doesn't go into as much detail as that other 20 minute video would have gone into.
It just simply checks to make sure that one of the numbers we chose is greater than 31.
Otherwise, it assumes that it's potentially a date.
Now, I realize that this is going to disqualify some choices.
For example, 2, 4, 6,30, 31 wouldn't be possible, but this will say that it'll disqualify that.
And then this one just checks to make sure that both of those are True with a little conditional formatting there, to turn it green when it's True, False when it's not right.
So when you open this, you'll get a set of numbers.
You press the F9 key, just keep pressing F9.
If you get a red false there, then don't choose those numbers because you're going to end up down here in this densely populated 5.4% of the universe.
Keep pressing F9 and the green trues are the ones that are best to play.
To get the two numbers one to 10 right here, this shorter formula chooses 2 random numbers from that.
OK, so there's the formulas.
They're all down in the YouTube description.
Oh – the one gotcha here: YouTube will not let me post a greater than or less than sign because it thinks I'm trying to put HTML in the description.
So I'm going to put GT.
You're going to have to convert that back.
I'll probably just put the spreadsheet out there so you can download it and just try it, not have to try and build this yourself.
Although hey, if you if you hit - if you if you win huge - don't forget you should buy me lunch for this spreadsheet.
Now for my Excel friends let's talk about the how this formula is working.
The first thing I do is I need the low numbers 1 to 25.
So I use equal SEQUENCE of 25.
And then because I have 25 numbers there, I generate a random array of 25 numbers.
And then this awesome little bit of the formula sorts the numbers 1 to 25 by these numbers here.
And see that way every time I press F9, get a different ordering of the 25 numbers.
From here I want to choose just the top three numbers, so the SEQUENCE of three.
And then the INDEX.
Check that - I was pretty happy that I was able to do that.
The INDEX says, “hey, here's a bunch of numbers”.
And which row do I want?
I want 1, 2, 3 using that SEQUENCE function and it gets me 23, 24 and five.
Press F9, get a different random sort of the numbers.
Now I get 10, 5, and 13.
All right, so that's the first half - we're choosing 3 numbers from 1 to 25.
To get numbers from 26 to 50 – again, the sequence function plus 25.
The same formula to generate a new RANDARRAY.
Sort these 26 to 50 based on this random array.
This time we're looking for the top 2 using the SEQUENCE.
And again the INDEX function there finally.
So now I know I want 10, 5, 13, 34, and 28..
So I've stacked those together into one array.
And then just to make your life easier so you can mark the card easier.
SORT these five numbers, these five numbers here in the sequence so they're always easier to mark.
It's important to remember that any 5 numbers 14, 15, 21, 32, and 40 has the exact same chance of winning as 1, 6, 16, 17, 23 or even 1, 2, 3, 4, 5.
The whole thing here is because of the parimutuel nature of the Euro Jackpot, matching 5 out of five.
You want to make sure that if you hit, you want to make sure that you're out here in the sparsely populated section of the universe instead of down here where a lot of people are choosing dates.
Alright.
Hey, thanks to Markus for sending that question in.
You know, a lot of fun macro permutations there to come up with these ideas and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
