Create random numbers - Google Sheets Tutorial

- [Instructor] When you analyze data for internal customers, you can use your company's real data without giving away secrets. If you're giving a presentation to individuals outside your company, though, you shouldn't use your real data. Instead, you should create data sets that let you tell the story you want to tell without giving anything away. In this movie, I will show you how to use the Rand and Randbetween functions to create random data for your worksheets. Now, you've probably looked at the values in column A and realized that I have a list of doctors and their last names aren't numbers, and there's no obvious way to use random data with them. Well in fact, there is. We're not going to use a built-in formula function. Instead, we're going to use a built-in capability of Google Sheets, which is really cool. The idea behind this list of doctors is that they work in a clinic, and we need to know the order in which they will be on call for the next nine days. Each doctor will only need to serve once, but we want to randomize the mix so no one can complain. To randomize the order of values in a range, select the range, so I'll select the cells A2 through A10, and then go to the data menu, and you'll see that we have randomize range. I'll click that, and we get a new random order. That's a very cool function. Now, let's move on to what might be considered more traditional uses of random values. In this case, we're going to do random values of dollars and cents. Dollars are whole number values, so I will use the Randbetween function. So I'll click cell C2, type an equals sign and Randbetween. Now I give the low and high numbers. Let's say that I want to have a range of $250, then a comma, to 950, which will be my high. And I'll type a right parentheses to close, and enter, and I get my value of 363. I can worry about formatting later. So, I will click cell C2 and drag the fill handle down. So I have all of my values. Right, now I can do something similar for cents, but rather than using whole number values, I will use the Rand function to generate values between zero and one. These are decimal values and they start at zero, and they end at .99999 to however many digits Sheets will allow you to use. So, to calculate the number of cents, I'll type equal, Rand, followed by an open and close parentheses with nothing in between, and enter. Right, so there I have my cents, and you can see that it is a fairly large number. So, I will copy that formula down so I have other values. And you can see that when I did that, that the worksheet recalculated, so I'm continuing to get random values. So now with the values for dollars and cents calculated, I will select cells C2 through D10 and then press Ctrl+C to copy, go to the edit menu, point to paste special, and click paste values only. I could also have used the keyboard shortcut Ctrl+Shift+V. I'll click paste values only, and I get the results of the formula instead of the formula itself. So now, when the worksheet recalculates, these values will not change. Now I can get my total, which I will put in cell E2 for row number 2, so I'll type an equals sign. And I want to add my dollars and cents so I will add C2 plus, then, the value in cell D2 rounded to two decimal places. So I will type round, R-O-U-N-D, left parentheses, the value is in cell D2, then a comma, and the number of places, so those are the decimal places, so I'll type two, right parentheses, enter. And I get my value of $799.42. Now, I'll click cell E2, and rather than dragging down I will double-click the fill handle at the bottom right corner. So, when my mouse pointer changes to a black crosshair, I'll double-click, and I get my values, and I see that I have a couple of single-digit decimals, so I will select cells E2 through E10, and on the toolbar, I will click the format as currency button. And there I get my values formatted as actual dollar and cent amounts. So that's how you can work with monetary values and generate values that you can use for sample data. Now, let's talk about generating random dates, which is a little bit different. In Google Sheets, dates are represented using numbers. So what that means is if you want to generate random dates, then you need to generate random numbers, and then convert them to dates. But of course that means that you need to know the number corresponding to the dates at the start and end of the range in which you want to generate random dates. So here's how you find that out. In cells G2 and H2, I have dates January 1, 2018, and December 31, 2018. If I select those two cells and then go to the toolbar and change the format to number. So I'll click more formats and click number. And I get 43,101 and 43,465, which corresponds to the 365 days in the year of 2018. You will notice that the start date and end date numbers both have .00 after them. That's because I changed them to the number format, which automatically comes with two decimal places. In dates, anything after the decimal refers to a time. So, for example, a value of .5 would correspond to noon. In this case, I just did the date and didn't have a time element, so those values are .00. So now I can generate a series of random dates using those numbers. I'll click in cell J2 and type equal, Randbetween, because I'm dealing with whole numbers. And the low will be 43101, comma, and the next, or end date, the high, will be 43465. Now I have my low and my high for my starting date and my ending date, and I'll type a right parentheses and enter, and I get 43293. Now, I'm going to copy that down to a number of cells. I'll copy it down to row ten. I'm not next to anything, so I do need to actually drag. So I will position the mouse pointer over the fill handle and drag down. There we go. Now I want to replace the Randbetween function with the actual values of the results so they don't change, so I'll press Ctrl+C to copy, and then Ctrl+Shift+V to paste values, great. Now, rather than the function, I actually have the values. Now to change these to dates, I will go to the toolbar, click the more formats button, and click date. And there you see, I have a series of nine random dates within the year of 2018.