Description

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!

Transcript

Bill Jellen: Hey I’m Bill Jellen from Mr. Excel.com and I’ve got a cool Excel tip for you today. Mike Gel Girvirin: Hey this is Mike Gel Girvirin at Excel is fun at YouTube and I have a different way to do that. Bill Jellen: Welcome back to another dueling excel pod cast, I’m Bill Jellen from mr excel, Mike Girvirin from excel is fun, today’s questions and invite, Dave at youtube. Dave wants to create a random name but he doesn’t want the list of random names to be in worksheet sells. So normally we would have a list over here, this are a 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 or not. So what randbetween one and eight excel gives us a random row from the range D2 to D9. Thus we have Ed, copy that down you still get a bride of names and every time we press F9 we get a new list of names, but Dave does not want this list over here in his worksheet, so check this out. I’m going to use equal, and point at that entire range. Now this is a special formula, called an array formula, it’s entered in more than one cells, we have to press ctrl + shift + enter, which gives us exactly what we had, you say “what’s the point of that?” press F2 then F9 and it converts those list of cells into a single array with curly brackets around it. Now press ctrl + c to copy that to the clipboard, come back here in my original formula and where had D2 to D9 I’m now going to paste ctrl + v and it pastes my list in there so copy that down and now, we don’t have to have this data over here, just press F9, F9, F9, F9, F9, F9, and without having a list anywhere, get a list of random names built directly from the formula. Hey let’s go over to Mike from Excel is fun. Mike Gel Girvirin: Thank’s Mr. Excel. Hey that was pretty good solution, I don’t really have a better solution, I have some variance on that one because putting an array in 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 put this array just highlight these names here, their in the cells, we can delete them later. And then I’m going come straight here highlight that and hit the F9 key, that will evaluate that right in the middle of the formula and then you can just leave it there, hardcode it in, and then comma row number just in case you don’t have the analysis tool pack and you font have the RandBetween, we could use int. for integer, and then the ran. Ran this is an argument less function that generates a number between zero and one. Now the trick is, this is zero to one so if it comes out point five, if I multiply that times the count here, eight times point five would give us four and it would 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 count off, which counts words, I’m going to highlight that there, close parenthesis. And I’m just going to hardcode this in, do the same trick F9; just because I didn’t know how many there where there. Now watch this, sometimes rand comes out to be really close to zero, and what’s something really close to zero times eight? Well it’s almost zero and the integer function would give us zero and we don’t want that because this is one to eight. So the trick is you always add one; and then close parenthesis on the int. 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 to hardcode the name to 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 could 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. And then we can 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 eight plus one, close parenthesis, close parenthesis, and so that would be a name. And then we could just delete these over here, and there we have it as we hit our F9 key both this formulas will randomize. Alright we’ll see you next trick. Bill Jellen : Alright, Mike that was cool pressing F9 right when you’re building the formula, that saves a little hassle occurring the array out there also looked, adding that to a name. So, great, you know whoever started keep score here, I now regret it because I realize 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 it very cool ways to do this in excel. I want to thank you for stopping bye. On behalf of Mike, and myself, see you next time for another dueling pod cast, excel if fun and Mr. Excel.