 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. First, a word from our sponsor. Yeah, actually, we're sponsoring ourselves on this one because apparently the merchandisers, they don't want to be seen with us. But that's okay, whatever, because our merchandise is better than their stupid stuff anyways. Like this CPA thinking cap, for example. CPA thinking, CAP, you see what we did with the letters? And this CPA thinking cap is not just for CPAs either. Anyone can and should have at least one possibly multiple CPA thinking caps. Why? Because based on our scientific survey of five people, all of whom directly profit from the sale of these CPA thinking caps, wearing this CPA thinking cap without a doubt, according to the survey, increases accounting productivity tenfold. Yeah, at least. Apparently the hat actually channels like accounting energy from the quantum field ether directly into your head, allowing you to navigate spreadsheets faster. It's kind of like how in like the matrix when Neo learns kung fu, or at least that's what the scientific survey saying. So get one, because the scientific survey participants could really use some extra cash. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. 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 sales so you can focus in on the heart of the practice problem. Blank tab being a blank so we can practice formatting the sales 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 where 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 red 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 50-50 then we want a random outcome between two variables. So I can use my random function in order to do that. So let's first think about that. Let's think about how we can get a random function between one and two. Now remember when you're flipping a coin, in theory you can say well if the coin was fair and I was to flip it for the entire population being infinity, infinity times, then you would think I'd get an even break between 50-50 heads and tails. So when we actually do a sample, when we test it, 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 50-50 and infinite flips. 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 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? No, 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, both 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 a hundred times, let's say we copied it down, you know, a hundred times, then we would have a sample in essence of a hundred 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, 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 B 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 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. That's 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. Paste it with just the numbers one, two, three. I copy, put my cursor on a column, right click the cells, copy them. And I'm going to put them over here somewhere in 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, 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 if there's our logic function. We can double click on the if or add the brackets, shift nine. Here's our logic box. So here's our arguments down here. We're on the logic test being the first argument where we're going to say that we want, if this number is equal to the number one in our box over here, then when I say then I put a comma. If that test is true, what do you want us to do? That's the next value if it's true. We want you to put a heads next to it if it's true. And then comma, if it's false, what if it's not true? What do you want us to do? We want you to put a tails. So once again, first test if this number equals one. Next part, we want you to then put a heads in it. Next part, if it's not equal to one, put a tails in it. And if I hit enter, it puts a tails. Now I'd like to copy that down. I'm going to double click on it. I can't copy it down as if because these cells will shift down. I want this cell to shift down. I don't want these cells to shift down. So anything that's outside of the current range I'm working in, I need to tell Excel don't shift it down. So for example, this one's in D1. So I'll put my cursor in D1. Now the way you do this is one way is you could just select F4 on the keyboard. It puts a dollar sign before the D and a dollar sign before the one. Those are the two variables locating our cell on the two-dimensional screen. We've got D1. So it's saying that dollar signs have nothing to do with dollars. It's just telling Excel, when I pull this down, don't change the D or the one. Keep the cell exactly the way it is. That's called an absolute reference. And you only need a mixed reference, by the way, but an absolute will work here. So I'm going to use that. And then over here, we've got the tails. So that's on C1. So same thing here. I'm going to put my cursor in there. F4, dollar sign before the C and the one. And C2. Also, we don't want it to move F4. So now these are all absolute cell references except for this one, F2, because I do want that one to move down. So let's hit Enter. I'll show you what I mean. I put my cursor on this. Put my cursor on the fill handle. Let's drag it down to the number one where it should show us the heads. If I double-click on it, there's the references. Didn't change that cell. Didn't change that cell. Didn't change that cell. Did change this cell because now we moved that down to four, whereas here it was at a number two. Then if I just put my cursor on this one and double-click it, it'll go all the way down. So there we've got it all the way down, and we could say these are our results for our first test. I could make it into a table if I want now. Go into the Insert tab, Tables, and Insert a Table, and say I want to add the table. And then we could look at our results, and we could look at them this way. You know, I could show the results from Z to A and put all my heads and all my tails, which might give me an idea. I can count where the middle point is. And then, of course, what we would want to do is get the percentage that's going to be heads versus the percentage that's going to be tails. So let's make a skinny H over here. And so this is basically our sample that we took now. We took a sample of the population, which, in theory, is infinite number of flips, which we know, in theory, if you did an infinite number of flips would come out 50-50, but we just did a sample of 100. So now we can count the heads and count the tails. So let's say we've got heads, tails, and we'll do a count function here. So I could do it this way. We were going to say equals count if. Count if brackets. We only need one condition. So I'm going to say count if. I'm going to pick up the range. Now I could do either range doing this with numbers or doing this with the non-numerical value. So let's just do the heads here. So I'm going to say count them. If that range, comma, has a criteria, what's the criteria that it's just simply going to be heads? So count them if they are heads. And then enter, so there's 52. Now I know if there's 52 out of 100, I could say 100 minus 52, or it would be better for me to give me a double check to do the same thing here. Equals count if brackets the range. So there's the range, comma, the criteria if it's a tails. And then closing that up and enter. And then I'll pick up the total. And the total equals the sum of these two. So there we have it. And I only have 99. So notice I don't exactly have 100. Apparently I didn't count it out properly because I stopped at 100. I missed that first cell. So there's only out of 99. Now actually I think that's actually good because notice if it was out of 100, we could see here that that would be representing 52% versus 49%. But the fact that we always use round numbers in these practice problems can kind of lead to confusion because what we're really doing is saying, I'm taking the 52 heads out of the total number of flips, which there was only 99 in this case. So that means to get a percent, let's put an underline here, font group and underline. I can get a percent by taking equals the heads divided by the total, which is not 100, but only 99. So it's not 52%. We've got a number one. I need to make that to a percent. Home tab numbers, I'm going to make it a percent. Add a couple of decimals just to make it a little bit more exact. So it's 52, 53, right? And this is going to be equal to 49 divided by 100. And then this one is simply going to be equal to this divided by itself or 100%. Or in other words, I could sum up these, which will be 100. And then let's format this one. I'm going to use the format painter this time to format these two the exact same formatting. Home tab, clipboard, format painter, and then paint brushy these two. And so it's the same formatting. I'll underline it here. Home tab, font and underline. So we can see that it didn't come out exactly, even though I did a pretty fair amount of flips, 100 or 99 flips, but it's still 52 heads and tails, 47. So if I was to judge this, I can't really say what's unfair on heads side based on this result, because statistically speaking, it's quite possible that I flip it 100 times and that's pretty close to 50, 50, right? So I'm still going to assume the null assumption that the coin is innocent. It's not fair. I mean, it is fair unless I get a preponderance of evidence that's going to prove to me otherwise. And so that's kind of the general idea. Let's go to the home tab, font group, put some brackets and make this one blue. Okay, I'm going to try to make all these skinnies the same skinny size. Put my cursor on B. I'm holding down control E, holding control H, and then I'm going to make them the same skinny size so they're kind of uniform. And then, so now let's copy this number H. I'm going to go to the home tab, clipboard and format paint the skinny and then skinny rise the L over here. So now let's just do another test just to play with our numbers here. And let's say that we did like 15 of the 1 to 15 flips. And let's say that we did a sample of just one, one flip up to 15 flips and see what the differences are, or two flips up to 15 flips. So let's say we say the number of flips is going to be one and then two and let's bring it up to 15. I'm going to put my cursor on this one and bring it up to 15. And so then I'll center this one and then let's just add our tests. So this is going to be test, test one and then I'll put test two and so on up top and then I'm just going to use my random, my random between one and two again. So random or equals random between, double click the between one comma two. So one's head and two is tails is what we're standing, what it stands for again. So I'm just going to hit enter and so I get, I'm going to put my cursor on that one and fill handle it down. So on the first test I'm going to say we flip it and test two times and then I'm going to put my cursor here and copy it to the right and then I'm going to copy this one down. So I'm going to imagine we flipped it three times this time and then I'm going to notice all of those came out to one on this one random test. So with three flips that's quite, you know, that's quite possible. If I copy it over again and then I pull these down I imagine we flip it four times and then I'm going to copy these to the right and then pull, oh hold on a sec and then pull these down we're going to flip it five times copy these to the right and flip it six times copy these to the right imagine that we flip it seven times copy these to the right I'm going to pull this over a bit imagine that we flip it eight times copy these to the right and then imagine that we flip it nine times and then copy these to the right and imagine that we flip it ten times I'll do it this way so I can copy it to the right easy ten times copy to the right and then we're going to imagine that we flip it eleven times copy to the right imagine that we flip it twelve times copy to the right imagine we flip it thirteen times copy to the right two more times and say that we're going to flip it fourteen times and then copy to the right one more time and then we have to stop these good times of copying to the right and say that we flip it fifteen times so there's our random generation tests going from one I'm going to put my cursor on these two and drag it to the right and it should be able to pick up the test numbers going from test one to fourteen I'm going to select all of these make that into a border by going to the home tab, font group and I'll make it black and white which is typically what I do with the borders and let's center it and then I'm going to go back to the left and I'm going to select all of my information here and put my blue and borders around it now this is just my test range so what I'd like to do is just copy this whole thing and then I'm just going to paste it one, two, three so that this thing will reshuffle anytime I want to kind of reshuffle it I can go in here and reshuffle everything and then it'll and then I can come and copy it again so let's go ahead and copy this thing and copy the whole thing and then go to the left and I'm going to paste it one, two, three just the values now I don't want the formulas but I also want the formatting so I could right click and paste the formatting so I pasted the values in the formatting but not the formulas so I get this mimicking of the table which now gives us our results and I can go down here and say well let's take a look at the heads so I'm going to use my count if function again so I'm going to say equals count if brackets and then I'm going to take the range here down to here and then comma and I'm going to say count if it's there's the range and the criteria if it's a one so count it if it's a one and then brackets enter there are zero this time that is a one notice with these first two tests we've got two tests they came out completely tails which could happen because we only flipped it a few times and then if I copy this to the right now I've got one and two so I'm going to copy this all the way to the right and so there we have it and then we're going to say tails so I could do the same thing count if brackets I'm picking up the entire range because I want to be able to copy it across I'm not just going to pick up those two because I want to copy it across count if comma it's a number two and then close it up and so there it is and here's the total then which is the sum of these two which means we only ran two tests this time so I can underline this and then I can say these are my percent heads versus the percent tails so the percent heads would be equal to zero over two zero and the percent tails would be two over two hold on a sec equals two over two and then the percent percent total which is going to be equal to the sum of these two percents so I'm going to make all of these percents home tab numbers percentifying them so there's our percents and I could put an underline here so there we have it in our first flip we had zero heads two tails and so total tails zero percent heads and a hundred percent tails so let's copy that across putting my cursor on the and copying across and you can see of course the idea would be that if we have more flips then you would think we would get closer to a better result so I'm going to go to the home tab font group and make this blue this time for my totals blue and white let's put some borders around it too for the fun of it put some borders around it so the idea here being that with two flips is probably not going to be enough with an infinite number of flips of the population and so we could come up with some skewed results of you know here's two that all three hit tails here which is kind of unusual and then here we've got the tails are just doing quite well we've got 25 percent tails 75 head because one out of four versus three out of four, five flips so now we have two tails three so that one looks at least a little bit more accurate notice this one we flipped all heads came up this time which is kind of unusual even though we flipped it six times so that's kind of weird and then you got four and three this one actually came out exactly 50-50 so the fact that if you get more flips you would get closer on average however it's not always going to be closer than like this one we flipped it less amount of times 14 versus 15 and the 14 came out to what we would think is actual population 15 is off so then if you use a random generating tool like this you can then go in here and basically reshuffle everything right I can go in here let's do a reshuffle reshuffle make sure that we get every column on the reshuffle it might be better to put a table in here to make sure everything is shuffling let's actually do that let's insert a table make it into a table and then when I reshuffle each column everything should shuffle up and then hopefully that all shuffled up and then we could take all of our numbers that were randomly generated hopefully copy them and paste them into our table over here but paste them formats only one two three and so now we've got a different set of numbers now we're on two that came up heads and then now we've got two and three so there's just a nice tool to be able to kind of simulate our results with the randomness I'm going to make this a little bit thinner and we'll stop it here and continue on with some more testing next time