How To Make: MEGA MILLIONS Lottery Numbers in Microsoft EXCEL!

[Music] good afternoon everybody if you're new to the channel my name is Gary and this is typed by GP today is Monday the 22nd of October in 2018 and tomorrow is the big drawing for the Mega Millions one point six billion dollar jackpot so I thought it was going to be interesting to put together as sets of numbers from a year's worth of past Mega Millions lottery drops so I'm gonna show you guys how to do that for fun coming right up [Music] welcome back so here we are on the Mega Millions page for search winning numbers and I've already pulled up a year's worth of past lottery winning numbers from November 2017 to October 2018 so from here we're gonna pull up a spreadsheet and then we're gonna we're gonna transfer these numbers onto the spreadsheet now mind you I'm doing this on the Mac and when I try to copy and paste numbers from this website using a PC on to excel it didn't work so I'm gonna try to figure out a way to do that on the PC it used to work even with you know just just a table on on who loved I was able to do that long time ago on Excel but for some reason right now it's not doing that so I'm gonna try to figure out a way to do that but but you know aside from that if you know I can you know I right now I can copy and paste this from this side onto my spreadsheet so let's pull it up quick alright so so we have this sheet okay so let's go back to the site okay so I'm going to highlight all this all the way down and got that highlighted command C to copy go back to the spreadsheet command V there you go see it's so easy to do it on here for some reason it sighs it's crazy right on the on the on the PC I guess I can't seem to get it done over there well over here is really easy it's just copy and paste like um so how I like to do this I like to create a space and kind of label you know the numbers and the Megane numbers and I'm gonna get rid of the Megaplier and the details column here so I'm going to insert a row okay so this is N 1 number 1 2 3 4 & 5 and the meta number okay and then we're gonna get rid of the Megaplier that's not important for this one okay so in this video we're going to use some form of us to handle different issues that we're going to find in the spreadsheet and you know it's gonna be pretty interesting I think so you know it's gonna be some techniques that you guys can use in different spreadsheets you know that you might run into so I think this is going to be a great practice even though this is for the lottery the techniques what we're gonna use here can be used anywhere else so just to be thorough we're going to see how many times in number comes up in this set so so the way you do that is so I like to lay it out a certain way so over here I'll create a so this is gonna be number and this is going to be frequency so how many times it comes up in this set and I'm not going to include the mega numbers because the regular numbers the five the first five numbers it's one to seven and then the mega number is one to twenty five so there's going to be a different different set that we're going to take a look at but first we're going to do be the first number to the fifth number on the on the lottery number page okay so we have to lay out the numbers here from 1 to 70 so the way I like the way I like to do that is I'll start it off with one two three okay oh I'll select that then just run it down to 70 and this probably easy way to do this is just how I do it so you can do it any way you want I'm just sure I'm just showing me the way I do it and I you know if you feel like doing it this way that's in that's cool if you find an easier way to do it that's great too so on the frequency we're going to use fine if okay so we're gonna say oh I'm sorry how give so we're going to count yeah it's gonna count how many times it comes duck in range so equals count yes right then we're going to highlight the first and we're first of five number set for the year do that I just happened sorry guys that was a glitch unless we do that so let's try to do it again do the range again so from here to here I don't do named ranges I know some of you guys like to do named ranges I don't like to do that it's probably easier to do it that way but I just don't do it okay so now I've got that selected I'm going to do make that the little click function at 4 [Music] and then the reference so it's going to stay it's going to look in and b22 f-102 and this corner it's going to look for k2 which is the number one right so let me post and that's why we have the we have the dollar signs there to keep that as a permanent reference right so that that's a permanent range is gonna keep on looking at it's not gonna change when you when you extend it all the way down to number 70 it's going to keep on going looking in therefore these numbers in that sense so sorry for the sound of the fence if you guys can hear that I put a fan cooler on my on my macbook pro but I'm using for their self I think that's interested in that I'll I'll give post a link in the and in the description so now we have that in there so we're just going to extend it all the way down okay so now it's counted how many times the real one came out and that's set number two number three number four number five every six okay so so number one came out eighteen times that were talking about twelve times number three qf 9 times 4 9 times 5 5 times 6 6 times 7 7 times 8 7 times 9 three times and vice versa right okay so now that we have that the next step is to find out how many times as each number come up in each column so and and the reason for that is we're going to group these numbers and they combine choose and ran between functions and I'll show you guys that is if it's Greek to you now if you understand it later I went you know once we start doing that it's going to choose between a set of numbers and then from in that set of numbers those are also randomizing in sets based on how many times you know if one of the if these numbers don't come up in that in that column why would you put it in there right so let's let's try it here they still coming up here on each on each numbered column here so I'm call them n 2 1 just call me each each of these columns the number with the numbers is a harder than one cells okay so you want to find out okay so in each column how many times did these come up and so they'll give you a percentage of frequency to make a better decision on how you're going to deal with randomizing it in a way that's smart so let's do that okay so now like we're gonna see how how we can find how many times these numbers come up in each row so sorry each column so I this is a previous formula that I came up with so I'm copied it from my previous spreadsheet when I was practicing it so so we're going to command E and I will go over demo it right so all right so the the equate the the formula is if is there and then you do a vlookup okay and then and then we're gonna change we're going to change these okay so we're going to look at each one of these and say so b2 to be 102 so those so that's this so from here to be 102 which is the end of the set in that column how many times does this number the number one comes out okay so we're going to change this to okay click on this see just a quick one yes so b12 is the last cell right and then we want to we wanted to come up with a true when it does find it in there it's going to say true we already know how many times it comes up in the set we just want to know where it comes up on the on the calls and I'll show you the reason for that later so so this is the equation so if it's a vlookup you know the regular vlookup equation and then false true okay so click enter here so now so we know that it is true in n1 okay we're going to change this also - so now we're not putting dollars on this one because we wanted to move down we want to change the reference going down so it'll go k3 on the next when we start pulling down this this formula to cover the rest of numbers okay so now so we know that so now we know that one comes up in the first position does not come up in the second position does not come up in the third position and vice versa I say they are all false except for position one okay so we know that one comes up and the first position on the on the number line here for the lottery so we're going to select all this now and we're going to extend it all the way down to number 70 all right so now you can see here that and then we have to go over the whole thing which I'm just going to you know show you so number one comes up in position one not and two three four five number two comes up in position one and two but not in three four and five number three the same thing number four comes up in positions one two three okay so so we have that part of the of the problems solved first we wanted to find out how if that number comes up in that column okay and that makes it easier later when we start well when we start randomizing and choosing what we put that formula together for choosing and randomize okay so now we're going to start that here and okay so now I brought in I brought in the formula that I have that I created in my other spreadsheet and we're gonna talk about this and I'll show you how it's done so and the reasoning behind why I did what I did okay so you'll see that the formula I use here is choose and then the ran between is that means gonna choose between positions one two low numbers one high number six so positions one so this position one which is the number one band between two Seventeen's positions here this is position three this is position for this is position five and this is position six okay again so this is position 1 all right which number why and the reason why it's it's that it's all number one's got it's got its own positions because the frequency that it comes out there is it came out 18 times in the year so that's about 18 percent right and since there's a you'll see later when we let me try to think that when we start putting these numbers and sets we'll see that some of these don't you know it's like like 75 don't come out you know suppose so the number one has its own position because it's and it comes up pretty often so one in six you know one two three four five six normally 80% will fall into like a 105 but since we can't do that we'll do a 1 of 6 so it'll choose it one of six times okay and then the other ones are set so that's so okay so then let's do the choosing of the sets so let's do that okay so now remember if you guys don't know I'm sorry if you guys don't know every time you click enter intercept you put something in the cell you click enter these will recalculate you know all of these will recalculate because you're doing a random number so the choose function here is going to choose a random number between one of six and one of those numbers is a position in the formula where one is this position to this position three is this position for is this position five minutes is position and six is this position okay so so now we're going to start putting the ISA numbers into groups and we're gonna have we're gonna use the number 1 here as its own group this opposition because it comes up pretty pretty often in the in the column and doesn't come out of any other column so we're gonna make its position pretty strong because we know it's going to come out 18 times right and so I came out 18 times in 101 instances so on this one we so on this one if you see here and in this cell I did a ran between 2 and 17 so on the randbetween function the the first number is the this one so the first number is the lowest number that's going to choose to a random number between so it's gonna do a random number between 2 and 17 so on this one 2 and 17 because 2 and 17 here are true okay so okay so let's let's look at that so number 2 to 17 here alright so we'll just we'll just put some color in that so we can we can identify which ones are in groups so number one is its own group so that's going to be the low outlier here and in and call in the number position number one position okay so the next two numbers and then remember 18 and 19 did not come out at all on here and position one okay so we're gonna skip the ones that say false because we know it was a very little probability of that and remember it's only for fun okay you know but it's just fun to talk about how you know how he would you know how you would go about solving the problem of getting a higher probability so we know that all year long number 18 and number 19 did not come out in the draw on the first position so those are false okay and then the other two numbers are here so this is the third group color that the fourth group is this so 27 28 29 is the fourth group okay the fifth group is number 31 okay the sixth group is 39 and 40 okay and then I will do their other true ones here yeah so we know that there are six positions that we have to do in this formula okay so so from the six positions we know that the first position is number one the second position is read between 2 and 17 the third position is ran between 20 and 24 the fourth position is a read between 27 and 29 okay the fifth position is going to be 31 and the sixth position is going to be 39 and 40 plus the it's going to randomize so so that's exactly what I did here so going up boy in these right you're you're you're looking here and no column 1 the first position is going to be the number one column is going to be the basis where all the other ones are going to be based off because you don't want to repeat any numbers that that exists both in 1 & 2 or 1 2 & 3 so you don't want to kind of step over each other right so yes because there's going to be a duration of this where the two two identical numbers come up and it's going to happen I tried it I made the mistake of of making this just straight up without making a caveat on the numbers so you'll see here so this is the basis right so on number on the first number and num number 2 column you'll see that say right between 18 and 19 because that's true here right although it is true here there's numbers that are true here as well but we covered all of those already and position number 1 okay I know this is going to start to get confusing here but just you know just follow me here so so we can so you can get it and then so that's 18 and 19 so ran between 18 and 19 is then choose between those two numbers whenever it chooses that position and be in the choose function and the next one is 25 or 26 so 25 and 26 right so so in position - you chose 18 and 19 25 25 that's my six now a nice person just go down you just go down you just can't overlap with the first call okay and the same thing goes for these other ones so whatever you're already generating the numbers are already generating and and in this column we can't generate here okay so you can't choose any of those numbers so that's why these are so high right so we have between 40 to 44 26 and 53 and 60 and 61 because you already generating those other numbers in here and same thing goes for position number four number four column okay whereas your you're going to choose between 55 and 56 59 I ran between 62 and 66 and then 68 and then and number five you're only choosing between 57 58 67 69 and 70 okay all right so you just go through this value you've created all of your numbers here and you can just copy this right and then let me know in the comment section if you guys want this the spreadsheet I'll send you comment with your email and I'll see whoever wants to whoever wants it as well okay so now when I did this I created 10,000 iterations of this set okay actually let's do the mega number two so the method was a little different because it has two only 25 members in there so let's take a look at that okay for this one we're going to do the mega number and your frequency over here so we're not going to so does that it doesn't get too confusing at high these two columns okay so it doesn't get confusing and then we're gonna use the same the same method to be used for the regular numbers that are not meta numbers so we know that if the middle numbers are 1 to 25 so 1 2 3 let's select all that pull it down up to the number 25 okay and then you're going to do a count if this is the range and we're going to make that the if the dollars answer down one the criteria would be this okay so just the same way we did the other one and there okay so now let me pull this is going is only going to reference that permanent set of numbers and the reference is going to change so now we know that these have come up this way so Part C in time I'm going to cut this for a little quick and I'm going to pull the the the count that I already did for and I already grouped it on the spreadsheet okay so now I have the the numbers pulled in from my other spreadsheet and this is going to be a little bit confusing so bear with me okay so in this one these are the numbers and these are the frequencies and I only took the top four okay you'll find that in here and this is set okay I only took the top four and then to group those so numbers one two and three go in one group and then seven goes into another group ten and eleven go to another group thirteen fourteen go into another bedroom and vice versa five 17 19 and 20 2015 4 okay so those are the only numbers I'm going to use for for my for my mega so let's put that in here so that's how many groups here so that is seven groups okay so they look nothing too mega here okay both choose beat one seven and we're doing this because we're going to choose between the position one to position seven in this formula and then the first position would be the set of numbers 1 2 3 so it will be ran between 1 , okay the second position is set so just put a 7 in that one comma and the third position will be 10 and 11 so there's probably a better way to do this this is just the way I did it okay and that's just 1314 and that's a 17 the nexus 19 and 20 and the last one is 23 and 24 okay so now we're going to make a lot of iterations of this so if you pull this down those numbers will keep on changing like so see so and as I keep pulling that down kind of keep on changing and then and that's a good thing actually for this so let's uh let me just make some space over here see I'm gonna do okay so now I'm going to pause this real quick and then I'm going to I'm going to make 10,000 iterations to this will be from here to do 1001 1000 1 ok and come back it'll be all populated okay now so it's also populated to about 10,000 iterations so we're going to hide this so that it's not confusing to look at [Music] okay okay so now we have 10,000 durations of this and to prove to you that there is so for a position t1 right now go put four zeros here up one two three four so we're at position ten thousand and two I'll row - ten thousand - okay now the reason for this the reason why we did this we wanted to get as many iterations as possible to get a homogeneous mix of sets of numbers right and that's going to set us up for the next thing we're going to do so now we have a data set of 10,000 sets okay so what we're not gonna you know put money on 10,000 sets of numbers unless you're able to of course no boy stopped you from doing that for but for mere mortals like I am you know I - I'm gonna choose maybe 40 40 stencils numbers it was just a little you know this is gonna be a $2 per set as the 80 dollars from you spend right for example for me yeah you know stove you want to get a homogeneous mix so what we want to do is we want to want to get 40 cents of numbers and it has to be random so so what do we do for random numbers you randomize right so so between 2 and 1,000 - so hit zero I'm sorry equals brands between 2 and 1000 - okay see every time we do that it changes that were so it's going to be a really good mix here so now we need about 40 of these alright so stern 6 37 38 39 40 okay now copy these because we're gonna pull out of our data set which is this one from from column T and why copy this because this is gonna be our our numbers down to do 40 okay so okay so you're probably wondering what these numbers are right so we generated these numbers here we randomized between two and one or ten thousand and two because we wanted to get the cell position right so so now we know that it's TUV wxy okay so now we have to we have to say this was for this cell this cell is going to be T to two nine six seven and then this cell is going to be you to 296 and vice versa right so so T - why now I got lazy and I just didn't want to research how to how to do that in a simpler way but this is how I did it and actually the way the way I did it made a change it basically is so let's say this is the first cell then we're going down to the 40th so this is going to be a H right so so these are thought with 200 cells anyway so we'll look at that later so the way I'm gonna do this is I'm going to say okay so equals and we're going to combine text and and what's in these cells okay so in order to do that you could do that okay so we want t in there and this oh okay you don't want that has to change okay so now it's it's not going to that cell right and there's a reason for that so let me go through this later you're gonna see you I we need to after we go after we populate this and then we're going to go into each cell and press together it'll go and we'll turn it to sell t 8 6 7-5 so I'm just gonna pull this all the way back here to got the dollar signs here so there's gonna be you know the dollar size be and there's probably an easier way to do this but I'm just too lazy to find out so that's B so W X and and the reason we did that is so definitely let me pull this down it's not gonna keep on referencing that one cell so all right this will populate the the rest of this okay you see every time let me do something in there it changes right so now I have to go into each cell now and click in there and see if you click in the formula bar and click enter or return watch it turn into that what's in that cell t47 one sorry I forgot to do something sorry I forgot one step so let's just do this so we have to do we have to paste values so copy Plan C copy and paste special values okay okay now we can go in there and do that all right I feel it I feel like a total dumbass right now I forgot to put a equals in there so that it'll do it so so if you look here this cell what's it equals in here click enter it goes through that there you go see he goes there so I'm gonna do that so all of these so if you can imagine if you keep on putting equals it key will keep on changing and I know this is how many 200 so 240 so basically clicking each cell and changing each number or making two hundred iterations of 10,000 sets is gonna keep on jumping around and pulling from you know pulling from these from these cells all over a data set so that's about two million two million iterations if I if I got my math right so so I'll pause this real quick I'll you know I'll do all that I'll keep on doing this and they'll get bored and I'll return I'm gonna have it all changed with equal size okay so now that's all populated we're just going to copy these values here so it doesn't change any more a special values okay so here your numbers now that'd be an order you mean you just you know you can just put that in order when you put it on the on the set on the scantron or stamp paper for the for the event a million slaughter so I'm sorry about my chair squeaky so yeah that's how I do it that's how I know that's how I did it so and like I said I'm just doing this for fun the way everybody anybody comes back and say well I did your technique and I didn't win anything well you know that wasn't the point you know we just doing this for fun so to see if you can actually do it on I'll expel and create numbers on Excel from the original data set and and eliminate the numbers that are you know or you don't think it's going to come out because they came up falls on those on those columns so anyway I hope this was this was entertaining for you all I know it was for me but then I you know I really like Excel and you know we like spreadsheets so if you haven't yet please subscribe me comments and in the comment box there and in the description you know you can put my links on there from from my affiliate links please support them and I'm also gonna put their you know a link to my people you know just in case you you find any kind of value in this what I'm doing you know please a help support my channel and that's it and so that's video thank you very much like or subscribe thank you [Music]

Channel: Tech By GP

Views: 30,876

Rating: 5 out of 5

Keywords: lottery, lottery ticket, lottery drawing, mega millions, mega millions drawing 2018, microsoft excel, excel, lotto result, lotto ticket, powerball, super lotto, superlotto plus, google sheets, google sheets tricks, lottery numbers, lottery number generator, lottery numbers for tonight, megamillions, california lottery, power ball, lottery jackpot, gambling, mega millions winner, lottery tickets, money, mega, big win, winning, mega millions jackpot, jackpot, billion, winning numbers

Id: WQDrZRzbClk

Length: 51min 2sec (3062 seconds)

Published: Tue Oct 23 2018