 Hi, this is Dr. Don. This is a problem from Larson Chapter 9, 9.2, 0.33, in which we take data and we need to find the regression equation as well as a line plot and also a plot of the residuals. I showed you previously how to do it using StatCrunch. This time we're going to do it using the Excel data analytics tool pack, which should be bundled in with almost every version of Excel. Let's go. We're going to start by clicking on Open in Excel. We can see on the Windows version here is the spreadsheet's been downloaded. I'm going to double click that to open it. It comes up and we have to enable editing. And there we have our data, the X and the Y. In order to do the regression, we go to Data, Data Analysis, which opens up the Data Analysis tool pack, and you want to scroll down until you see Regression and click on Regression. It opens up this dialog box and we need to input our Y range. Initially, be careful. Make sure it's the Y value, the response variable, and here they've labeled Y for us. I'm going to highlight those cells and then I'm going to input my X variable. I just cleaned that out and I've got my insertion point there. So I'm going to highlight and select the X cells. I double check to make sure that they're same. We've got to have 10 values in the response variable and 10 values for the predictor variable. If you accidentally don't highlight the same number of cells, it won't work. We want to click labels. Since I do have a label in the first row there, we're going to let it be in a new worksheet. I want residuals. I want the residual plot that gives us the residual versus the X values that the question wanted. And we want the line flip plot, which is the scatter plot that we will need. I just click OK. And Excel gives me a new tab. And I've got two plots here. I'm going to drag those down and they're pretty small. But of course with Excel, you can just drag these corners and expand them to make them a little bit bigger. The residual plot is there. And here on the line flip plot, the blue are the actual values of Y. The orange is the predicted values. I believe I could click on that and I want to add a trim line. And I want to display the equation on the trim line. And I want to close that. Now we've got that equation. I'm going to click on it and move it so we can see the equation. And we can see the equation is Y equal 11.158 times X plus 18.568. If we look over here in our ANOVA output, which is the standard part of a regression output, we see again, our intercept is the 18.568, which matches that. And our slope is .115 times X. And we want to look at the significance of the ANOVA. The ANOVA is not significant, .35. So that tells us that the output of this ANOVA is questionable, that it is not necessarily going to allow us to predict Y given X's. This needed to be down below our significance level, which is traditionally .05. So this tells us a lot that this slope is not necessarily different from zero. Remember our null is that the slope is zero. And we need to reject that null in order to say the slope is not zero. So this apparent slope there may in fact not really be there. And that's what the .35 tells us. So let's look at that. If we look at the residuals, we can see why. There's a definite pattern here. This is not a random distribution of residuals. Remember that we need to have a random variation of our residuals around zero. This line is the zero line. If that's not random, and here you can see that pattern again. It goes up, it goes down, it goes up. That is not random. Therefore that tells us again that this is not a reliable regression line. So I hope that helps. And all you need to do is just to compare this plot and this plot with the options in the question. And I think you'll be able to answer it pretty easily. I would point out over here that part of the output are these residuals. And I know one of your classmates gave you a worksheet which does that. This data analysis tool pack does it automatically and gives you the predicted values of why. I'll expand that a little bit. And the residuals, the difference. If you remember, there's the predicted value for example, the orange, and it differs from the actual value for that point. That's that residual that comes up over there. It's minus 2.8. The actual value is down below the predicted value. And you can see the same thing is true for this next point. The actual values below the predicted value. Here the actual values are above, so they should be positive. That one gives us a residual of positive 4.28. So that's what the residual, I'm sorry, the residual plot is telling us, but you can derive it from looking at the line plot. So I hope this helps.