Warren from Australia writes about choosing Lotto numbers, but =RANDBETWEEN(1,45) is giving him duplicates. Today, in Episode #1401, Bill shows us another way to solve the problem and further eliminate the probability of duplicates. "Learn Excel"
Transcript of the video:
MrExcel podcast is sponsored by PowerPivot.
Visit facebook.com/powerpivot to take the NERD-TASTIC QUIZ. You can win an Xbox 360.
Learn Excel from MrExcel podcast. Episode 1401- Better Lotto Well, hey welcome back to the Mr.Excel netcast. I'm Bill Jelen.
Today's question sent in by Warren from Australia.
Warren is trying to pick lotto numbers using RANDBETWEEN and what Warren is discovering is if he presses F9 enough times he eventually runs into a situation where he's getting a duplicate.
Warren says that's a bug in RANDBETWEEN it doesn't seem to know that that numbers has already been picked.
Well that's not a bug in RANDBETWEEN. It's just the way that RANDBETWEEN works.
If you want to make sure that you don't get any duplicates I have a different way to go. Let's take a look at this big huge formula first and then we'll break the whole thing down.
First half here I just use =RAND, and I copy that down 45 times because Warren is picking from 1 to 45.
And then the big huge formula, this is gonna make your head spin the match of the large of all of those numbers comma column A1 Close that parentheses, close parentheses and then comma the numbers comma 0.
What the heck is this doing let's talk about it.
We'll break it down into the 3 pieces.
The column of A1 it's just a very clever way to get the numbers 1 through 6 going across because when I copy that across it changes to the column of B1 which is 2.
the column of C1 which is 3 and so on.
okay LARGE LARGE of a range column 1 is just like MAX.
That's easy But when we ask for column 2 column 2 right there, we're getting the second largest value and then the third largest value and then the fourth largest value and so on all right so now that I have this number here, let's press F9 a few times to get a nice small That's out there hey I love this one number 3 so we now have this number 0.955.
Okay, so what the MATCH is doing is saying.
Hey, go look for this number 0.955 in this whole range of numbers and tell me where it occurs and so in this particular case it's occurring in cell 3 so put all that together and we have our 6 numbers, and it should very very rarely repeat. It's only gonna repeat if one of these random numbers happens to occur twice which is such a large amount of precision there in those random numbers.
It's very unlikely that you're ever gonna get a duplicate within 45 it might happen much less likely then the RANDBETWEEN that Warren have.
Well hey I want to thank you for stopping by. See you next time for another netcast from MrExcel.
Visit facebook.com/powerpivot to take the NERD-TASTIC QUIZ. You can win an Xbox 360.
Learn Excel from MrExcel podcast. Episode 1401- Better Lotto Well, hey welcome back to the Mr.Excel netcast. I'm Bill Jelen.
Today's question sent in by Warren from Australia.
Warren is trying to pick lotto numbers using RANDBETWEEN and what Warren is discovering is if he presses F9 enough times he eventually runs into a situation where he's getting a duplicate.
Warren says that's a bug in RANDBETWEEN it doesn't seem to know that that numbers has already been picked.
Well that's not a bug in RANDBETWEEN. It's just the way that RANDBETWEEN works.
If you want to make sure that you don't get any duplicates I have a different way to go. Let's take a look at this big huge formula first and then we'll break the whole thing down.
First half here I just use =RAND, and I copy that down 45 times because Warren is picking from 1 to 45.
And then the big huge formula, this is gonna make your head spin the match of the large of all of those numbers comma column A1 Close that parentheses, close parentheses and then comma the numbers comma 0.
What the heck is this doing let's talk about it.
We'll break it down into the 3 pieces.
The column of A1 it's just a very clever way to get the numbers 1 through 6 going across because when I copy that across it changes to the column of B1 which is 2.
the column of C1 which is 3 and so on.
okay LARGE LARGE of a range column 1 is just like MAX.
That's easy But when we ask for column 2 column 2 right there, we're getting the second largest value and then the third largest value and then the fourth largest value and so on all right so now that I have this number here, let's press F9 a few times to get a nice small That's out there hey I love this one number 3 so we now have this number 0.955.
Okay, so what the MATCH is doing is saying.
Hey, go look for this number 0.955 in this whole range of numbers and tell me where it occurs and so in this particular case it's occurring in cell 3 so put all that together and we have our 6 numbers, and it should very very rarely repeat. It's only gonna repeat if one of these random numbers happens to occur twice which is such a large amount of precision there in those random numbers.
It's very unlikely that you're ever gonna get a duplicate within 45 it might happen much less likely then the RANDBETWEEN that Warren have.
Well hey I want to thank you for stopping by. See you next time for another netcast from MrExcel.
