 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 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 but that's okay whatever because our merchandise is is better than their stupid stuff anyways like this cpa thinking cap for example cpa thinking cap you see what we did with like 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 10 fold yeah at least yeah 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 accounting instruction dot com or accounting instruction dot think of it dot com 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 sales 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 random a random function in the tables and use that table to copy and paste 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 had 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 that's not fair right so let's do let's do our 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 a r column i'm over here in a r if you're working in a new sheet you could just build a new sheet and start in column one if you so choose uh or call them a and then i'm going to put the number here and i'm going to say we're going to go from one to and let's just do it to 75 i'm just going to not do 100 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 so 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 uh 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 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 blue more colors standard there's the blue boom and then font group and center it we could also make these thinner so if i select the whole thing again make them a little bit thinner because i only have one number in there and then i could wrap the text so the headers still fit just to practice that so i'll select the entire header field and go to the home tab alignment and wrap the text so now you've got the header now wrapping the text uh notice that that makes this whole row wider so if i had anything to the right it could be it could kind of be kind of an issue because it makes everything on this whole row wide but there's pro that's the pros and cons to wrapping the text so then if i say the total heads on each flip we'll we'll do our account if so if it's a number one it represents heads number two represents tails let's see what our results are how many heads did we get out of i only did 74 flips instead of 75 okay out of 74 flips so equals count if brackets i'm going to put my cursor on this cell i'm going to do it with my key strokes this time holding control shift up but then i want to make sure i don't pick up the header so i'm going to say hold shift down and that picks up the whole range i can still see the formula up here and so i'm just going to hit enter and i'm sorry i didn't finish the formula i have to say comma and the next criteria is uh if it's a one number one and then i can close it to see it down here it's count if the whole range comma criteria one and then tails is going to be the same equals count if brackets shift nine i'm going to do it just with the key board now up up holding shift and control up holding just shift down so i don't include the header looking at the formula bar adding the comma to put the condition the second criteria number two and enter if i double click on it down here you could see the whole function and then if i put my total it should come out to 74 using the trustee sum function equals the sum of these two so i'll put an underline here home tab font group underline and then i can say the percent heads percent heads is going to be equal to the total heads 34 heads divided by the number of flips 75 and then that shows up as just a number because i don't have it as a percent formatting home tab number group percentifying it i like to call it the percentify some people that annoy some people but i think it's fun and i'm going to un i'll just keep it at that percent so we'll round it and then percent tails and we're going to say this equals 41 over 75 so that should be percentified 55 so the percent total should be 100 percent right so we're going to say percent total is going to be equal to the sum of the 45 and the 55 which is one and if i go to the home tab number group percentify it comes out to 100 which i can't see so i'll widen the cell out a bit and so there it is so then i'm going to put an underline here home tab uh font group underline and there we have it now i can copy this all the way across and i can see for this first test 34 heads 41 tails that's a breakout of 45 55 we would expect it to be 50 50 but of course we only flipped it 74 times out of the total population which is infinite times so so it's not going to be exact right let's let's say we did that though 100 times so i'm going to copy this all the way across selecting them copy with the fill handle all the way across now notice i can't see whenever you see these hash taggy things that means usually the cell is not large enough so i'm going to select all of the headers all the way across to try to get them to be the same size selecting all of them to here and i need them to be about this wide so i'm going to put my cursor between es and et and make them wide enough to see that 100 percent so there we have it so if i look at all so if we look at all of these we can say if let's just look at the heads side which we would expect to be 50 percent right so we got 45 53 56 56 53 uh 60 49 right so some are higher some are lower but they're kind of hovering around the 50 so let's select this whole thing i'm going to put this in my my uh calculation formatting which i'll make another color to make it stand out as not part of the center of the table home tab uh font group and i usually make that dark blue or i have been at least lately and then white on the text so we can see the text font and put some brackets around now it now i might want to then say okay let's see if i can take these results let's just take a look at the heads and see if i can analyze that a little bit more and maybe i'd like to do that with a vertical analysis so i'd like to take this this is in a horizontal row and transpose it to see it vertically so what i can do there is i can copy this whole thing and it's kind of a two-step process i'm going to copy this whole thing and say i would like to make that into a column control c or copy i'm going to go up here up top and i'm going to first paste it just the numbers only i don't want the formulas just the numbers i'm going to right click paste it one two three and then i'm going to copy that whole thing again control c and paste it here but transpose right click and paste special and transpose meaning i want you to reverse the x and the y's so now it's in a column format then i'm going to delete all of this stuff from ir all the way out i'm deleting the whole column so you can just delete the numbers you don't really need to but right click and delete and so there we have our data so i could enter a table here if i want uh into this data uh so i could say let's go to the insert tab and make a table and say okay so there's our table and then i could say this is the expected value notice when i put something on the second line of the table and hit enter before i do that let's i'm going to undo that i'm going to select this whole data make it a percent uh home tab numbers percent to find it and then and you could add some more decimals maybe you want it more exact over here you know but let's keep it at let's keep the decimals off and then i'm going to put the expected value here expected so and notice when i hit enter it adds another table now the expected amount is just point five or fifty percent it's already in a percent format and then i can say this is the difference i'm going to make another column difference and when i hit enter it makes the whole column this is going to be equal the 45 minus the 50 there's the difference i'm going to copy this down to just double clicking on uh that uh fill handle and so now you can see the differences uh some above and some below on the table so that looks good i'm going to i have an extra column so i'm going to delete this column over here right click and delete let's do that now if i want to add a total line to a table i could do that by going to the table tab so i'm on the table in the table tab i could add a total column which is in the table style options group and then down here uh it puts a total so so so so so now i've got uh the total column maybe on this one i want to take the average so notice i hit the drop down and say take the average which is taking the uh average of these columns which comes out to 50 percent pretty close right or if i wanted to see it with decimals it's not exactly 50 but it's pretty close and so this is going to be 50 and the difference if i sum up the difference uh we we've got a difference of of 33 percent it's kind of a interesting but in any case let's go back up top i'm going to make iq skinny and then we could make a histogram of the results we got right so these are all the results we got we could say let's make that into a histogram let's insert a uh i'm going to select this data insert and then charts and graphs a histogram we've seen in the past so now you've got a histogram that looks you know kind of kind of like what we would expect right the center point is around the 50 percent and the from the results we have we see the spread that's starting to build up like kind of like what you know what you might expect uh when you when you basically run the tests now if i was to do that like i could do this whole thing again and then come up with a different histogram and see see you know what the difference would be if i ran you know the test multiple times right so i could for example go back to my my uh data and see if i could double click on it so i could so it will reshuffle so now i've reshuffled my data and let's just build it one more time right so if i take this whole thing and i copy let's do it from as i'm hopefully i'm hopefully the whole shape table shuffled when i double click on it and then i'm going to go from as all the way to to uh to e o right click copy and then let's just imagine that we build this out uh again so i'm going to go all the way to the right and just do it again so we'll go all the way over here and i'm all the way over in b j right click paste it one two three so now we have uh just the numbers and then i could format them i'm going to select the whole header i'll do this faster because we've seen it before selecting the headers i'm going to make the header black and white i'm going to center it and wrap the text while i'm here and then i'm going to select all of the data and we'll make that all of the data blue and bordered so font group border and the blue and then i'm going to say this is going to be heads and by the way i could just copy my results over here so the it's going to be the same formulas as my dark blue so why don't i just copy this whole dark blue thing and it'll calculate the same thing on the bottom of my second one so i'll just copy this whole thing say copy that and bring it on over to the results and i'm in uh bj 77 and paste it so everything looks like it's going properly so now we've got another set of results so now let's do the same thing copying the heads i can go to the heads here and say we'll just select all of them i'm going to transpose them going up top i'll start in in b right click pasting just the values and then right click and copying pasting in in a right click special so that i can transpose making a column from them deleting all of this stuff which are no longer necessary so we'll delete that we can insert our table i could say insert uh table and boom and then i can make the whole thing home tab number percents and then i'm going to delete this tab right click and delete make this smaller and then let's insert a histogram insert histogram so here's my second histogram running a whole another set of 100 tests at 75 and you could see it's close but it's not exactly the same as the histogram we had over here so if i copy this histogram and i put it down here again let's just i just want to test that it looks the same copy this histogram and put it to the right here so you've got two histograms that are quite similar but you could see they're not exactly the same uh when we run our two tests so this one they have different buckets that they've built on the buckets as well but you have a similar kind of shape to them but not exactly the same all right and then next we just want to say well what if we had an unfair coin like the coin was was not fair well well how can we kind of represent that well we could say let's just let's just test that out and say we were going to have the tests the test over here i've got caps locks on tests and let's say that we do the same thing one two and bring it up to 75 or 74 uh 74 tests right there i'll center that and then on on the actual tests test one we'll do the same random equals random between and then i'm going to say between one and three this time so we'll imagine this time that uh one is a heads and if it's not a one which means it's going to be a two or a three it's tails right so one's head so now it's an uneven coin it's going to land more on tails right so that's one way that we can kind of simulate uh using our using our random function an uneven coin right you can imagine different ways that we might try to get the coin weighted unevenly so it's a three so if it's a two or a three it's tails right and then i can copy this across test we'll do this a little bit faster because we've seen it before test two i'm going to copy these two i'll bring that out to what do we have 100 tests to 100 over here 100 tests and i'll make this into a table so i don't need to format the headers maybe and then i'll go all the way back on over and i'm just going to copy this down copy this down and bring it down to 74 and then i'll copy that whole thing and paste it across to the 100 tests that we will make that would have an uneven coin so now we're going to paste all the way across so there's our table generation let's insert a table insert table and so now we have our our random generator for the unfair coin so now let's select the whole thing and and make a static test copy in the whole thing and put our cursor over here and we'll put it right here and right click and i'm going to paste one two three just the values i'm going to select the headers and format the headers so i'm going to make them home tab font group black white centered and wrapping the text i'll select all of the data now and let's make that our blue and bordered so we'll select the entire data set and we'll make that home tab font bordered and blue and then now when i do my count if its heads it's going to say i want you to say equals count if brackets and then i'm going to take the whole thing i'm putting my cursor up one control shift up and then shift down now i'm going to look up here to my formula bar comma if it's a one count it and so that comes out to 21 and then one way i can do the second bit if it's tails i'm just going to say i know there's 75 of them so i can or 74 so i could just say this equals you know the count of 74 minus 21 that would be the easiest thing to do we could also come up with a with a formula to say count if it's not equal to you know one right and that would give it but but the easiest thing to do would be that and i'm going to say then the totals and sum them up which of course should come out to 74 and then we can take our percent our percent heads versus the percent tails and then the percent total so the percent head this time was equal to 21 divided by 74 making that a percent home tab numbers percentifying tails is 51 over 74 and we'll say home tap numbers percentify font group underline the total percent then of course summing 28 72 is number group percent 100 percent so there we have it if i copy this across it's copied all the way across for our 100 tests so now we've got that copied i will make this something funny happen here hold on us oh i can't i have an issue and that's because i used this 74 and that's not going to move that needs to be absolute so that's r i seven one that's our r i 75 so i'm going to put my cursor in here f4 dollar sign before the letter dollar sign before the number and then copy that across and it should be good to go and then i'll select the whole bottom bit and make it that dark blue and white for our totals down here and so we'll make that home tab font group dark blue and white so now you can see of course if i looked at the percent heads then you're going to say hey look that doesn't look right something looks looks leaning towards it looks like it's leaning towards the tails right so if i let's copy all of the heads and transpose our totals so i'm just going to say if i just look at my heads i would expect it to be around 50 50 if i go up top and transpose it first i'm going to right click just the values then i'm going to copy that and put my cursor in vh right click paste it one paste it special transpose enter out delete all of this stuff because we don't need it anymore and then i'm going to insert a table insert table okay make it a percent home tab number percentifying it so there so there we have our numbers and then if i wanted to say this is what was expected expected and this is the difference difference expected would be 0.5 or 50 percent i'm going to double click copying that down the difference is 28 minus 50 and so notice the differences are all you know going one way so now we have some evidence where we're saying hey look something doesn't look right according to these tests we have a preponderance of evidence to then reject the null assumption that it's a fair coin which if flipped an infinite amount of times would come out to 50 50 right in the total population i'm going to delete this now if i make a histogram of this data i'm going kind of quick because we're running long on time i can go to the insert chart's histogram so here's a histogram of the unfair coin leaning towards tails and you can see the center of the graph it still looks like it's centered but you can see where the the labels are over here instead of around 50 percent so the center point is spreading out over here which is is not where we would expect the center to be whereas if i copy the the even coin and i copy the histogram we came from that and we copy that over here then let's paste that we can see that the center point is what we would is closer you know to what we would expect so so that's our uh uh this one's still kind of shifting over to the right here a bit that's kind of interesting but you get the point here so you get so so so the idea is is that we would test it the total population is uh infinite number of flips here when we compare it to like sampling that we might do in an election so an infinite number of flips which would be 50 50 we took a sample which is a finite number of flips and here we've got a preponderance of evidence that certainly leads us to believe that something looks off here we're still a little bit shifted over to to to the right but we're nowhere we're nowhere near as skewed as obviously this one up top and again obviously because it's a random sample we can then get into questions of how close are we to the actual number given the samples which we'll talk more specifically about in future presentations