 Statistics and Excel. Election poll statistics example. 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 that's okay whatever, because our merchandise is better than their stupid stuff anyways. Like our crunching numbers is my cardio product line. Now, I'm not saying that subscribing to this channel, crunching numbers with us, will make you thin, fit and healthy or anything. However, it does seem like it worked for her, just saying. So, yeah, subscribe, hit the bell thing and buy some merchandise so you can make the world a better place by sharing your accounting instruction exercise routine. 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 to this workbook, three tabs down below. Example, practice blank. Example, in essence, answer key. Practice tab having pre-formatted cells so you can get right to the heart of the practice problem. The blank tab, basically a blank sheet so we can practice formatting excels in Excel as we work through the practice problem. Let's go to the example tab to get an idea of what we will be doing. Now, in prior examples, we imagined that we had the whole population data set and then we took a sample of that information from the data set to see how closely the characteristics of the sample tie out to the full population. In this case, we're not starting off with the full population data set. However, we are starting with an assumption in the actual population. The assumption being is 60% for candidate A. That's the assumption that we are making. And then the question is, well, how can we create a sample in Excel using our Excel tools to kind of mirror the data that we might get in a polling type of situation so we can practice our statistical tools? So what we're going to do here is we're going to imagine that we had 10 samples, for example, and just use our random between 1 and 100 function in order to do that. And then we'll get our data from that information and we'll do some calculations on it. And then we'll just practice using our Excel and extend that same concept to taking a whole lot of samples and then we'll make a histogram from the results of those samples. All right, let's go to the blank tab and let's do it. So we're on the blank tab. I'm currently 175% on the zoom in. I'm going to format the cells first by selecting the entire sheet with the triangle up top, right clicking on the cells, formatting those cells. I like to go typically to currency bracketed numbers for negatives and red for negatives, no dollar sign. And we don't really need any decimals, so I'm going to remove the decimals in this case. And so there we have it, let's say OK. So now we've got the underlying formatting of the worksheet. I'm going to zoom in a little bit more holding down control, zooming or scrolling in on the mouse. OK, so let's make our random generated samples. Let's call this sample one. I'm also going to make the whole sheet bold and emboldened the whole sheet. So I'm going to select the triangle again, home tab up top, fonts group and emboldened the whole sheet. All right, so the first sample, if we're trying to simulate that 60% is going to be for candidate A. We could say, well, what if I make a random generation between 1 and 100 and everything that's below 60 or including 60, 0 including 60, will be then what we count for candidate A and everything above that will be a randomly generated item that is not for candidate A. So that's one way we can kind of structure this so we can practice our statistical kind of testing using Excel's tools, trying to mirror an actual real world situation, right? So we're going to say, all right, this equals random between. And I'm just going to say bottom part is going to be, hold on a sec, that's an array. That's not what I wanted, random between, that's the one I want. There it is, random between. The bottom number is going to be 1, the second argument, comma, and notice I'm looking at this box right here to see the second argument is then 100. So between 1 and 100, bracket it up and enter it, so 63. So that result then, we're imagining would be that that would be a random sample that wasn't for candidate A, because it's over the 60, right? Anything from 60 or under, we're imagining if we ask someone if they are for or against candidate A, would say they're for candidate A and everything over the 60, we're imagining is a result saying they're not for candidate A, right? So that's going to be our example here. And so then let's go ahead and copy this down. Now I could copy it down to like, let's make it copy it down to like 150 or something. I'm going to try to make it not exactly 100, even though that's the tendency, so that when we do our statistical analysis, it's not, we have to make sure we understand what's happening and do the percentage of the total. So I'm going to put my cursor on the fill handle here and I'm going to look at the numbers on the left-hand side to go down to like 151. So I'm going to scroll down and I'm looking at those numbers on the left and I went way too far. I was talking when I was looking at them, I was looking at them, but then I still, it didn't help. And then I'm going to say, okay, there's to 151. So there we have it. So there's our randomly generated numbers all the way down. So now I'm going to go to sample two. Let's just make 10 samples. So I'll call this one sample two, and then we'll select both of them and put my cursor on the fill handle and drag it to the right until we should get 10 of these. So five, seven, eight, nine, 10, 11, 10. There we have it. Let's do some, let's do some formatting up top by going to the home tab in the font group. And the dropdown, but not too hard. Don't break it when you hit it. Hit the dropdown. We'll make it black. And then on the colors of the letters, white. And then let's center that alignment center. I'm going to wrap the text alignment group, wrapping the text. And then I'll make all of these a little skinnier by selecting the entire column B to K. Let go, put my cursor in between any of those columns and squish it up a bit. Scrunch it up. All right. So now all I'm going to do is then copy these samples on overtake to K as well. So I can take this whole bit right here and I'm just going to fill handle it at the bottom. I could copy it by the way, because it'll copy the formula as you can see in the formula bar. But I'm going to just fill handle it and drag it over to K. Now notice it's a little tricky when I do this because I can't see the headers up top. But I noted that it was going to K and I only had 10 of them so I can copy them over. So now we've got this whole thing of random samples. Let's make it blue and bordered as is usually our custom. So I'm going to go to the home tab. Actually, let's make a table around it. I'm going to un-highlight it and put my cursor anywhere in the group of numbers. Insert tab, tables group, and then insert the table. If you have any more than one cell collected or selected, then it'll try to put a little table in place. If you just have one cell, it'll put the table properly to, what is that, B1 to K151 looks right. So we'll say OK. And there we have it. So now I can easily, and let's squish them up again. I'm going to select from B to K. They widened the cells. So let's squish it back up again. I like to conserve some space if I may. So then like you can see how it reshuffles every time we do some action to it. So let's keep that as our random generator tool. And then I'm just going to copy these and paste them over here but paste them one, two, three so that the formulas are no longer there. Picking a random sample of 10 samples. So now we're imagining we took, this one sample had 150, I think that we did, that were judging. And then we did that 10 times. So now I'm going to copy this whole thing. Just selecting the whole column from B to K, right click and copy. And let's put that over here. Let's skip a line so I can put some totals in column N. Right click and I'm going to paste it, but I'm going to paste it one, two, three. Paste it one, two, three. And so there we have it. I'm going to reformat my formatting. Selecting the, and I could by the way paste the formatting. So I could like right click and paste just the formatting. And so now I at least have the headers and it squishes them up nicely. But I'm still going to make this middle bit blue. So I'm going to make this blue. And so now we've got our randomly generated numbers that are not shuffled around. Home tab, font group, bucket. If you don't have that blue, more colors, standard blue. Okay. And then let's border it, font group, drop down all borders. All right. So now we just, what they call hard coded numbers are in here. No formulas. So now I can take my results of these samples. So I'm going to scroll down to the bottom. And I can say, all right, let's say 4A. So how many of these, if I'm selecting these are 4A? Well, everything that is above, I mean below 60s, including 60 and below, we're imagining is 4A. Right? So the random selection had anything, because 60%, you know, so we're saying everything from 60 to below on our random sample between 1 in 100 is going to be 4A. Anything above 60, we're going to be saying is for non-A, some other than A. So that's going to be the idea. So how can we do that with a formula? We're going to use a count if formula. So it's a little bit tricky because we want to say count if it's less than or equal to 60. So let's see what that looks like. So we're going to say equals count if. And we only have really one condition. So I can just use this first one. I'm going to select the entire range. So I can do this a little bit quickly. I'm using the keyboard up and then I'm going to hold down control shift up, selecting the entire range. Now I'm holding just shift down so that it's now selecting just the range I want. Now I can continue with my argument up top here if I would like to, and that moves up the argument bar up top. So that's kind of nice. I don't really have to go all the way back down again and I can say, okay, what's the next bit of the argument? I'm going to say comma and then we want to say, I've got to say less than or equal to. Now, because those are non-numerical terms, we have to put quotes around them. So I'm going to say quote, less than or equal to in the quote. And then I have to attach it to the number 60. So we have to use an and and then 60. So I'm going to say, so there's our formula. I'm going to say enter. And there it is. If I double click on it, I probably should have gone down here so you could see it more clearly anyway. But here's the range. There's the quotes greater than or equal to. And then an and and the 60. So it pulled everything greater than or equal to 60. Now you might like, if we have this out of 100, this is why I didn't want to have a sample of just 100. Because if it was out of 100, you'd say, well, that should come out to around 60% or something. But it's a sample, so it won't be perfect. But we didn't do it out of 100 purposely because not all our samples are going to be exactly 100 people or whatever we're sampling. Now we could say not a and do the same thing for not a. And I could just say, well, I know there's what 150 samples. So I could say this equals 150 minus 102. So 48. But let's do it with a formula to practice our formula. Similar formula equals. We're going to say count if brackets. I'm going to do it all with a keyboard now. Shift nine up arrow on the keyboard, selecting the range holding control shift on the keyboard. And then the up arrow takes me all the way to the top. Now I'm just holding shift down to select the range. Now I'm going to scroll back down this time so that I can actually see what I'm working on down here. Because it's a little bit easier to see possibly. And then I'm going to say comma. And we want this I'm going to put the criteria is going to be brackets. And greater than I don't need an equal sign here. Just greater than brackets and 60. So we want this one to be just simply greater than 60. So enter. So there we have it. So then I can put the total to give me a double check with the trustee sum function, the most famous function equals the S U M shift nine on the keyboard up arrow on the keyboard holding down shift up again. So we're going in 152 to in 153 enter. So there's a total of 150 which makes sense because I'm on 151 plus the header row. So there's 150 there. Let's put an underline home tab font group underline. All right. Now let's say percent. Let's make this a percent for a. So now the percent is going to be this divided by the total. So the per percent will simply be equal to the 102 divided by up once to the 150. And then I need to percentify that cell. That's what I call it home tab number group percentify. You better recognize the percentify. All right. We'll keep it there. All right. And then I'm going to say percent not a and I could I could do this the same way equals now the 48 divided by the total divided by the 150 enter. And then and then we're going to percentify home tab number percentify. And then font group underline. Let's sum it up. It should come out to 100 percent right equals the sum shift nine up arrow holding down shift up again comes out to one. Let's make it a percentification cell home tab number group percentify. And so there we have it. So it came out to 6832. So we took a random sample of between one and 100. And you would think it's kind of a little bit more skewed than you would think right from a random sample but it's a random sample so that could happen. And then I'm going to select all of these put my cursor on the fill handle and drag it to the right. And we'll see what happened in our 10 samples here. So we had 686557 so some are above some are below as would be what we would basically expect. So now let's make let's make this bottom column blue maybe. So I'm going to select the whole thing to make it this our results column. I'll make it home tab font group. Let's make it dark blue and white and let's put some font group borders around it. Now I'm going to I'm going to put this column. Let's say let's say the percent of a column in a column format. So let's copy copy this and say I'd like to see my results up here up top. So I'm going to go up top and say I want my results to be in a column format up here. So let's paste it first in column Z right click pasting it 123 or let's paste it. Let's paste it 123 and then I'll add the percents home tab number percentify. And then I'm going to copy it again and then paste it and why why that just is where we landed. We're paste that's why we paste it and why right click and we're going to paste it this time special. And we're going to paste we're going to transpose it this time transpose and boom. So now we've got it transposed right there. Let's delete these numbers. They're no longer useful. You have served your purpose. You're no longer necessary or that was dark. I had to you will be deleted home tab font group and black white. Let's center it and then and then I could put some black and white here or let's put some borders and then I could compare that to the expected. So what did we expect to happen point six 60% let's percentify that cell home tab numbers percentify. So then the difference difference the difference from the expectation equals the 68 minus the 60. Let's percentify that home tab number percentify. And then we'll select these two up top. Phil handle them grabbing Phil's handle and dragging Phil's handle down the Phil handle. All right. Let's make these top two black and white home tab font group black white center. And then we'll select all these data home tab font group borders and let's make this bucket down blue. And then we can pick up the average at the bottom. Let's let's first add another column here so that I can put my average down here. So I'm going to put my cursor on column Y. I'm going to select the whole column because when I add a column it'll always add it to the left. So I'm just going to right click and insert. And so now we'll just add a column. I'll put this average. So let's just take the average equals the the equals the average of these which comes out if I percentify at home tab numbers group percentify. We it comes out to 61. So if I take the average of all the results I'm going to then it comes out to be you know pretty close right. So so so so the bottom line when we simulated 10 10 samples of 150 we came out. We came out to an average of you know a little over over under. Well hold on a second. This should be equal to the one above it. And let's copy that down. All of these should be 60. We don't need totals down below. Okay that makes more sense. So now we were over we were over we were under we were under under over over over under right. But and then if we take the average of the whole of the whole thing let's copy this down one more time. This one came out exactly to 60 really huh. Then we're at we're at 61. So how many like if we had the samples this is one two three four five six seven eight nine ten. So this is sample number sample number making that home tab font group black white centered. Let's send her the numbers here to home tab alignment center and then let's make that our blue and our borders. So so and then let's make this X a little skinnier to I'm going to put my cursor between X and Y and make a skinny X skinny X Y. Because the X is between X and Y. So we'll keep the Y the same and we'll skinny the X. Okay so that's so that's the so that's how we can kind of use some of our tools and Excel and and and kind of simulate the random some random results we can practice with our statistical tools. Now once we get the idea with this with this random sample I took 10 sets of samples of 150 we could take way more than that right. I could level what if I did what if I took you know a 500 samples of of this amount. Let's let's let's see. Right. I'll do more of that next time. That's what we'll do next time. We'll take a whole button and I'll let's make L skinny too. I'm going to make L skinny. Okay. Sorry for interrupting myself on that. But so next time we'll basically we'll just make more samples so we can practice using you know some larger data sets and maneuver. And around Excel as we do so. And then once we get our results over here we could then possibly plot the results on on say an instant histogram and see see see what the results start to look like if we were to put them in the format of a histogram.