 Statistics and Excel. Statter plots with car related data. Get ready, taking a deep breath, holding it in for 10 seconds, 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'll basically build this from a blank worksheet so you can start with 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 in it so you can get right to the heart of the practice problem. Blank tab, just having the data within it so that we can practice formatting as we work through the practice problem. Let's go to the example tab to get an idea of the end result. We're going to take our data, we're going to sort our data in a table, then we're going to be making scatter plots from the data. Now the scatter plot will typically be taking two sets of data and trying to determine whether there'll be a relationship between the sets of data, the independent variable typically on the x or horizontal, the dependent on the y or the vertical. We'll create the scatter plot a couple different ways. One way we will just take the data and copy and paste it and that way we can easily put in the scatter plot and the second way we'll try to take our full data table over here and select the cells we want for the scatter plot. We'll also do another one comparing in a similar fashion miles per gallon and acceleration and have a similar scatter plot. All right let's go to the blank tab to get into it. Now we have a lot of data over here so if you don't have access to this data I believe we got this data from Kaggle so you can go into there and search for the data related to automobiles and possibly find this one there or you can copy the data set from here if you want to type in the data set. It's quite a long data set or you can do your own research for data set from a similar set of statistics here for this type of data set or you can try to create the data set although this would be a more complex one to do that with. All right so I'm going to scroll up top and I'm going to delete this item so we have our data on the left so I pull this data in it's formatted nicely because it's all in a vertical table for us already so what I would like to do is put a table around it now and then I can sort my data more easily so before I do that however let me format the cells like I do basically every time I'm going to put my cursor on the triangle up top selecting the entire sheet right click the selected area formatting the cells I usually go to currency and then I go to negative numbers bracketed and red get rid of the dollar sign and then you got to look and say do I need decimals and for most of these we don't need decimals except for possibly the acceleration so what I'm going to do is take the decimals off by default and then maybe go into this column and add them back in for this particular column and so I'm going to say okay let's do that that's my underlying format I can then select this data set g the entire column if I want to add decimals home tab number possibly add a couple decimals there so now we have it nice and number formatted then I'm going to put my cursor somewhere in the table and by the way let's make it bold to I'm going to select the entire thing again home tab font bold it and then I'm going to center the header rows so that excel knows that these are headers home tab alignment and center them you might also want to wrap the text alignment wrapping the text so that now you can see you know the whole thing and even though you've got a squished up even though the they're kind of squished up here so you got it wrapped all right let's put our cursor in somewhere in the data set I'm going to go to the insert tab tables and add a table as we have seen in the past now the dancing ants are going all the way from a1 to h39 so notice only two cells are necessary to to grab this whole table also note that when there's missing data like this blank piece right there sometimes you might want to go in and put a zero there or something so that you don't have nothing in the cell because when you have nothing in the cell that could mess up your tables and whatnot when you create the tables so I'm going to say okay so there is our table all right so we want to make a scatter plot now and the scatter plots are found in the insert tab and then the charts group and then we've got our dots here here's the scatters so now the question is well we need two sets of data so when we were looking at the histograms we were usually looking at you know one set of data and thinking about the midpoint of the histogram and the spread now we want to take a look at the relationship between two sets of data so first let's think about horsepower and the miles per gallon and so then the question would be well do you want the horsepower on the x axis or do we want it on the y do we want it on the on the horizontal or on the vertical the horizontal is is uh is usually going to be what we would call the dependent and the vertical the independent although you can kind of see it the other way uh two we're plotting the two against each other but so the easiest way to do this if I want the the horsepower on the horizontal making it the dependent variable traditionally I'm going to copy that and I'm going to put that over here in j so I copied the whole column I could select the whole column like this and control c or right click and copy I'm going to put that in j one and right click and paste so there we have it I can paste it like that so there we have our horsepower and then I want to pick up the miles per gallon so I'm going to pick up this one I'm going to select the entire column putting my cursor on b selecting the entire column you could say control c on the keyboard or right click and copy and then you could say control v on the keyboard or right click and paste so there we have it so there's our variables we want now again if I want to put a table around just these variables I can put my cursor here and insert tab and in the tables group table and hopefully again the dancing ants go all the way down to the bottom of the table they do I'm going to hit okay so there is our table so now we can sort our table they're kind of together now these two data sets are kind of together so I can then sort it this way I can say from a to z on the horsepower or from z to a on the horsepower and I can take the miles per gallon and take it from a to z or z to a in this fashion as well and so so we can sort it in that way and now if they have them side by side here it's really easy for me to enter the scatter plot because now I can just simply select these two items you know of course there's a couple ways you could do that you could select the entire thing scrolling down like this uh but that so and then if you do that once you enter the scatter you don't want to be down here because then your scatter plot will be input down here and you'll have to drag it up so you want to go all the way back up to the top but you might want if you have a table you can kind of you can click if you click