After Scotts question for tomorrows podcast, I thought it would be cool to see how a pivot table could analyze the most popular lottery numbers. The MegaMillions jackpot in several US states is up to $216 million and today's podcast will select the most popular numbers. Episode 453 shows you how. UPDATE: the Mega Millions download site has changed. For a lengthy 2014 video showing more complete steps, watch:
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, it's Tuesday morning and just a few minutes ago.
I recorded, Tuesday's podcast.
Where, Scott from Australia sent in a question about lottery numbers, and I said that on Wednesday, I'd take a look at it an analysis of how to choose the most popular lottery numbers.
I started to think about that, and the Mega Millions is up to 216 billion.
That's it about 11 states in the United States, and the drawing is Tuesday night.
So, we're moving Scott's question to Wednesday and right now, we're gonna pull the numbers from the Mega Millions website, to take a look at how we can use a pivot table, to find the most popular numbers in Excel.
Now, in tomorrow's podcast which was originally Tuesday's, will be on Wednesday.
I say that we'll take a look at the numbers, the next day and it'll all be confused.
I'm so sorry.
But, anyway...
So, here we go we went out to the Mega Millions website, and they have under winning picks a place where you can download all of the numbers.
So, I went to this site and the data is beautiful.
It's common to limited, I basically copy this and pasted it into Excel.
And then use data, text to columns in order to get everything into a single column.
So, what I have is, I have the date, the number, that was drawn and whether it was part of the regular drawing or the Mega Ball Number.
So, I'm going to choose one cell in the database and use data pivot table and pivot chart report and just click [ finish ].
I have got a brand new worksheet, where I get a list of the fields of my original data set and a blank pivot table where I can drop things.
So, I'm going to take the number field and drop that along the left-hand side and as you would expect.
We'd see a list of numbers from 1 to 56.
This particular lottery draws numbers from 1 to 56, and now we need to count those and in order to count.
I want to take some sort of a text field over and it happens that I have this type field.
I'm gonna drop that in the data items and because it contains text.
It's going to give me a COUNT instead of a SUM.
I wouldn't want to sum the numbers because then every time 56 came up, it would get 56 points instead of at the number one, which we're going to get one.
So, I used count of type here.
Now, I'm gonna double click on the number button and then click advanced and say that I want to sort this descending, based on the count of type.
That'll bring the most popular numbers to the top, click [ OK ] Now, in this particular lottery, there's two basic drawings.
There's five numbers that are in a regular pool, and then one number which is the mega number.
I'm gonna take the type field and drop it up in the page area and say that I just want to see the regular numbers.
Alright! So, based on this the five most popular numbers over the history of the lottery are 32, 5, 22, 47, 42 and 17.
Now, there's a three-way tie there.
If you're gonna play the lottery you have to choose whether you're gonna choose 47 and 42, or which combinations of those.
Now, for the money ball.
The fifth ball, the one that really makes things.
It really increases the odds here.
Basically, if I choose the money ball, you'll see that because I chose the auto sort under the advanced button.
That even after I pivot the table, I choose a different filter.
But, I still have the most popular numbers to the top and this says that the most popular number that shows up in the money.
Ball or the Mega Ball, is the number three.
So, there you have it.
Go out and play the lottery.
If we all hit, we'll never have to watch the podcast again.
Thanks for stopping by.
Tomorrow, we'll get to Scott's question that was originally a Tuesday podcast.
Now, I'll be the Wednesday podcast.
Thanks for stopping by.
I'm Bill Jelen.
Well, it's Tuesday morning and just a few minutes ago.
I recorded, Tuesday's podcast.
Where, Scott from Australia sent in a question about lottery numbers, and I said that on Wednesday, I'd take a look at it an analysis of how to choose the most popular lottery numbers.
I started to think about that, and the Mega Millions is up to 216 billion.
That's it about 11 states in the United States, and the drawing is Tuesday night.
So, we're moving Scott's question to Wednesday and right now, we're gonna pull the numbers from the Mega Millions website, to take a look at how we can use a pivot table, to find the most popular numbers in Excel.
Now, in tomorrow's podcast which was originally Tuesday's, will be on Wednesday.
I say that we'll take a look at the numbers, the next day and it'll all be confused.
I'm so sorry.
But, anyway...
So, here we go we went out to the Mega Millions website, and they have under winning picks a place where you can download all of the numbers.
So, I went to this site and the data is beautiful.
It's common to limited, I basically copy this and pasted it into Excel.
And then use data, text to columns in order to get everything into a single column.
So, what I have is, I have the date, the number, that was drawn and whether it was part of the regular drawing or the Mega Ball Number.
So, I'm going to choose one cell in the database and use data pivot table and pivot chart report and just click [ finish ].
I have got a brand new worksheet, where I get a list of the fields of my original data set and a blank pivot table where I can drop things.
So, I'm going to take the number field and drop that along the left-hand side and as you would expect.
We'd see a list of numbers from 1 to 56.
This particular lottery draws numbers from 1 to 56, and now we need to count those and in order to count.
I want to take some sort of a text field over and it happens that I have this type field.
I'm gonna drop that in the data items and because it contains text.
It's going to give me a COUNT instead of a SUM.
I wouldn't want to sum the numbers because then every time 56 came up, it would get 56 points instead of at the number one, which we're going to get one.
So, I used count of type here.
Now, I'm gonna double click on the number button and then click advanced and say that I want to sort this descending, based on the count of type.
That'll bring the most popular numbers to the top, click [ OK ] Now, in this particular lottery, there's two basic drawings.
There's five numbers that are in a regular pool, and then one number which is the mega number.
I'm gonna take the type field and drop it up in the page area and say that I just want to see the regular numbers.
Alright! So, based on this the five most popular numbers over the history of the lottery are 32, 5, 22, 47, 42 and 17.
Now, there's a three-way tie there.
If you're gonna play the lottery you have to choose whether you're gonna choose 47 and 42, or which combinations of those.
Now, for the money ball.
The fifth ball, the one that really makes things.
It really increases the odds here.
Basically, if I choose the money ball, you'll see that because I chose the auto sort under the advanced button.
That even after I pivot the table, I choose a different filter.
But, I still have the most popular numbers to the top and this says that the most popular number that shows up in the money.
Ball or the Mega Ball, is the number three.
So, there you have it.
Go out and play the lottery.
If we all hit, we'll never have to watch the podcast again.
Thanks for stopping by.
Tomorrow, we'll get to Scott's question that was originally a Tuesday podcast.
Now, I'll be the Wednesday podcast.
Thanks for stopping by.
