 Statistics and Excel. Binomial distribution, coin flip, random number generation. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth soothing. Excel. Here we are in Excel. If you don't have access to this workbook, that's okay because we'll basically build this from a blank worksheet. But if you do have access, there's three tabs down below. For example, practice blank. Example, in essence, answer key. Practice tab, having pre-formatted cells so you can get to the heart of the practice problem. The blank tab, blank worksheet so we can practice formatting cells within Excel as we work through the practice problem. Let's look at the example tab to get an idea of where we will be going. We're considering a binomial distribution situation scenario of a coin flip scenario where we have a fair coin, 50% chance it lands on heads or tails. We need to define success. We're going to say that heads is a success, tails is a fail. We will be plotting this out using our binom.dist function. We'll also look at binom.dist.range so you can compare and contrast the use of the two. We will plot it and then we'll use a random number generator, a little bit more complex one than we seen in prior sections found in the data and the analysis tools here. If you don't have that, I'll show you how to open that up as well. Let's go to the blank tab to get started. We're going to then format the entire worksheet to start out. I'm going to do that by selecting the triangle up top, right-click and format the cells. I'm going to go to the currency and negative numbers, red and bracketed, no dollar sign and no decimals as our starting point. We have a coin flip scenario, coin flip, so I'll just call it up top. I'm going to make the whole thing bold as well, selecting the triangle, home tab, font group and embolding the entire thing. What we need for a binomial type distribution is P for the probability of success for each activity. We're going to say this is going to be P or the probability of success. For a coin flipped, if it's a fair coin, we're going to say that's going to be 0.5 typically. I'm going to say 50%, 0.5, home tab, number group, let's percentify that cell. Then the number of rounds, I'm going to say number of rounds, and I'm just going to say there will be 12 rounds. Then I'm going to make column C a little bit skinnier and plot this out. Let's plot it out. We'll plot this out in a similar way as we did with the prior presentation. I'm going to say X and it's going to be P of X. I'll make this black and white up top, selecting these two up top, home tab, font group, bucket drop down, making it black, and then making it white and centering that. Now I'm going to say this is going to be numbered from 0 to 12, but I'm going to use our nice sequence function to do that. Instead of going 0, 1, 2, and selecting those and copying down to 12, which we could do, but I would like it to be adjustable, so I'm going to use the sequence thing here. I'm going to try to show the pros and cons of using the spill arrays to some degrees, as well as we think about this, we'll do this two different ways. So I'm going to say this equals the sequence s e q sequence tab, and then I'm going to say that we want the rows are going to be 12 of them plus 1 because I need 12 plus 0. And I'm going to start at 0 instead of 1, number of columns, none. So I'm going to put 2 commas to skip that argument. The starting point will be 0, closing it up and enter, and you give us that nice spill format there. Now let's do the second bit here, which is the binome, and once again I'm going to use an array kind of format, so this is going to be binome.dist. So here we have the two, that we have .dist and .range, .dist.range. The .dist.range is the newer of the two, and so it's got more flexibility, but again there's kind of pros and cons to using each of them, and you might pick one or the other depending on your circumstance, but this one you should be able to use basically for all circumstances, and therefore might be the default that you would want to be thinking of. So we're going to say then the number of trials is going to be 12, and then comma, the probability is going to be the 50 comma, and then the numbers I'm going to select this range here. So I'm going to put my cursor on here D2, control shift down, so it picks up that range. I'm also going to select these two and make sure that they're absolute F4, dollar sign before the B and the 4, this one F4, dollar sign between the B and the 4, or before the B and the 3, and then enter. So then it spills down, so now we've got these arrays here. If I select this item, home tab, number group, percentify it, and add some decimals, there we have it. Now I'm going to do the same thing here and not use the arrays. One of the downfalls or pitfalls of using an array, by the way, is it's a little bit more difficult to say insert a table. If I want to go insert and put a table, then it's not picking, see how it didn't pick the entire area that it normally would if there were not arrays here. And if I close this up and I try to say I want this whole thing in a table, insert table, and okay, it messes up the spills. So you've got to be a little bit careful when you're working with the tables. The other thing is that it doesn't have a formula down here. It only has a formula in that top cell, which could be a pro or a con. But let's do the same thing. I'm going to copy this down here and do it without an array. So let's copy that. Let's give us some space and I'll put it down here to see the two methods you might use. I'm going to have the same headers. This equals the X and then copying that to the right for the f of X home tab font group black, white, centering it. So now I could use the standard, the good old method of just saying 012 selecting those three, copying it down with the fill handle. And then I'll use the other binom function. So equals binom.dist, but not the range this time. So binom.dist double clicking it. So now we've got the numbers, which I'm going to pick up this one. And note the order of the arguments are a little bit different. So I'm going to say comma. And then the trials that we're going to have will be 12. And then I'm going to say f4 on the keyboard to make that absolute because I'm going to copy it down and then comma. The probability is going to be 50% f4 on the keyboard because I'm going to be copying it down and then comma. And you'll note it's got this cumulative argument. Now the cumulative argument is the same thing or something we saw with the Poisson distribution. We're saying that if you pick false, then you're not going to have a cumulative up to a certain point. Whereas if you pick true, it'll try to do the cumulative up to the point, up to that certain point. We want it not to be cumulative so we can type in false or we can put a zero here, which will also say false. That's telling it false and enter. I'm going to put my cursor on it and then double click the fill handle. That should copy it down and then we'll percentify it and add a couple of decimals. Now with this second one, note that what we can do here is insert a table because we didn't use the spill functions in any spill function. So I can go to the insert and if my cursor is in here, I can make a table from it and insert the table and there we have it. And the tables can be nice sometimes.