How to download past drawing results of the Mega Millions game and find the most common numbers in the last year, quarter, or month. This long video is a followup to episode 453, where I glossed over how to put the results in a long column. Update: +MrBubeTube pointed out that the Iowa lottery website offers the results in a nice tab-delimited form that imports better to Excel: http://www.ialottery.com/Results/MM.txt
Transcript of the video:
The MrExcel podcast is sponsored by Easy Excel. Learn Excel from MrExcel podcast episode 1911.
Mega Millions lottery. Find the most popular numbers.
Hey, welcome back to the MrExcel podcast. I am Bill Jelen. Today.
I have a question that is definitely related to work, particularly if your goal is to win the lottery and never have to work again.
Now, I got an email from someone who had watched episode 453.
This is from almost five years ago, and right here at the one 11 Mark.
I made this offhand comment that I said I was going to use data text to columns to convert the data from the way that you download it to this format. And when I went back and watched it, I did not explain that well at all.
So it's time to update this video and we're going to go a little bit slower than normal today, but still it's going to be a relatively fast video.
Anytime you see this red box pop up on the screen, that's a great time to press pause and write down the formula that I used.
All right, the first thing we have to do is download all mega millions winning numbers.
And this has gotten harder, at least at the mega millions website, doesn't have the download a CSV anymore.
But if you search Google for download all mega millions winnings numbers as CSV it should take you now here.
At least the Texas lottery is offering that going back to 2003.
So right here we clicked download file and they have downloaded. So I'm going to open that. All right.
Now it's a little frustrating that there are no headings. So we're going to insert a row.
I'm going to choose row number one, I'm going to right click and say that I want to answer that.
Cause that's the row for the headings. And this is day. This is month, this is day. This is year and I'm call.
I'm going to call this one, regular one, regular one was regular space one.
And then with that cell selected, I'm going to grab the little square dot the fill, and we'll see the black plus on there.
That's when you know, you're in the right spot and drag that over five rows. And then this is the mega ball.
And finally, if your state has the multiplier, this is the multiplier. I know that not all States have that. Okay.
Now here's the day that we downloaded from the Texas website. And unfortunately, it is not perfect.
We're going to have to add some fields before we can even do the trick to get it into the right format. In the first field has to go here between the year and regular ones.
I'm going to right click on the E for regular one and insert.
And I'm gonna call this date and the weather. We're going to get the date fields going to say equal D A T E open parenthesis.
I'm going to use the mouse and click on the year.
Comma, use the mouse, mouse and click on the month.
Comma, use a mouse, mouse, and click on the day, closing parentheses and press enter. And that gives us a date. So this data is going all the way back to 2003.
Now, once I have that formula selected the cell with that form, the selected again, I'm going to go to that little square dot, want to see the black plus sign, but at double click and it will copy that formula down. Don't worry about those pound signs there.
Those hash lines just need to make the column wider and it will be able to see the dates.
All right. So there's our first truck.
Now from here, we can actually do what we call the unwind process, where we take these numbers that are going across and make them go down.
Unfortunately, if you're in Excel 2007 or 2010 or 2013 or something, even newer than that, how they've hidden this command. So it's really hard to get to the command.
The first thing you have to do is hold down the alt key and press D. D as in David.
And you see up here, we get the office access key, all D and then press the P. P as in Paul.
So alt D P, which stands for data pivot table.
It brings up the old, old wizard here, and we can choose multiple consolidation ranges. We'll click next.
And step two, a, we say that we're going to create the page fields click next.
And then for the range we want to start here on this date field, and I'm going to hold down the shift key.
I'm going to go use the right arrow key, so shift. Right, right, right, right, right, right.
This is like that range of data from date through the mega and then control and shift.
Hold on. Both of those and down arrow to select that entire range. And then we can click finish.
All right.
Now, Hey, for those of you watch the podcast regularly, you know, that just last week I used power query to do this exact same trick.
However, I'm assuming that since power query is only an Excel 2010 and the people watching this may not have power where I'm doing it the old way.
So yes, there would be a faster way to do this with power query, but you'd have to go through and download power query.
And plus if you don't have Excel 2010 you know, that it's more of a hassle. All right.
So here we are. Here's our this looks exactly like what we just had.
I realized that I want to click here in the grand total column and press the control key and the down arrow key together to get down to the last row with data.
And from here, we double-click get a brand new sheet and this sheet now has the information that we need in the order that we need it.
So here in a one, I type the word date and then B one, I'm gonna type in the word type.
And then finally in C1, I'll type the word number.
Now, a few more things that I want to do to make the analysis more usable in the in the end.
So over in column D let's type the word year, and the formula here is going to be equal Y E A R, year of that date. And that will give us the year, 2003.
That way we can take a look at the most popular numbers in each year.
I'm also going to add another column called new game, new game. The mega millions changed on October 22nd of 2013.
And before that, there were, I think only numbers up to 56. And now there's numbers.
I don't know, they're higher numbers. I'm sorry. I don't know, off the top of my head.
And as we have to know if this drawing is from the new game or the old game, so we're going to say equal if the date, so 82 is greater than, and then I'm gonna use the date function open parentheses of 2013, comma 10 common 21.
If it's greater than October 21st, then we know that we have the new game. Otherwise it's the old game.
And again, double click the fill handle to copy that down. All right.
So now we have we have the type, whether it was the mega ball or a regular ball, we have the number that was drawn.
We have the year also over there in column eight, we have the date in case we'd ever need to go back and see that. And we have this column that tells us whether it's the new game or not.
One more thing I'd like to do is I would like to get the type in column B to B just the words, mega or regular.
And so I'm going to come over here and create something called type two. All right.
Now what I want over here is just either the word mega or regulars asked for the left of that cell B2 comma seven, because there's seven letters in regular.
Now, when there's less than that, like in mega just gives us the four letters, but see, it's essentially cutting off the one, two, three, four. All right, there we go.
We now have the data in the format that we need to do the analysis and to do this analysis, we're going to create the real pivot. Build a pivot table. That's going to be useful.
So if you're an Excel 2007 or newer out, we're going to come here to the insert tab, choose pivot table sources, table range one, and we're going to go to a brand new worksheet click. Okay. All right. So we want to see the numbers down the left hand side.
I'm going to take this number field and drag it to where it says rows. All right.
So there's all the various numbers. And then I need to know how many times each of those rows appeared.
So I have to take some text field in this case, I'm going to use the type field and drag that to the values areas. Now I can see for each number, how many times they were drawn.
And then I'm going to take the type to field and drag that to the filters area.
Also, we'll take the new game field and drag that to the filters area. All right. A couple of things.
So here's all the numbers. It looks like we probably have numbers from one, Okay.
Down to 75, and we want to see which numbers were the most popular.
So I'm gonna choose one cell has to be just one cell over there in column B, like the first number here and B five. And on the data tab, we'll click Z to a all right.
And so now over the entire life of the game, at least the life of the game in Texas, which goes back to 2003 these five numbers are the most popular numbers.
However again, as I said before, October 22nd, 2013, they had a smaller pool of numbers.
And so the higher numbers of course were drawn a lot less.
So to be completely fair here, we have to go to new game and ask for just true. Click. Okay.
And now we're seeing the most popular numbers since the game changed in October of 2013.
And so you know, 10, three, nine, two and seven, however, the mega ball is in here. All right.
So from type two, let's open that up and say that we want just the regular numbers. All right.
Now, today I'm recording this on August 17th, 2014.
If you repeat these steps at any point in the future, you're potentially definitely going to have different numbers that were the most popular numbers, but certainly at this point we have 29 49, 35 10, and then there's a two way tie for 23 and 47. All right.
So you're going to have to choose, or maybe you play them both. I don't know. All right.
The other thing that we could do here, it's kind of interesting is we can take the year field and drag the year field up here and we get it to say, well, Hey, let's see just the data for 2014. Click. Okay. All right.
So they're the most popular numbers are 10 29, 35, 47 and 13, not a big change because again we're only looking at data in 2014 and the last part of 2013, Hey, I'm going to break in here.
This is actually the next day when I'm editing the video.
Another interesting thing to do would be to take the year field and move it over to columns. All right.
Particularly once you get more data, if you're doing this in 2015 or 2016, you could then see the most popular numbers for each of the years. And you're allowed to sort these columns either way.
So you can choose one cell in the year and and sort of see what was most popular or, or we could also take the year field out and put the date field going across it.
And that's going to be a lot of data.
I realized that but when the date field is across the columns this dropdown here under date filters allows us to choose various things like everything in the last quarter or everything in this quarter.
So let's choose this quarter and you'll have to scroll out to the right to the grand total to see which numbers were the most popular for all of those dates or this tip.
For those of you who happen to be in Excel 2013 or newer, you don't even have to put the date field in the pivot table.
This only works in Excel 2013.
We come here to the analysts tab and go to insert timeline, choose date, click. Okay.
And now data let's see, it should still be sorted high to low. Yep. So now we can choose a particular month.
So if we want to see the most popular numbers in July or June, or you could even choose, you know, a range of dates using the timeline also possible changes to quarters or years.
So we want to see everything from maybe the last six months or something like that.
You could very quickly change the timeline and see the most popular numbers. Again, this is only an Excel 2013 and newer.
It wasn't there in 2010 or 2007 or before. All right.
Back to the original video, but you can choose from the dropdown here and see what the most popular numbers were in any year. So those are the five regular numbers to do the exact same thing for the mega ball.
We'll choose 2014 here and change from regular to mega and click. Okay.
And there are the most popular numbers actually, let's take the 2014 out and just do all draws since the beginning of the new game. All right.
And again, a big tie, there was seven, 10, nine, 13, and 15 each having come up eight times since the games switched over.
All right, I'm going to be completely contrarian here and go the other way, rather than choosing one of the popular numbers. I'm going to say that a, these 15 mega balls are all equal.
And just the law of averages says that the number four is way overdue, right.
That if I'm going to, if I'm going to bet the number four is way overdue.
So I'm just going to do the contrarian thing here.
And that's the beautiful thing about the lottery is everyone has their own system. and you can choose what makes the most sense for you.
All right.
So my dad who occasionally plays the mega millions, if it gets large enough on him passing these numbers along to him.
And what do you think 23 or 47, 23, I'm going to go 23. All right. That's our play for this week.
And of course again assuming when you see this at some point in the future and you down follow these steps in download these numbers, you're going to get a completely different set of numbers.
The video that people wrote about was five years old. So just imagine someone up in 2019 is watching this.
You're going to have a much longer string of data and completely different results every time.
Here's another tip for those of you only in Excel, 2010 or newer we can actually get rid of these two filters here where you have to choose from the drop-downs.
And back on the it's called the options tab in Excel 2010, or the analyze tab and Excel 2013.
We're going to insert a slicer and we want slicers for type two for new game and four year, click. Okay.
I'm going to put all three of these slicers, right on top of each other.
So I want to take a little bit of time to resize them make them smaller, make them all fit up here and year I'm going to make that one wider.
And for years say that I want a bunch of columns up here in slicer tools.
Options can probably fit everything in two rows. If I go with six columns, there you go. All right. So here's how a slicer works.
If we want to see just the mega ball for the new game in 2014 and 2013. So I choose 2013.
Oh, here, wait. We have to clear this filter from the timeline. Choose 2013, 2014.
I had to hold down the control key to do that, just the mega ball, just the new game.
And we'll see the largest numbers there, or same period of time. I want to see the regular ball.
There are the five, if I don't care about new game versus old game I can just clear that filter and see the highest numbers overtime for everything. So interesting.
Weight, lots of different kind of ad hoc.
What if analysis you can do with the slicers without having to use the dropdown? Good luck. I know if you win, you'll never have to watch the mr. Excel podcast.
Again. We'll be, Hey, by the way, if you want to save this file.
Okay, because we open a CSV file. You can't save a pivot table in a CSV file.
You have to come here and do file to save as, and most important. Change it from CSV to XLS B. And you'll be good to go.
I want to say thank you. See you next time for another podcast from MrExcel.
Mega Millions lottery. Find the most popular numbers.
Hey, welcome back to the MrExcel podcast. I am Bill Jelen. Today.
I have a question that is definitely related to work, particularly if your goal is to win the lottery and never have to work again.
Now, I got an email from someone who had watched episode 453.
This is from almost five years ago, and right here at the one 11 Mark.
I made this offhand comment that I said I was going to use data text to columns to convert the data from the way that you download it to this format. And when I went back and watched it, I did not explain that well at all.
So it's time to update this video and we're going to go a little bit slower than normal today, but still it's going to be a relatively fast video.
Anytime you see this red box pop up on the screen, that's a great time to press pause and write down the formula that I used.
All right, the first thing we have to do is download all mega millions winning numbers.
And this has gotten harder, at least at the mega millions website, doesn't have the download a CSV anymore.
But if you search Google for download all mega millions winnings numbers as CSV it should take you now here.
At least the Texas lottery is offering that going back to 2003.
So right here we clicked download file and they have downloaded. So I'm going to open that. All right.
Now it's a little frustrating that there are no headings. So we're going to insert a row.
I'm going to choose row number one, I'm going to right click and say that I want to answer that.
Cause that's the row for the headings. And this is day. This is month, this is day. This is year and I'm call.
I'm going to call this one, regular one, regular one was regular space one.
And then with that cell selected, I'm going to grab the little square dot the fill, and we'll see the black plus on there.
That's when you know, you're in the right spot and drag that over five rows. And then this is the mega ball.
And finally, if your state has the multiplier, this is the multiplier. I know that not all States have that. Okay.
Now here's the day that we downloaded from the Texas website. And unfortunately, it is not perfect.
We're going to have to add some fields before we can even do the trick to get it into the right format. In the first field has to go here between the year and regular ones.
I'm going to right click on the E for regular one and insert.
And I'm gonna call this date and the weather. We're going to get the date fields going to say equal D A T E open parenthesis.
I'm going to use the mouse and click on the year.
Comma, use the mouse, mouse and click on the month.
Comma, use a mouse, mouse, and click on the day, closing parentheses and press enter. And that gives us a date. So this data is going all the way back to 2003.
Now, once I have that formula selected the cell with that form, the selected again, I'm going to go to that little square dot, want to see the black plus sign, but at double click and it will copy that formula down. Don't worry about those pound signs there.
Those hash lines just need to make the column wider and it will be able to see the dates.
All right. So there's our first truck.
Now from here, we can actually do what we call the unwind process, where we take these numbers that are going across and make them go down.
Unfortunately, if you're in Excel 2007 or 2010 or 2013 or something, even newer than that, how they've hidden this command. So it's really hard to get to the command.
The first thing you have to do is hold down the alt key and press D. D as in David.
And you see up here, we get the office access key, all D and then press the P. P as in Paul.
So alt D P, which stands for data pivot table.
It brings up the old, old wizard here, and we can choose multiple consolidation ranges. We'll click next.
And step two, a, we say that we're going to create the page fields click next.
And then for the range we want to start here on this date field, and I'm going to hold down the shift key.
I'm going to go use the right arrow key, so shift. Right, right, right, right, right, right.
This is like that range of data from date through the mega and then control and shift.
Hold on. Both of those and down arrow to select that entire range. And then we can click finish.
All right.
Now, Hey, for those of you watch the podcast regularly, you know, that just last week I used power query to do this exact same trick.
However, I'm assuming that since power query is only an Excel 2010 and the people watching this may not have power where I'm doing it the old way.
So yes, there would be a faster way to do this with power query, but you'd have to go through and download power query.
And plus if you don't have Excel 2010 you know, that it's more of a hassle. All right.
So here we are. Here's our this looks exactly like what we just had.
I realized that I want to click here in the grand total column and press the control key and the down arrow key together to get down to the last row with data.
And from here, we double-click get a brand new sheet and this sheet now has the information that we need in the order that we need it.
So here in a one, I type the word date and then B one, I'm gonna type in the word type.
And then finally in C1, I'll type the word number.
Now, a few more things that I want to do to make the analysis more usable in the in the end.
So over in column D let's type the word year, and the formula here is going to be equal Y E A R, year of that date. And that will give us the year, 2003.
That way we can take a look at the most popular numbers in each year.
I'm also going to add another column called new game, new game. The mega millions changed on October 22nd of 2013.
And before that, there were, I think only numbers up to 56. And now there's numbers.
I don't know, they're higher numbers. I'm sorry. I don't know, off the top of my head.
And as we have to know if this drawing is from the new game or the old game, so we're going to say equal if the date, so 82 is greater than, and then I'm gonna use the date function open parentheses of 2013, comma 10 common 21.
If it's greater than October 21st, then we know that we have the new game. Otherwise it's the old game.
And again, double click the fill handle to copy that down. All right.
So now we have we have the type, whether it was the mega ball or a regular ball, we have the number that was drawn.
We have the year also over there in column eight, we have the date in case we'd ever need to go back and see that. And we have this column that tells us whether it's the new game or not.
One more thing I'd like to do is I would like to get the type in column B to B just the words, mega or regular.
And so I'm going to come over here and create something called type two. All right.
Now what I want over here is just either the word mega or regulars asked for the left of that cell B2 comma seven, because there's seven letters in regular.
Now, when there's less than that, like in mega just gives us the four letters, but see, it's essentially cutting off the one, two, three, four. All right, there we go.
We now have the data in the format that we need to do the analysis and to do this analysis, we're going to create the real pivot. Build a pivot table. That's going to be useful.
So if you're an Excel 2007 or newer out, we're going to come here to the insert tab, choose pivot table sources, table range one, and we're going to go to a brand new worksheet click. Okay. All right. So we want to see the numbers down the left hand side.
I'm going to take this number field and drag it to where it says rows. All right.
So there's all the various numbers. And then I need to know how many times each of those rows appeared.
So I have to take some text field in this case, I'm going to use the type field and drag that to the values areas. Now I can see for each number, how many times they were drawn.
And then I'm going to take the type to field and drag that to the filters area.
Also, we'll take the new game field and drag that to the filters area. All right. A couple of things.
So here's all the numbers. It looks like we probably have numbers from one, Okay.
Down to 75, and we want to see which numbers were the most popular.
So I'm gonna choose one cell has to be just one cell over there in column B, like the first number here and B five. And on the data tab, we'll click Z to a all right.
And so now over the entire life of the game, at least the life of the game in Texas, which goes back to 2003 these five numbers are the most popular numbers.
However again, as I said before, October 22nd, 2013, they had a smaller pool of numbers.
And so the higher numbers of course were drawn a lot less.
So to be completely fair here, we have to go to new game and ask for just true. Click. Okay.
And now we're seeing the most popular numbers since the game changed in October of 2013.
And so you know, 10, three, nine, two and seven, however, the mega ball is in here. All right.
So from type two, let's open that up and say that we want just the regular numbers. All right.
Now, today I'm recording this on August 17th, 2014.
If you repeat these steps at any point in the future, you're potentially definitely going to have different numbers that were the most popular numbers, but certainly at this point we have 29 49, 35 10, and then there's a two way tie for 23 and 47. All right.
So you're going to have to choose, or maybe you play them both. I don't know. All right.
The other thing that we could do here, it's kind of interesting is we can take the year field and drag the year field up here and we get it to say, well, Hey, let's see just the data for 2014. Click. Okay. All right.
So they're the most popular numbers are 10 29, 35, 47 and 13, not a big change because again we're only looking at data in 2014 and the last part of 2013, Hey, I'm going to break in here.
This is actually the next day when I'm editing the video.
Another interesting thing to do would be to take the year field and move it over to columns. All right.
Particularly once you get more data, if you're doing this in 2015 or 2016, you could then see the most popular numbers for each of the years. And you're allowed to sort these columns either way.
So you can choose one cell in the year and and sort of see what was most popular or, or we could also take the year field out and put the date field going across it.
And that's going to be a lot of data.
I realized that but when the date field is across the columns this dropdown here under date filters allows us to choose various things like everything in the last quarter or everything in this quarter.
So let's choose this quarter and you'll have to scroll out to the right to the grand total to see which numbers were the most popular for all of those dates or this tip.
For those of you who happen to be in Excel 2013 or newer, you don't even have to put the date field in the pivot table.
This only works in Excel 2013.
We come here to the analysts tab and go to insert timeline, choose date, click. Okay.
And now data let's see, it should still be sorted high to low. Yep. So now we can choose a particular month.
So if we want to see the most popular numbers in July or June, or you could even choose, you know, a range of dates using the timeline also possible changes to quarters or years.
So we want to see everything from maybe the last six months or something like that.
You could very quickly change the timeline and see the most popular numbers. Again, this is only an Excel 2013 and newer.
It wasn't there in 2010 or 2007 or before. All right.
Back to the original video, but you can choose from the dropdown here and see what the most popular numbers were in any year. So those are the five regular numbers to do the exact same thing for the mega ball.
We'll choose 2014 here and change from regular to mega and click. Okay.
And there are the most popular numbers actually, let's take the 2014 out and just do all draws since the beginning of the new game. All right.
And again, a big tie, there was seven, 10, nine, 13, and 15 each having come up eight times since the games switched over.
All right, I'm going to be completely contrarian here and go the other way, rather than choosing one of the popular numbers. I'm going to say that a, these 15 mega balls are all equal.
And just the law of averages says that the number four is way overdue, right.
That if I'm going to, if I'm going to bet the number four is way overdue.
So I'm just going to do the contrarian thing here.
And that's the beautiful thing about the lottery is everyone has their own system. and you can choose what makes the most sense for you.
All right.
So my dad who occasionally plays the mega millions, if it gets large enough on him passing these numbers along to him.
And what do you think 23 or 47, 23, I'm going to go 23. All right. That's our play for this week.
And of course again assuming when you see this at some point in the future and you down follow these steps in download these numbers, you're going to get a completely different set of numbers.
The video that people wrote about was five years old. So just imagine someone up in 2019 is watching this.
You're going to have a much longer string of data and completely different results every time.
Here's another tip for those of you only in Excel, 2010 or newer we can actually get rid of these two filters here where you have to choose from the drop-downs.
And back on the it's called the options tab in Excel 2010, or the analyze tab and Excel 2013.
We're going to insert a slicer and we want slicers for type two for new game and four year, click. Okay.
I'm going to put all three of these slicers, right on top of each other.
So I want to take a little bit of time to resize them make them smaller, make them all fit up here and year I'm going to make that one wider.
And for years say that I want a bunch of columns up here in slicer tools.
Options can probably fit everything in two rows. If I go with six columns, there you go. All right. So here's how a slicer works.
If we want to see just the mega ball for the new game in 2014 and 2013. So I choose 2013.
Oh, here, wait. We have to clear this filter from the timeline. Choose 2013, 2014.
I had to hold down the control key to do that, just the mega ball, just the new game.
And we'll see the largest numbers there, or same period of time. I want to see the regular ball.
There are the five, if I don't care about new game versus old game I can just clear that filter and see the highest numbers overtime for everything. So interesting.
Weight, lots of different kind of ad hoc.
What if analysis you can do with the slicers without having to use the dropdown? Good luck. I know if you win, you'll never have to watch the mr. Excel podcast.
Again. We'll be, Hey, by the way, if you want to save this file.
Okay, because we open a CSV file. You can't save a pivot table in a CSV file.
You have to come here and do file to save as, and most important. Change it from CSV to XLS B. And you'll be good to go.
I want to say thank you. See you next time for another podcast from MrExcel.
