 Statistics and Excel coin flip statistics example in Excel part number two. Get ready taking a deep breath holding it in for 10 seconds and 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 basically built this from a blank worksheet in a prior presentation so you could start from there with a blank sheet if you so choose and we will be continuing on with the practice problem in a format where you might be able to simply open a blank worksheet starting from here going forward as well. If you do have access to this workbook three tabs down below example practice blank example in essence answer key practice tab having pre-formatted cells so you can go right to the heart of the practice problem the blank tab is where we started with a blank worksheet so we can practice formatting as we work through the practice problem. Let's do a recap of what we've done in prior presentations and then we'll take those concepts and continue on with this presentation so we are imagining a scenario where we're trying to test whether or not a coin is fair in other words if we were to flip the coin the null assumption is that it would come out or have a 50-50 chance of landing heads or tails if we look at this from a sampling kind of perspective we can imagine the entire population then being a theoretical number as though we flipped it infinite amount of times and of course to test it we're going to take a sample flipping it some finite amount of times and see whether or not we have a preponderance of evidence to overturn the null hypothesis which is that we expect it to be a fair coin now in order to do this in excel we practice using some tools to simulate a 50-50 chance and we used the between so we took a random between one and two so that excel will give us a nice random sample we showed how we can then we can then show the results in terms of heads or tails or ones and twos if we so choose and then how we can basically get get these results and see how close they are to what we would expect if it was a fair coin which would be the 50-50 noting it's not going to be exact because we're just simply taking an example of an infinite number of flips and then we saw how we can put together a table and come up with a random function in the tables and use that table to copy and paste uh so that we can run multiple tests so in this case we ran multiple tests with two and then three and uh i'm sorry with two and then three and then four of them and then we looked at our results the percent that's head the percent that's tails on a result to note that obviously if we take larger samples we're usually going to tend more uh closely to what we would expect the population results to be which is going to be the 50-50 although of course uh more uh numbers in the sample does not necessarily we're going to come up to a closer result than some other than a sample that has less results because there's going to be an element of chance that's going to be involved so now we want to take the same concept and just expand the number of flips that we have so let's say let's say that we're going to test this out a hundred times with just let's say 75 flips just to get a nice number and then we'll try to approximate what if the coin is not fair how can we use our between function to simulate something that isn't fair so that we can test a coin it's not fair right so let's do let's do our same normal kind of test with a fair coin so we'll build out uh we'll build out a hundred tests so i'm going to make a skinny ar column i'm over here in ar if you're working in a new sheet you can just build a new sheet and start in column one if you so choose uh or column a and then i'm going to put the number here and i'm going to say we're going to go from one two and let's just do it to 75 i'm just going to not do a hundred so that we have to still calculate the percent of the result so i'm going to select those two and take it down to let's say 75 so we'll flip it 70 times 75 times for each test 75 times i'm going to center that and then i'm going to say this is going to be test one and then test two and then i'm going to copy that out a hundred times so i'm going to copy this out a hundred times i'm putting my cursor on the fill handle dragging to the right notice the testing excel's being nice and gives us that little uh little hint or to show us what how far out we are so there we're at a hundred so there's a hundred tests now i'm going to select all of these headers and make them my header formatting so i'll select this whole thing and i'm going to make it home tab alignment center and headers i usually make black background and white so black white and the headers okay so now if i go all the way back on over so now i'm going to now just implement my random knit my random function equals rand between between one and two one representing heads comma two representing tails or you can do it vice versa whichever way you want to see it but one or two enter and so that so that comes out to one and some which is the heads right and i'm going to take that i'm going to copy it all the way down to 75 and then i'm also it might be easier to copy it and paste it instead of using the fill handle to drag it to the right so that i can see the headers so i'm going to copy this whole thing right click and copy and when i paste it it'll paste the cells so i'm going to then select myself from a u all the way to the 100 tests and then i'll put my cursor here and right click on the selected area and paste them not one two three normal pasting and there there we have it now we have a hundred tests that have been populated now i might want to put a table into this the table might make it easier to kind of randomly shuffle these if i click on anything notice they kind of randomly shuffle so if i but if i insert a table insert tab table insert and okay so now we've got our our table and if you sort the table up top and each row that kind of gives it a good shuffle and then and then it it reshuffles all the all the time right so now what we want to do i want to keep my shuffling here i want to keep this thing up so i can go back in and shuffle the 100 uh tests see every time i click something the whole table seems to shuffle so i'm going to hope that that is the case so i get a nice random shuffle every time and then i'm going to copy the whole table i'm going to take the whole table or maybe i could just copy the headers let's do it this way i'm going to copy from as as all the whole all the way down to excel the whole column out to uh test 100 at e o and ctrl c or right click and copy and then i'm going to paste it it to the right but i'm going to paste it one two three so i'm going to paste it in let's paste it over here in er and right click and i'm going to paste it but make sure i paste it one two three just the values only one two three values only so now it's not going to shuffle and i've got a hundred random tests hopefully they're all random right so then i could go down and i can i can do my averages on the 100 tests let's go ahead and format it though let's select the ones up top and do my formatting making this my header i'm going to go to the home tab uh black white and then center and then i'll select all of the data and we'll take this all the way to the right and all the way down through the data and i'll make it that blue that i like to use home tab font group and i'm going to hit the bucket drop down if you don't have that