Dave wants to know how to choose a random name, but he does not want the complete list of names in the cells in the worksheet! Episode 1020 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! I'm Bill Jelen, from MrExcel.com.
I've got a cool Excel tip for you, today.
Hey! this is Mike Gel Girvin from Excel Is Fun on YouTube, and I have a different way to do that.
Welcome back to another dueling Excel podcast.
I'm Bill Jelen, from MrExcel.
Mike Girvin, from Excel Is Fun.
Today's question sent in by Dave at YouTube.
Dave wants to create a random name, like he doesn't want the list of random names to be in worksheet cells.
So normally, we would have a list over here.
These are the list to choose from equal index of that list.
Press the [ F4 ] key and then RANDBETWEEN.
Now, RANDBETWEEN is a function that is always available in Excel 2007.
In previous versions it was only available, if you had the analysis tool pack turned on.
So, we went RANDBETWEEN 1 and 8.
That tells us, give us a random row from the range D2, D9.
So, we got Ed, copy that down, you see we get a variety of names.
Every time we press[ F9 ], we get a new list of names.
But, Dave does not want this list over here in the spreadsheet.
So, check this out.
I'm going to use equal and point to that entire range.
Now, this is a special formula called an array formula.
It's entered in more than one cell.
So, we have to press [ ctrl + shift + enter ], which gives us exactly what we had.
You say, well what's the point of that? press [ F2 ] then [ F9 ] and it converts those lists of cells into a single array with curly brackets around it.
I press [ ctrL + C ] to copy that to the clipboard.
Come back here to my original formula and where I had D2 to D9 I'm now going to paste [ ctrl + V ] and a paste my list in there.
So, copy that down and now TADA!
We don't have to have this data over here.
We must press [ F9 ], [ F9 ], [ F9 ], [ F9 ]...
And without having a list anywhere, get a list of random names, built directly from the formula.
Hey! Let's throw over to Mike, from Excel Is Fun.
Mike: Thanks, MrExcel.
Hey! That was pretty good solution.
I don't really have a better solution.
I have some variants on that one because putting the array into the index function and doing some randomizing is really a good way to go.
Hey! I'm going to do equals Index and I'm going to for this array.
Just highlight these names here, they're in the cells.
We can delete them later, and then I'm going to come straight here highlight that and hit the [ F9 ] key.
That will evaluate it right in the middle of a formula and then you can just leave it there hard coded in and then comma, row number.
Just in case you don't have the analysis Tool pack and you don't have the RANDBETWEEN.
We could use INT for integer and then the RAND.
RAND this is an argument less function that generates a number between 0 and 1.
Now, the trick is this is 0 to 1.
So, if it comes out point 5, if I multiply that times the count here, 8 times point five would give us 4 and then it will pick the fourth name.
But, there's a problem here.
Well, let's go ahead and count these and I don't know how many there are?
So, I'm going to use COUNTA which counts words.
I'm going to highlight that there, closed parentheses.
And I'm just going to hard-code this in and do the same trick [ F9 ].
Just because I didn't know how many of words there.
Now watch this, sometimes RAND comes out to be really close to zero and what's something really close to zero times 8?
Well, it's almost zero and the integer function would give us zero and we don't want that because this is 1 to 8.
So, the trick is you always add one and then close parenthesis on the end close parenthesis on the Index.
And that function there will work, and then I could hit my [ F9 ] key and it does that.
Another solution is if you don't want a hard code them into the formula, you could copy that array and then [ ctrl + F3 ], to add a name [ ctrl + F3 ].
I'm going to click [ new ].
I'm going to call it, name come down here type equals better expand this.
So, we can see the whole thing I typed an equal sign and I'm going to [ ctrl + V ] and so there it is stored in memory, click [ ok ], click [ ok ] and then we could do our index, equals index and the array is the name. I'm going to hit [ F3. ] Double click that name, comma and we'll do our INT RAND times 8 + 1, close parenthesis, close parentheses and then So, that would be a name And then we could just delete these over here.
And now we have it as we hit our [ F9 ] key both those formulas will randomize.
Alright! We'll see you next trick.
MrExcel: Mike! That was cool pressing [ F9 ], right when you're building the formula.
That saves a whole hassle creating the array out there.
I also loved adding that to a name.
So, great you know, whoever started to keep score here.
I now regret it because I realized soon we're going to invoke the mercy rule and just throw the game to Mike.
But, actually we're all winners because we're all learning very cool ways to do things in Excel.
Well, thank you for stopping by on behalf of Mike, myself.
See you next time for another dueling podcast from Excel is Fun and MrExcel.
I've got a cool Excel tip for you, today.
Hey! this is Mike Gel Girvin from Excel Is Fun on YouTube, and I have a different way to do that.
Welcome back to another dueling Excel podcast.
I'm Bill Jelen, from MrExcel.
Mike Girvin, from Excel Is Fun.
Today's question sent in by Dave at YouTube.
Dave wants to create a random name, like he doesn't want the list of random names to be in worksheet cells.
So normally, we would have a list over here.
These are the list to choose from equal index of that list.
Press the [ F4 ] key and then RANDBETWEEN.
Now, RANDBETWEEN is a function that is always available in Excel 2007.
In previous versions it was only available, if you had the analysis tool pack turned on.
So, we went RANDBETWEEN 1 and 8.
That tells us, give us a random row from the range D2, D9.
So, we got Ed, copy that down, you see we get a variety of names.
Every time we press[ F9 ], we get a new list of names.
But, Dave does not want this list over here in the spreadsheet.
So, check this out.
I'm going to use equal and point to that entire range.
Now, this is a special formula called an array formula.
It's entered in more than one cell.
So, we have to press [ ctrl + shift + enter ], which gives us exactly what we had.
You say, well what's the point of that? press [ F2 ] then [ F9 ] and it converts those lists of cells into a single array with curly brackets around it.
I press [ ctrL + C ] to copy that to the clipboard.
Come back here to my original formula and where I had D2 to D9 I'm now going to paste [ ctrl + V ] and a paste my list in there.
So, copy that down and now TADA!
We don't have to have this data over here.
We must press [ F9 ], [ F9 ], [ F9 ], [ F9 ]...
And without having a list anywhere, get a list of random names, built directly from the formula.
Hey! Let's throw over to Mike, from Excel Is Fun.
Mike: Thanks, MrExcel.
Hey! That was pretty good solution.
I don't really have a better solution.
I have some variants on that one because putting the array into the index function and doing some randomizing is really a good way to go.
Hey! I'm going to do equals Index and I'm going to for this array.
Just highlight these names here, they're in the cells.
We can delete them later, and then I'm going to come straight here highlight that and hit the [ F9 ] key.
That will evaluate it right in the middle of a formula and then you can just leave it there hard coded in and then comma, row number.
Just in case you don't have the analysis Tool pack and you don't have the RANDBETWEEN.
We could use INT for integer and then the RAND.
RAND this is an argument less function that generates a number between 0 and 1.
Now, the trick is this is 0 to 1.
So, if it comes out point 5, if I multiply that times the count here, 8 times point five would give us 4 and then it will pick the fourth name.
But, there's a problem here.
Well, let's go ahead and count these and I don't know how many there are?
So, I'm going to use COUNTA which counts words.
I'm going to highlight that there, closed parentheses.
And I'm just going to hard-code this in and do the same trick [ F9 ].
Just because I didn't know how many of words there.
Now watch this, sometimes RAND comes out to be really close to zero and what's something really close to zero times 8?
Well, it's almost zero and the integer function would give us zero and we don't want that because this is 1 to 8.
So, the trick is you always add one and then close parenthesis on the end close parenthesis on the Index.
And that function there will work, and then I could hit my [ F9 ] key and it does that.
Another solution is if you don't want a hard code them into the formula, you could copy that array and then [ ctrl + F3 ], to add a name [ ctrl + F3 ].
I'm going to click [ new ].
I'm going to call it, name come down here type equals better expand this.
So, we can see the whole thing I typed an equal sign and I'm going to [ ctrl + V ] and so there it is stored in memory, click [ ok ], click [ ok ] and then we could do our index, equals index and the array is the name. I'm going to hit [ F3. ] Double click that name, comma and we'll do our INT RAND times 8 + 1, close parenthesis, close parentheses and then So, that would be a name And then we could just delete these over here.
And now we have it as we hit our [ F9 ] key both those formulas will randomize.
Alright! We'll see you next trick.
MrExcel: Mike! That was cool pressing [ F9 ], right when you're building the formula.
That saves a whole hassle creating the array out there.
I also loved adding that to a name.
So, great you know, whoever started to keep score here.
I now regret it because I realized soon we're going to invoke the mercy rule and just throw the game to Mike.
But, actually we're all winners because we're all learning very cool ways to do things in Excel.
Well, thank you for stopping by on behalf of Mike, myself.
See you next time for another dueling podcast from Excel is Fun and MrExcel.
