Learn Excel - A Better Lotto: Podcast #1401

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on May 10, 2011.
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"
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top