 Statistics and Excel. Sort data randomly. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth, soothing Excel. First, a word from our sponsor. Well, actually, these are just items that we picked from the YouTube shopping affiliate program, but that's actually good for you. Because these aren't things that were just given to us from some large corporation which we don't even use in exchange for us selling them to you. These are things that we actually researched, purchased, and used ourselves. Focusrite Scarlett Solo 3rd Gen USB interface with software suite. I've been using a Focusrite for years for my audio needs before which time I had a USB microphone which plugged directly into the computer. But I think you'll find, as I have found, if you want to increase the quality of your microphone, you will need an interface and the Focusrite is the go-to interface as far as I'm concerned. I've been using this for years now. It works well. It's easy to use. It seems quite durably built because I only do the screen recordings. I only need the one Solo interface. However, if you have multiple microphones you need to plug in or if you have other instruments you need to plug in, you can look at a similar model that has more input ports. If you would like a commercial-free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com Where we have many different courses, you can purchase one at a time or have a subscription model given you access to all the courses. Courses which are well organized have other resources like Excel files and PDF files to download and no commercials. Here we are in a blank Excel worksheet continuing to think about how we can get and how we can organize data in such a way that we can practice our statistical tools on it. In prior presentations, we thought about how we can create the data within Excel and we also thought about some ways that we might get data from other sources and then organize that data once we get it into Excel so we can do our statistics on it. In other words, when we get data from other sources it might be just in one cell. Once we copy and paste it into Excel it might be deliminated or separated by commas and or spaces. We want to be able to separate the data and put it from a row into a column because that's how we usually like to see the data when we apply our statistical tools such as creating functions like the average function, the median function. And when we insert, go into the Insert tab and insert things like histograms and box and whiskers, for example, back to the Home tab for the default. So now we want to think about well what if I have like numbers that I either received somewhere else or I made myself in Excel that I want to then randomize. They're already in order and now I want to randomize those numbers. So what I'm going to do first I'm going to do like I do every time I'm going to format my cells. So I'm going to select the entire worksheet by selecting the triangle or control a right click on the selected area format the cells. I'm going to go into then the currency and then negative numbers bracketed and read, remove the dollar sign. I'm not going to be working. Well, I'll actually keep the decimals because we will have some decimals we'll be dealing with. So I'll keep the decimals this time and then we'll hit OK. All right, so let's say we have some data that is in order. Let's say we're going to create the data in Excel. So if I create an ordered list of data there's a couple of ways that we can do that. I could say for example that I'm going to start with 1000 and then I'm going to go to 3000. And now Excel will recognize that pattern if I start to copy it down. So what I'm going to do is I'm going to use the autofill not exactly copy. I'm going to select the two cells. This is cell A1 and A2. Put my cursor on the fill handle, which is this little box. So it looks like that square right there. There's the square or the plus button and then left click and drag that down. And you can see Excel is now seeing a pattern and it's now populating based on that pattern. I'm making the cell a little bit larger. Put my cursor between A and B left clicking and making it larger. So that's one way you can kind of create a pattern and maybe once you have the data that already has a pattern in it. Maybe you want to randomize the data, right? Usually when we're practicing statistics we're getting data that is not in a pattern because we got the data from some kind of test. So if we take a test and we randomly choose people, ask them how tall they are or how much they earn. Then we're going to get data that is in order of when we asked someone, right? Or possibly it's an alphabetical order by who we asked or by number of the person we asked. It's not going to be in order by the data and usually then we want to sort the data from top to bottom or bottom to top. But possibly sometimes you want to do the reverse. You want to make the data and then randomize the data, especially if you're creating your own kind of data set to practice how these tools work. So that's one way you can come up with a series of data. Now another way you can do that is a formula. I'm going to delete this. You might say that you have a thousand up top. And now what you want to do is say the pattern I'm going to put in place is going to be equal to the cell above it. Plus let's do it a little bit different. 1,500, right? So now I've got that pattern and I said times, didn't I? I'm double clicking on it. I should have said plus. That's what I said, but that's not what I did. So now every time I copy this down, it's going to take that relative reference A1 and it's going to pull that down. And we've got to get used to this relative reference when we start to practice in Excel. This is hard coded. That's going to be the same. But when I copy this A1 down, it's going to assume that I want to bring it down to A2 when I copy this cell down. So that's going to be what I mean. I'm going to put my cursor on the fill handle and left click, drag it down. If I double click on the cell to see what happened, now I mean it's taking the one above it, which is now A2 plus the hard coded or typed in number 1,500. So that's another way you can pretty easily make a series of numbers, right? So now it's taking a series of numbers based on the function that we use. And you can use it, you can use it, you can multiply it times something if you wanted to, right? I can delete this and say I can take the function that's going to be equal to the one above it times 100% 0.07 just to have a random one. So I'm going to take 100% of it and another 7%. So I added $70, 70% of a thousand. So it's 170% of a thousand. And again, I can copy that down. So let's say we have this row of numbers here. Now if I want to make this row of numbers random, if I mix up the order of these numbers, it's going to have a problem because these numbers are not hard coded. This is a formula. So if I want to keep those numbers as they are, even though I'm going to change the order of the numbers, what I want to do is copy these numbers. Control C. I'm going to paste them like over here in D, right click, and I'm going to paste them 123. This is what's called hard coded numbers. So now they're hard coded. This number is not dependent on the cell above it. So now that I have just hard coded data, just a list of numbers. If I want to organize this list of numbers randomly, this is one method that you could use. You could use our trusty random function, which looks like this, equals R-A-N-D. We saw it in a prior presentation. Double clicking on the random. It doesn't have anything we need to fill in because it's just going to choose a random number and it's going to put it in decimal format, right? So there it is, a random number between zero and a hundred percent, basically, right? Or one and a hundred percent. I'm not sure if it includes zero. But in any case, random group of numbers. If I put my cursor on the fill handle and drag that down, now it's giving us a random list of numbers. Now we have the same kind of issue that anytime I do something, it's going to re-randomize, right? So it keeps on recalculating. But it's random. So I would like to now order these numbers in accordance with this random set. So what I'm going to do is hard code the random numbers now. I'm going to make them into this function from the function to just the numbers that have now been generated. I'm going to select them all to do that, right click on them, copy, and then I'm going to paste right over the top. But instead of pasting normally, I'm going to paste one, two, three, values only. That's how I always think of it. One, two, three, values only. So there it is. So now I've got, these are hard coded. If I double click on them, it's a random number that's been hard coded. So now if I sort this column from like lowest to highest or highest to lowest, and I, and I lined these up next to it, then I should have a random kind of grouping of these numbers, right? So there's a couple ways I can do that. So what I'm going to put some headers here to do this because I want to filter these together. So I want something above these two items. So what I'm going to do, I could do that a couple different ways. I can select the entire thing, put my cursor here and drag it down. But that would be tedious if I had a lot of data. The other way you can do it is you could select the entire thing. You can say right click and cut or control X. And then I could put my cursor here and right click and paste it. That's the same thing as moving it down. I'm going to undo that. Or I can insert above. This would be the easiest way to do it. So I'm going to get rid of the dancing ants by double clicking. Meaning I don't want anything else selected or copied. I'm going to select these two here, right click and insert above. And this will push down. So I want these to push down and that'll put those two cells above because there's nothing below this. So that's the easiest thing to do. Otherwise it would have moved this down and maybe I don't want to move those down. Okay, so then I'm going to put this is this is the random and this is the the numbers or whatever. I'm just going to put some headers because once I add filtering cells, it needs to have a header on it typically. And to let Excel know that this is a header. It's useful sometimes to go to the home tab alignment center it and then font and bold it. And now that let's Excel know hey those are those are headers when you enter a chart or a table. Now if I select this data, there's a couple ways I can do the filtering. I can go to the selected data. I could go to the data tab up top and then we can go into the sort and filter group. We can add filters and that puts our filter these little filter boxes on it. And then I can filter it this way with a sort button from Z to A. So now it's it's going, you know, it's more randomly selected down here from Z to A based on this or I can take it from. I'm sorry that's a to Z or from Z to A. So now I put the highest one on the random selection here which basically randomized these numbers. So that's one way you can kind of randomize them. And then once they're randomized, you might just delete, you know, this this side of it and then you've got your random numbers over here. Now another way to do this is sometimes I don't like the filtering these filters because I'd rather have a table. So usually what I would do I'll undo it. I'm going to undo undo undo to get rid of those filters is I will select a table. So I'll select any cell within this group of cells and because they all have something in it when I enter a table, it'll select the proper range. So I don't even have to select the entire range. I can just go to the insert tab and then in the tables group at a table and then it selects the proper range. So it's saying don't worry about the dollar signs for now it's absolute reference but it's going from C1 to D to D8. And that's this range C1 this cell to this cell D8. Notice again that it's able to list the entire box with only two cells. It doesn't need four corners. It just needs two cells. So that's the range that we that we that's how we can label an entire box. So then we're going to say all right insert the table and now now they're the reason I like this better is because I feel it's less likely that these two things are going to get disjointed. Instead of just having added the data field which I feel like well what if it sorts this side but not this side along with it with a table I feel more confident that it's sorting it right. If I then say this sort from Z to A then it now sort of this side randomly right. And so then I can delete the random if I don't need it by selecting the entire column right click on that column and I'll just delete the column and now I've got these numbers in basically a random order. So the problem is of course that once I sort them orderly I hit the drop down and say sort A to Z now they're back in order that's usually what I want to do right. Usually I have random numbers and I want to sort them in order but but Excel doesn't really have us it's like entropy kind of like right right once once it only goes one way right it doesn't go back. Usually things get entropy things get more scrambled I guess but in this case once they're ordered then then Excel doesn't want you to go back to unordered right they want you to order from Z to A or from A to Z or you can do a custom ordering. But typically so you can do your custom sorting but there's not usually like a random a random sorting function over here. So sometimes you want to you want to take your numbers and then randomize them out when you're when you're practicing a data set could be a useful tool. So that's how you can you could use that random number generator to produce that.