 Statistics and Excel. Election polls. Statistics example 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. 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 CPA six-pack shirts. A must-have for any pool or beach time. Mixing money with muscle. Always sure to attract attention. Yeah, even if you're not a CPA, you need this shirt so you can like pull in that iconic CPA six-pack stomach muscle vibe, man. You know, that CPA six-pack everyone envisions in their mind when they think CPA. Yeah, as a CPA, I actually and unusually don't have tremendous abs. However, I was blessed with a whole lot of belly hair. Yeah, allowing me to sculpt the hair into a nice CPA six-pack like shape, which is highly attractive. Yeah, maybe the shirt will help you generate some belly hair too. And if it does, make sure to let me know. Maybe I'll try wearing it on my head. And yes, I know six-pack isn't spelled right, but three letters is more efficient than four, so I trimmed it down a bit, okay? It's an improvement. You can start from a blank worksheet in the prior presentation or you can continue on from here with a blank worksheet if you so choose. If you do have access to this workbook, three tabs down below. Example, practice, blank. Example tab in essence, answer key. Practice tab having pre-formatted cells so you can get to the heart of the practice problem. The link tab is where we started in essence from a blank worksheet, practicing formatting cells within Excel as we work through the practice problem. So let's just recap what we did last time and then we'll continue on this time. So we are imagining we're trying to put statistical information into Excel that would mirror poll results where the population of the poll is actually, meaning the actual population results for the entire population we're imagining is 60% for candidate A. So then we wanted to think about how we can mirror random or sampling to go towards that conclusion. So we then use the function of random between 1 and 100 and we did 10 samples of 150 using our random generation tool. Anything that has a result of 60 and below, we imagined for candidate A any random generated result of over 60, we said we're not for candidate A. We then used our random sample generator to then make hard coded numbers that we could then test out and look at the results of those 10 samples of 150. We're going to use the same concept here, but now just practice making a larger bucket, taking this out not just to 10 samples, but let's do like 500 samples. We did a whole bunch of samples and then we can basically make a histogram about the results, the percent results of our samples and see what that starts to look like if we did this over and over again. So we're basically going to build this as though we're on a blank worksheet and just basically do it again, just make a bigger one here. Alright, so let's make a skinny AC. I'm going to put my cursor between the AC and the AD and drag that in a bit. I'm going to call this sample. Let's say this is sample 1 and then I'm going to put sample 2 and then I'm going to copy that. I'm going to select both of them, but I'm going to go all the way out to like 500 so I can see it's counting up. So I'm going to go all the way out here a whole bunch of times just to note that we can use an Excel, test things out with a relatively large amount of numbers. So we'll just bring this all the way out here and it's going to count up a little bit tedious, but much easier of course than if we were trying to do something like this by hand. So we're almost there. Let's bring it up to 500. There it is. So I'm going to then go home tab, font group as it's selected, bucket drop down making it black and white and then I'll center it. That's wrapping the text. Then I'll center it over here. And now let's bring it down to like 150 again. So I'll say one and two and then we'll bring these down. Let's make it just like 75 on the number of people this time, but we'll do it 500 times. So let's bring this down and put it to 75 this time. So I'll say 75 just to switch it up. Let's make it like 80. Let's make it 80. Sorry for the confusion. So there's the number of samples that I'm going to have. I probably should have moved that over. Let's actually start our random thing over here. So the random result I'm going to say is equal r, a, n, d. And I want random between, random between. And then once again the bottom is going to be 1, the top is going to be 100. Between 1 and 100 and enter. So we got 33. So because that's below 60, we're imagining this would be a 4, the candidate, a result. I'm going to copy that all the way down, fill handling it down, and we'll copy it all the way down to 80. So that looks good. And then I'm just going to select this copied area and I'm just going to right click and copy it like that. I'm going to paste it over the numbers. So I'll just use those numbers to kind of see how far down we were going. And I can right click and paste it. Just normal. And it's pasting the formulas. So you can see the formulas are being pasted. I'm going to continue to paste it, you know, all the way across. So I can keep on. I lost it though. So let's copy it again. I'm going to copy this whole thing. Now this time, instead of me going down here and using the fill handle to drag it to the right. Because I can copy the entire formula, what I want and I don't need to like see a trend line like I did up here. It's easier not to use the fill handle, but possibly just to copy the entire column of numbers. And then I'm going to select the first cell of every of every column all the way out to that 500. So it takes a little bit to get there. But we'll get there, man. We'll get there just one step at a time. Actually, this is like one step at a time, but really fast steps because we step really fast. We're fast. And then we're going to right click and paste it and boom. So now we've got this whole data. Let's go ahead and put a table in this whole thing. I'll put my cursor in any cell, not selecting more than one cell. I'm going to the insert tab up top table group, adding a table and then it's selecting from a D. Let's say let's see a D one to 81. So let's select the whole thing and say, OK, tables has been entered. Dishes are done, dude. Sorry, that's a movie line. They always made me laugh. OK, so now if I then click on any of the whole thing, then recalculates. So now let's copy this whole number random number generator thing and paste it on another section of 500 columns, but one, two, three only just so that we no longer have the formulas. So I'm going to copy this whole thing, putting my cursor on the whole column of AD and copying all the way to the right until we get to the 500 cells that are way over here. So there we have it to TI and then I'm going to right click and copy and then I'll scroll to the right this time instead of so I can just scroll to the right. Scroll to the right. I probably could have done that holding shift or something made a little bit easier too, but and then I'm just going to put my cursor down and move a few tabs on over and then I'll put this in the TL column. So I'm going to right click and paste it this time one, two, three just the values now before I'm done. I also am going to paste the formatting right click and paste the formatting one, two, three. So there we have it and then I'm just going to add my blue. Now another way you can do this is I might select the top the top column. I want to select the whole thing and I might then scroll to the right and then all the way down holding down shift and then select the bottom column. Notice what that did it selected the whole range with just two cells and it's a little bit faster possibly sometimes than scrolling over there. So then I'm going to or like holding down the click and then dragging right so I'm going to go to the home tab font group and hit the bucket drop down. I want that blue. If you don't have it, you go to the more colors standard and you make it that blue or you can make it another blue. If you if you want to switch things up, you don't have to make it that blue. It's not a critical component to the problem. I'm going to put the borders around the whole thing. Border in it. Okay. And then let's scroll on down and let's put our our calculations at the bottom. So the calculations are going to be I'm going to say the percent for a. So let's get let's say this is for a the number for a and I'm going to. So I want to take everything in this column that is 60% and below and say that those count those. I want you to count those because those are the results that we are imagining are for candidate a. So to do that little bit tricky same same formula we did last time. I'll try to do it all with the keystrokes this time. So equals count shift nine up arrow for for the and then a hold on not count. Just count if count if shift nine range because we're looking at the range here up arrow. Starting the range holding shift and control all the way to the top holding down just shift down arrow one time. There's our range we could see it in the formula bar up top scrolling back down so we could see a little bit more clearly down here though. And then we're going to say comma what's the criteria the criteria is going to be less than or equal to so we have to then because that's. Text I have to put the quotes around it less than or equal to in the quotes and then we have to put an and. To connect it and 60 and that should do it so less if it's less than or equal to 60 is what that's saying alright and then if it's so we got 50 there if it's not a. So I could do I could do you know 80 minus it could be equal to 80 minus the 50 but I'm going to do a similar formula so we can practice our formulas equals. Count if bracket shift nine up arrow hold twice holding down shift and control up arrow to the top holding down just shift down so that we just have the data. And then we're going to say comma to the next argument the criteria is that it has to be. Quote quote greater than and I don't need an equals this time great greater than 60 so we're going to say then. Greater than 60 and close it up boom okay something I have to put an and I need an and to connect that out okay and then boom so there we have it and so the total. Should add up to 80 equals the some shift nine up arrow holding down shift up again adding them up to 80 and then font group and underline let's take a look at the percent. The percent for a so that's going to be the 50 divided by the 80 so this will equal to up to the 50 divided by up to the 80. Enter it's at one we need to percentify that cell you better recognize the percentify hometown numbers percentify it. And then we're going to go down here the percent the percent for B is going to be the 30 divided by or over the 80 equals up up up to the 30 divided by up up the 80. Then we'll percentify so we can recognize home tab numbers group percentify and then let's put an underline while we're here fonts group underline. And then the total percent total percent is going to be equal to the trustee some it should come out to 100 this is just a double check number some in the 63 the 38 comes out to one. We need to percentify so we could recognize number group percentify. Okay let's copy the results across so we come out to 63 38. So we and we think that the actual results are around 60 which would make sense because we took a random generated thing between 100. So you would think it would come around come out if we sum them up around 60 percent around would be 60 or below. So if I select these items and I copy them and and let's use the fill handle to drag it across. I could copy them by the way to but either method is probably just as just as good because I have to go all the way to the right anyways to like 500 cells which is way over to the right here. So we're just going to let it run there. Notice how it slows down to the end kind of helps you out. So there we have it. There's our numbers. Let's make this whole thing. Now I shouldn't have done. Let's make it all blue for the bottom bit. Make it all blue like has been our custom for the bottom bit here for the results that we're currently on. We want results. What does that even mean? Means he gets results. McGarnacle. Okay. It's taken a long time to get to the right. There it goes. Finally and then home tab fonts group drop down dark blue and then and then we'll border it in the white for the letters. Okay. So if we look at these numbers here the percent for a you would expect them to be kind of hovering around the 50% right. So now let's take this entire this entire I'm sorry hovering around the 60%. Let's take let's take this entire data set and make it a vertically aligned data set. So I'm scrolling scrubbing to the right. So I want to take this percent for a and make it vertical. So I'm going to select this whole thing again. Are we really going to do this? Yeah, it's not. We're going to do this again. We're going to take this whole thing. And by the way, it might be a little faster if I select these and then I scrub to the right. And then I'm going to scrub to the right scrub to the right scrub to the right scrub to the right and then hold down shift and then boom. So that takes the whole thing a little bit faster. Control C for copy or right click and copy. And then I'm going to go up to the top here, paging up to the top. I'm going to paste it out here somewhere. Let's put it in AMU. Sounds like a sorority name or something some kind of. I don't know. It's the AMU. So and then let's paste it just 123 pasting it 123. And then I'm going to copy it. I'm not going to format it yet. I'm just going to right click and copy and then paste it in AMT right click and paste it special though, special pasting transposing the numbers. Transpose, por favor. And then let's percentify it while it's highlighted. Home tab numbers percentify. There we go. And then I'm going to delete all of these. All of this stuff. This is no longer needed. You have served your purpose. You will be deleted. Right click and don't know. Okay, I'm going to go to the home tab font group and make this black and white. So this is the expected value, which we think is 60% point six, and then we'll percentify it so we can recognize it. Number group percentify. And this is the difference difference, which is going to be equal to the 63 minus the 60. Now to get that 60 to copy down, I'm going to say equals the one above it. So that it copies it down and then I'm also going to percentify this cell. Home tab number percentify the difference. I need to percentify it home tab number percentify. I'm going to copy this down one time to get them even. So now I've got those two. Those two even I'm going to select these two now and I could just right click and copy that or I can put my cursor on the fill handle and drag it down. Let's copy it because that'll be so I could copy this instead of using the fill handle just to see because I'm copying formulas all the way down. So it should work because it's not like a trend kind of thing that we're doing so that I can go all the way down. There's 500 of them down here. It's kind of a lot. And so then we're going to say right click and paste. So there we have it. Let's put some headers formatting home tab font group black white. Let's center it and then now we can see you know that we're going to have some above and some below in our data set. Let's go all the way to the bottom and let's say I took the average of the whole 500 results. So now I'm going to say this is going to be the average equals the average control or shift nine up arrow holding down control shift up holding down shift down. There's our formula up in the formula bar and enter comes out to one until we percentify at home tab number group percentify to recognize. And then I'm going to add some decimal. So it's not exactly 60% but it should come out pretty close. You would think given the fact that we had you know samples of 80 that we that we summed up to 500 and did that 500 times. Right. So let's let's go all the way up to the top again and and let's make a history. Let's make this blue and bordered home tab font group will border it will blueify it blueify it to beautify it. There it is. And and then maybe make this black and white. I don't know. All right. Let's make a histogram out of our percentages now. So now we can I can select this whole thing. I could select all my data. I'm going to say control shift down and then shift up so I don't pick up the average down there. And then I'm going to hold down. Let me do that again. Hold on. So control shift down and then shift up and then control backspace to get back up to the top. You could just drag and select it. But I'm just trying to emphasize some of the keystrokes that might make a little bit easier if you're using a lot of data. So then we can go to the insert tab up top and charts histogram. His togram is a gram about his toe. Okay. That's stupid. So you can see here that that it all the results kind of hover around somewhat what we would expect. Right. We would think it'd be around like all of our results would be around 60. You would think right because because that's the actual population center. And so you could see we as we build more data, you would expect the results to be looking something like this. You would see some results out here somewhere. Notice we did. You know, we've got these results of 41 and these results out here, but you would expect them to start to populate a shape looking something like this, given the nature of the data that that were put in together and the center point being being the actual center point being of the population being the 60. So when we randomly take a bunch of samples, 500 samples in this case, and and look at the, you know, the average results of all of them, we start to get the shape of a histogram like so. So we'll just remove the title now and you could change the size of the buckets down here. Remember, if I click on these items, this is the default that they gave us with the number of bins being 15. So you could go down here and say, well, maybe I want the number of bins to be 20 or something like that. And it'll start to change the data set. If you bring it down to 10, you're going to get a different look and feel. If you go back to the auto, then you've got the auto automatic buckets. So that's the general idea. We'll do a couple more examples with our with our data sets in future presentations.