I'm going to talk about a couple miscellaneous items in this screencast related to generating random numbers, how we can calculate combinations, and the number of permutations. Sometimes in Excel you want to create or have Excel generate a random number. I'll go through an example of why we might want to do this here, in a moment. But there is a RAND function, so you can just do RAND. And what that does is it creates a random number between 0 and 1. We can drag this formula down, so maybe you wanted to create a column of them. You notice that when I drag it down, the top one changed. Because every time you recalculate a formula in Excel, it does so for the entire worksheet. We could create an array of random numbers. The RAND function generates a number between 0 and 1. Maybe you want a number between 1 and 10. There's a RANDBETWEEN function. So, maybe between 1 and 10, inclusive. It's going to just choose with equal weighting a number between 1 and 10. And again, you can drag this over, and you can drag this down. You might be wondering why this is useful. Well, let's say you had a presentation order of students in class or some some order that you want to randomly assign. You can go in here and you can type in RAND to put in a random number. I can drag that down. I want to make these numbers permanent. So, I'm going to right-click, copy. And I'm just going to right-click, paste special, so I have the values. So, now when I recalculate or move things around on the spreadsheet, they're not changing because these are no longer formulas. Now, what I'm going to do is I'm just going to select both of those columns, and I'm going to sort by the second column. And I'm going to sort the second column lowest to highest, or you could do highest to lowest. Bottom line is, you are randomizing the student list over here. We can go to the Home tab. I can go up here to Sort & Filter. I'm going to do Custom Sort, because I want to sort by that second column. I'm going to sort by Column D. And it doesn't matter if you do smallest to largest, and I can click OK. And now I have randomize the order. Look, I get to go first. So, that's how you can randomize a list of names using random numbers. Sometimes you'll find yourself in a situation where you need to determine a random number between two values. Maybe we have a low number and a high number, example here is 7 and 20. You don't want to be necessarily integers, like I showed you with the RANDBETWEEN function. But if you want to calculate decimal numbers based on random numbers between 7 and 20, the way to do this in Excel is to use the RAND function. So, you're going to create a random number, multiply it by, in parentheses, the High value minus the Low value, outside the parentheses plus Low. So, our example here, we would type in RAND * (20- 7) + 7. So, here it is in Excel. I can enter that formula. Again, It's RAND (20- 7) + 7. And I can drag that down. I can copy over. And these numbers are random numbers between 7 and 20. Just to re-emphasize, the RANDBETWEEN function I could do 7 and 20, that's only going to give me integers, which are counting numbers. So, that, it does not do the same thing as we did up here. Real quick I want to show you something exciting with Office 365. I don't believe this is in Excel 2019, but there is a brand-new formula RANDARRAY. So I could create a random array here. Maybe I wanted to make this 10 rows long, 2 columns wide, and here we have the option to put min and max. So maybe I want it to go between, let's do something different now, 40 and 80. And here we have the option to decide if we want this to be a decimal number or an integer. So, if I just do true here, that means I'm going to create a 10 by 2 array, 10 rows, 2 columns, between 40 and 80 inclusive that are integers. And if I press Enter, it automatically does that. This is actually very exciting and there's lots lots of uses for this. Now we're going to talk about combinations and permutations. Let's say we had four people or four students here, Alex, Barry, Cathy, and Denise. And we wanted to determine out of those four how many different pairs could we create or how many combinations of two. There is a function built into Excel called the COMBIN function. The first argument is the total number, it's going to be 4, because we have 4, and the number chosen. So, if I make combinations of 2, those are just pairs out of 4 students. Then we would have 6 possible combinations. There's also something known as permutations. With combinations order doesn't matter. So, Alex and Barry would be the same combination as Barry and Alex. Permutations are a little different in that order does matter. So, Alex and Barry, in that order, is different from Barry than Alex, and that's known as a permutation. To calculate permutations in Excel, we can use the PERMUTE formula. Same thing, we're going to have 4 that are available to choose from. If we're going to permute those into 2 spaces. So, we're making pairs. Then I can do a 2, and we have 12 permutations. If we write out all of these combinations Alex and Barry, Alex Cathy, Alex Denise, Barry Cathy, Barry Denise, Cathy Denise. Those are our six combinations. So, order doesn't matter, Alex and Barry would be the same as Barry and Alex. But permutations, if we take that single combination Alex and Barry, we can permute that into two. Because we have Alex and Berry, which is different from Barry and Alex. And we can do the same thing for the other combination. So, there are 12 permutations, whereas there are only 6 combinations. So, hopefully you learned a little bit more about random numbers, combinations, and permutations in this screencast.
