 Statistics and Excel. Coin flip statistics example in Excel. 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. So you can just open a blank worksheet, but if you do have access to this workbook, three tabs down below. Example, practice a blank. Example, in essence, answer key. Practice tab having pre-formatted cells so you can focus in on the heart of the practice problem. Blank tab being a blank so we can practice formatting the cells in Excel as we work through the practice problem. Let's go to the first tab to get an idea of where we will be going. The scenario being, we're imagining we have a coin. We want to test whether or not the coin is fair. In other words, if it was a fair coin, we would expect it to have an even chance to be landing on heads or tails. If we were to flip the coin, if it's unfair, then we're going to think that the chances are more likely that it lands on either heads or tails. How do we test whether or not the coin is fair? Well, typically, we'll run some samples of it. We'll run some trials and we'll take a look at the results of the trials. Now, as we do this, we want to get an idea of the statistics involved and the testing involved and also the tools that we can use in Excel to formulate these tests so that we can practice with those tools in Excel as well. So let's go to the first tab over here and I'm going to format the entire worksheet and I'm going to do that by selecting the triangle up top or you can say control A, right click and format the worksheet as we do every time in Excel. Basically, we're going to go to the currency tab and bracketed negative numbers bracketed and read no dollar sign. I don't think I need the decimals because of the type of problem that we are working in. So I will remove the decimals. Then I'm going to zoom in a bit holding control and scrolling in. So I'm currently at 220. I'm at 235 now. And so now what we want to do instead of actually flipping the coin multiple times is say how can I simulate that process in Excel. Now, if we had an even flip of 5050, then we have that we want to a random outcome between two variables. So I can use my random random function in order to do that. So let's first think about that. Let's let's think about how we can get a random function between one or two. Now remember when you're flipping a coin that in theory you can say well if the coin was fair and I was to flip it for the answer. If you flip it for the entire population being infinity, infinity times, then you would think I'd get an even break between 5050 heads and tails. So when we actually do a sample, when we test it, what we're really you can think of that as a similar thing as taking a sample from the entire population. In this case, the entire population being infinity flips a theoretical concept and we're testing it with a finite number of flips and then seeing how close we get to what we would think would be the appropriate response given the whole population would be 5050 and infinite flips. Like that's one way you can kind of think of it with our sampling concepts. So we need something that's going to come out if it was a fair coin between even random between one and two. So if we say I'm going to have my random results, random results between one and two, I can say this equals a random function between and then I'm just going to say the bottom number is one and the top number is two. So one comma two, there's our random function. This will give us random numbers between one and two. Now it doesn't give us a decimal. So if I was to say maybe you might think that it's going to give us like point something right now it's going to give us the whole number of one or two. So that basically stimulates an even coin if I was to flip an even coin. So if I then copy that down, we can then get and by the way I'm working here in row six. So I'm going to actually delete some of the rows above it because I thought I was in row one to start with. I'm going to put my cursor on the one drag down to row five, right click on the selected area and delete that stuff. And then I'll also make this bold with a full sheet font group and make it bold. So then if I'm on number one, I'm starting at number two. If I was to copy this down like 100 times, let's say we copied it down, you know, 100 times, then we would have a sample in essence of 100 flips, right. And so now we've got random amounts of one and two. Notice that every time I click on them, they randomly shuffle again. Let's go ahead and put some a table in here. So I'm going to say format or I'm sorry insert table. There's our table. And so if we want to make this a little bit more formal, let's assign what one and two means now. So I could then say let's make be a little bit smaller. I don't want to put anything right next to the table. I need that space we've got to keep the table separate the table needs to be separate than the other stuff. And so I'm going to say the heads are or a one represents heads right so one represents heads and tails is going to be the two the two is tails. So let's go ahead. So we've assigned our terms and go to the home tab font group. I'm going to make this blue and bordered. I go to the more colors down here typically if I don't have the blue yet, and pick up that blue is the one I want. And then font group and put some borders around it. All right, so then what I'd like to do instead of having this shuffling around all the time, I can then copy my results. I'm going to copy this whole tab and then paste it special without the formulas pasted with with just the just the numbers one two three. So I'm going to copy put my cursor on a column right click the cells copy them. And I'm going to put them over here somewhere and like f I'm going to paste them one two three because I don't want the formulas there anymore. I just want the results of that shuffled those shuffled numbers. So I'm going to put one two three. So there we have it. Let's make another up hold on a second. It didn't paste one two three. I'm going to paste it one two three. Okay. And then I'm going to make a skinny E column skinny E. And then if I was to look at my results here, I would I could then do a fancy little formula if I wanted to to basically say whether it's heads or tails. So here's the outcome. I know that one is heads and two is tails, but I could try to say how can I convert my random ones and twos to heads and tails. We'll practice logic functions in Excel basically asking Excel to give us a result of heads. If this number happens to be a one, if this number is not a one, then we'll say give us tails. That's really all we need to do because there's only two variables heads or tails. So we're going to say if it's a heads, give us heads. If it's not heads, give us tails. So I'm going to put my cursor over here. We're going to say equals it.