Use random number functions for sorting and creating sample data

- [Voiceover] Excel has two functions called random number functions. And although they're not for everybody, they do have important uses. I've got a list here and from time to time it grows, it shrinks, and periodically we wanna do some spot checking of our recording process here and make sure that information is correct. We'd like to pull eight or 10 of these at random. You can sort a list in random order. Imagine how you might be doing this with a list of orders or shipments. It's a certain form of quality control. You just wanna pull a random selection of these.

How do we sort this in random order? If you've got a list in a column to the right, a temporary new column, if you are gonna be using a column, make sure if you've got any data to the right that it's separated by at least one empty column. You don't really have to put in a heading, but it's not a bad idea. I usually just type in X-X, something like that. Occasionally Excel will misread this and treat the top line as if it were data, so that's why we might do that. Often it doesn't do that, so it's not a major concern. But here we type in the function called rand. It's one of those functions that's followed by parentheses with nothing between the parentheses.

As you type this you need only type equal rand left parenthesis. I'm going to be copying this down the column, so I won't press Enter, I'll press Control + Enter. Rand simply generates a random number between zero and one. We don't care what it is. To copy it down the column I'll double-click here, just to make sure it got copied all the way to the bottom I'll press Control + period, zip down to the bottom there, yep, looks like we got it covered. Control + period, back up top. Now you can do a quick sort by simply clicking on a cell in a certain column, Excel will sort your contiguous list based on the data in that column.

We're gonna sort on column J here. I'll click on one of the cells here and click either the A-Z or Z-A button, makes no difference, either one, and the list has been sorted on the basis of those numbers. And in the meantime, because we made a change to content the random numbers have been regenerated. We don't care about that really, but the list as we now see it is in no particular order. As you look through each column here you can see it's in no special order whatsoever, it's in random order. And if we wanted to simply copy the first eight, 10, 12 records, whatever, we'll just highlight these, copy them somewhere else, review the details, and that's sort of our way of saying we checked out the data randomly, it looks pretty good.

We don't need this anymore, click over here, press Delete. Another function is called rand between. And it's for whole numbers, although you can use it for other applications. It's often used for sample data. I need some sample sales data for these states across a five day period here. Say it's the last week. And in most cases based on recent performance we're talking about sales here of about 50, 80 thousand at most here. So let's start with the idea, we wanna put in some random numbers, but we also wanna take into account the size of the states.

And maybe our sales have been somewhat roughly in proportion to the population, so off to the right ahead of time I put numbers out here that represent the state population in a rounded millions number out here. California has 39 million. So we're gonna use rand between here. I'm gonna highlight all the cells first, if you highlight cells ahead of time and type an entry, press Control + Enter, it'll fill in all the cells. And if it's a formula it'll fill them all in appropriately. Equal rand between is the function. Rand between as the description shows returns a random number between the numbers you specify.

I'm gonna make up 1,000 and 2,000. We wanna range of numbers between these two numbers. If the sample data that we create looks too spread out, too disparate, we can change this to be 1,500, try it again possibly. I'll put in 1,000 to 2,000. Now if I were to press Control + Enter now we'd have a series of numbers here. Remember it's seven states by five days. 35 different numbers here that would be between 1,000 and 2,000. But I wanna pull in that factor of the state population, so I wanna take this number, whatever it is, and then multiply it by the data over in column R.

And I wanna be able to copy this into all the cells, so I'll press the function key F4 to make sure the dollar sign is in front of the R, but not in front of the two. And I complete the entry here, Control + Enter. We've got our random numbers. Now randoms numbers contain this function, and an odd fact I mentioned earlier is they change as we make changes in the worksheet. So if, for example, if I go out to the right here and type a Space + Enter, or a number + Enter, whatever, watch those numbers change like that.

You can also press the function key F9. There could be times when you get a set of numbers and they just are not to your liking for whatever reason. Reconsider all this. If we wanted to go back here again, highlight all these and make them a little tighter in the variance by simply taking that first function, we can press F2, or go to the formula bar here and make that be 1,500 in our multiplier. And then as we press Control + Enter a whole set of new numbers, they're not gonna be as large in general, but there they are. Now they change, as I pointed out earlier, with any other change in the worksheet.

So what do you do when you say I don't want them to change anymore, I want to freeze them? Nice little shortcut here. We can point to any edge, say this one, the bottom edge, hold down the right mouse button and temporarily drag this into another location and then right back on top of itself. So I'm using the right mouse button, remember that means shortcut menu, we haven't see it yet, I'm gonna drag down and up. Down, up, let go of the right mouse button, copy here as values only. One of Excel's great shortcuts, not so widely known.

What this really should say right now is paste the results of the formula or the results of the function right here. That's what we're doing. So we've thrown away the formula, the rand between, we've got our random numbers, they're not gonna change now. Another example where you might need an adjustment, we've got some items, we run a small store, a lot of our items are between 10 and 100 dollars, so let's put up some sample items here that we're gonna experiment with pricing changes. We want a sample set here of numbers between 10 dollars and 99 dollars.

Equals rand between, 10 comma 99, Control + Enter. It looks like that. Now to make that a little bit more interesting instead of those all being even numbers why don't we make a change here too? I'll press the function key F two. What if we could make these be between 1,000, let's add two more zeros there, and make this be 9999, and then divide this by 100, and I'll press Control + Enter. Yeah, that's a more interesting set of numbers let's say. And here too, if we wanted to freeze these we can, with the right mouse button I'm gonna drag this into column N, right back on top of itself, let go of the right mouse button, copy here as values only.

Now there are times when you need to create sample data between certain dates or certain times. I need some sample data here between these dates. And here I'll just start with a single cell, equals rand between this date, now I am gonna be copying this down the column, so I'll use the function key F4 here to make that an absolute address, comma, and our ending date right here, here to, I want that to be an absolute address, 'cause I'll be copying the formula, complete the entry with Control + Enter, and the number doesn't look quite the way we would want it to, but we'll adjust that.

I'll drag this down to here, or as far as I need to, and if I don't mind copying the color as well I can copy the formatting of this downward using the right mouse button. There are other ways to do this too. I'm dragging downward with the right mouse button, fill formatting only. So we've got a bunch of random dates here. Now here to, if I make changes, maybe I want that to go to December 30th, if I make a change here all these numbers will change below. And as we said earlier, if I make a change anywhere in the worksheet, maybe just step out here, type Space, Enter, watch those dates all change except for the top two.

So after you create these remember, at some point you want to freeze them, we'll use that same technique. Cells are highlighted, right mouse button, drag down, up, or right, left, makes no difference, and then copy here as values only. And you might want to have a time range as well. This gets a little trickier. This number, if we were to look at it as a value, and you can do this a couple different ways, but you could go on the home tab and simply display this in general format temporarily here, that's actually a value that's between zero and one. So here it gets a little trickier, because we need to in our rand between function here actually multiply this value by the number of minutes in a day.

And that's 60 times 24, happens to be 1,440. Then we need to divide our result by it. So that's not exactly obvious. When you work with dates and times sometimes you encounter these issues. Equal, rand between, left parenthesis, so here's our starting date, needs to be an absolute address, since we will be copying this down the column, F4, we need to multiply it by 1,440, the number of minutes in a day, comma, here's our ending time, there too F4 to make it an absolute address, times 1,440, right parenthesis, and then we need to divide all this by 1,440.

I'll press Control + Enter here and we've got a time. And I'll copy it down the column as far as we need this. Doesn't necessarily have to be as long as the previous column. Over to the left we've got a bunch of random times. And here too, as in our previous examples, we would copy this to itself. The formula not exactly obvious, let me make column V wider here, see that a bit better if we double-click. It's still wrapping around, but I think you get the idea as to how that's working. And you can also see it in the formula bar too. A little trickier, but again, that gives us a lot of flexibility when we're creating sample data.

So rand and rand between, two functions that not everybody needs, but time to time, and really valuable as shown by the examples we've seen in this movie.