This week, Bill is in New York City to judge the ModelOff Worldwide Excel Championship! Today he investigates: "Does anyone really simulate the shuffling of a deck of cards with Microsoft Excel?"
Yes!
Today, in Episode #1610, Dan Mayoh shows us how to replicate this action - without producing any duplicates.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
Yes!
Today, in Episode #1610, Dan Mayoh shows us how to replicate this action - without producing any duplicates.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
Transcript of the video:
Bill Jelen: Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Hey, we’re here at the ModelOff World Financial Modeling Championships in New York City.
I have some of the Excel rock stars that are finalists here today.
This next trick is Dan Mayoh with shuffle a deck of cards --what a great trick in Excel.
All right Dan, rolling over.
Dan Mayoh: Hello everyone.
Bill Jelen: So Dan, where are you from?
Dan Mayoh: I'm from Australia Bill Jelen: Australia.
Bill Jelen: That’s cool.
How did you hear about the competition?
Dan Mayoh: Uh, it was posted on a Excel forum that I'm a member of, someone put up -- here is this competition.
So anyone here you know, who really thinks they're Excel good, have a go at it.
Bill Jelen: That's great Dan Mayoh: So I did.
Bill Jelen: That's good.
And so you entered, you went through the first two rounds and you're one of the top sixteen in the world.
Dan Mayoh: That’s correct, I’m excited.
Bill Jelen: They sent you here to New York City for the big competition tomorrow.
Dan Mayoh: Yes.
Bill Jelen: All right, so tell us what your trick is here today.
Dan Mayoh: Okay my trick here is, essentially how to simulate the random shuffling of a deck of cards in Excel without the need for any macros.
I do a lot of gaming analysis on Excel that often involves having you know, anywhere from one deck to eight decks of cards, needed you know, to simulate shuffle and then you run your computations to see what the result would be.
So this trick enables me to basically put the numbers 1 to 52 in a random order.
And I can say, you the 1-52 represents a deck of cards and it might be 1-4, the aces 5-8 or the twos and so on.
Bill Jelen: Makes sense.
Dan Mayoh: Essentially, you know, you can extend this to be wider -- just numbers 1 to whatever you want in order without any repetitions.
Bill Jelen: So why don't you just use RANDBETWEEN, =RANDBETWEEN 1-52?
Dan Mayoh: Well, currently if we use the RANDBETWEEN function with the arguments 1-52, we'll get a random number between 1-52.
If you do it 52 times, you are not going to end up with all the 52 numbers occurring once each; you're very, very likely going to have some doubles in there and some numbers missed out.
Bill Jelen: Ah, so your method is going to get us no duplicates, all right.
Dan Mayoh: That’s exactly right.
Bill Jelen: Okay good, show us how it works.
Dan Mayoh: Okay, we start with just using the RAND function and then open closed bracket =RAND().
And that gives us a number just between 0 and 1 to quite a few decimal places.
So we have 52 of those so I've got them in cells A1 to A52 here.
Next to that, will just actually put the numbers 1 to 52 so just started with 1, column B equals the cell above plus 1 and round it down.
Bill Jelen: Alright.
Dan Mayoh: Yep.
Okay and now here's the trick -- at this point we could use say the Sort command.
If we highlight the columns A and B and sort it on column A, that will change the numbers to B but if we wanted to do this you know, over and over and over again because we're doing some sort of simulation well then we have to program a sort command into you know, our B-B-A routine; or if we're doing it manually -- we have to manually sort each time and that can be time-consuming.
Bill Jelen: Right.
Dan Mayoh: So we can actually just use the LARGE function instead.
So here I've got a simple formula in column C which just references the 52 values in column A -- and then picks either the largest, the second largest, the third largest or so on.
And that's why we have the values in column B there, they're just essentially anchors to say up in row 1, I want the largest, number 2 for the second.
We could have also just instead of the reference to column B, we could have say just a ROW argument -- but this is a bit more general, it doesn't require us having it ROW one of the worksheets.
Bill Jelen: Right, yeah.
Dan Mayoh: So all that's done now is giving us our random values from column A but it's put them in order for us.
Still not all that much good to us, so we need one more function and this is just in column D -- the MATCH function.
And this will take our value in column C which in row 1 we know is the largest, in Row 2 we know is the second largest, and so on.
And find where did that value occur in column A, it might be in the 1st spot, might be in the 52nd spot, might be somewhere in between and it will return for us where that value occurred in column A.
Bill Jelen: That's, that's great.
I think, heading over column D, I felt sure you were going to have a VLOOKUP over there doing a VLOOKUP between A and B but you made it even shorter just using the MATCH.
The MATCH tells us a position that gives us number 1, all right.
Dan Mayoh: Yes, I much prefer using INDEXs and MATCH rather than VLOOKUPs, but that's a discussion for another video.
Bill Jelen: Next year's VLOOKUP will have to have a little dueling thing that could use INDEX and MATCH instead of VLOOKUP.
I get it, especially in this group.
All right Dan, hey thanks for that great trick.
Dan Mayoh: You're welcome Bill.
Bill Jelen: Good luck tomorrow in the competition.
And thanks for this great tip.
Dan Mayoh: Thank you.
Bill Jelen: I want to thank everyone for stopping by.
I’ll see you next time for another netcast from MrExcel.
Hey, we’re here at the ModelOff World Financial Modeling Championships in New York City.
I have some of the Excel rock stars that are finalists here today.
This next trick is Dan Mayoh with shuffle a deck of cards --what a great trick in Excel.
All right Dan, rolling over.
Dan Mayoh: Hello everyone.
Bill Jelen: So Dan, where are you from?
Dan Mayoh: I'm from Australia Bill Jelen: Australia.
Bill Jelen: That’s cool.
How did you hear about the competition?
Dan Mayoh: Uh, it was posted on a Excel forum that I'm a member of, someone put up -- here is this competition.
So anyone here you know, who really thinks they're Excel good, have a go at it.
Bill Jelen: That's great Dan Mayoh: So I did.
Bill Jelen: That's good.
And so you entered, you went through the first two rounds and you're one of the top sixteen in the world.
Dan Mayoh: That’s correct, I’m excited.
Bill Jelen: They sent you here to New York City for the big competition tomorrow.
Dan Mayoh: Yes.
Bill Jelen: All right, so tell us what your trick is here today.
Dan Mayoh: Okay my trick here is, essentially how to simulate the random shuffling of a deck of cards in Excel without the need for any macros.
I do a lot of gaming analysis on Excel that often involves having you know, anywhere from one deck to eight decks of cards, needed you know, to simulate shuffle and then you run your computations to see what the result would be.
So this trick enables me to basically put the numbers 1 to 52 in a random order.
And I can say, you the 1-52 represents a deck of cards and it might be 1-4, the aces 5-8 or the twos and so on.
Bill Jelen: Makes sense.
Dan Mayoh: Essentially, you know, you can extend this to be wider -- just numbers 1 to whatever you want in order without any repetitions.
Bill Jelen: So why don't you just use RANDBETWEEN, =RANDBETWEEN 1-52?
Dan Mayoh: Well, currently if we use the RANDBETWEEN function with the arguments 1-52, we'll get a random number between 1-52.
If you do it 52 times, you are not going to end up with all the 52 numbers occurring once each; you're very, very likely going to have some doubles in there and some numbers missed out.
Bill Jelen: Ah, so your method is going to get us no duplicates, all right.
Dan Mayoh: That’s exactly right.
Bill Jelen: Okay good, show us how it works.
Dan Mayoh: Okay, we start with just using the RAND function and then open closed bracket =RAND().
And that gives us a number just between 0 and 1 to quite a few decimal places.
So we have 52 of those so I've got them in cells A1 to A52 here.
Next to that, will just actually put the numbers 1 to 52 so just started with 1, column B equals the cell above plus 1 and round it down.
Bill Jelen: Alright.
Dan Mayoh: Yep.
Okay and now here's the trick -- at this point we could use say the Sort command.
If we highlight the columns A and B and sort it on column A, that will change the numbers to B but if we wanted to do this you know, over and over and over again because we're doing some sort of simulation well then we have to program a sort command into you know, our B-B-A routine; or if we're doing it manually -- we have to manually sort each time and that can be time-consuming.
Bill Jelen: Right.
Dan Mayoh: So we can actually just use the LARGE function instead.
So here I've got a simple formula in column C which just references the 52 values in column A -- and then picks either the largest, the second largest, the third largest or so on.
And that's why we have the values in column B there, they're just essentially anchors to say up in row 1, I want the largest, number 2 for the second.
We could have also just instead of the reference to column B, we could have say just a ROW argument -- but this is a bit more general, it doesn't require us having it ROW one of the worksheets.
Bill Jelen: Right, yeah.
Dan Mayoh: So all that's done now is giving us our random values from column A but it's put them in order for us.
Still not all that much good to us, so we need one more function and this is just in column D -- the MATCH function.
And this will take our value in column C which in row 1 we know is the largest, in Row 2 we know is the second largest, and so on.
And find where did that value occur in column A, it might be in the 1st spot, might be in the 52nd spot, might be somewhere in between and it will return for us where that value occurred in column A.
Bill Jelen: That's, that's great.
I think, heading over column D, I felt sure you were going to have a VLOOKUP over there doing a VLOOKUP between A and B but you made it even shorter just using the MATCH.
The MATCH tells us a position that gives us number 1, all right.
Dan Mayoh: Yes, I much prefer using INDEXs and MATCH rather than VLOOKUPs, but that's a discussion for another video.
Bill Jelen: Next year's VLOOKUP will have to have a little dueling thing that could use INDEX and MATCH instead of VLOOKUP.
I get it, especially in this group.
All right Dan, hey thanks for that great trick.
Dan Mayoh: You're welcome Bill.
Bill Jelen: Good luck tomorrow in the competition.
And thanks for this great tip.
Dan Mayoh: Thank you.
Bill Jelen: I want to thank everyone for stopping by.
I’ll see you next time for another netcast from MrExcel.
