- Open the data set in Excel. As usual, graph your data before running your statistical test. See scatter plot.
2. Select Data Analysis from the “Data” tab at the top of your window. Then select Regression and click “OK”.
3. In the Regression window, fill in the appropriate values for the Y and X data ranges, by clicking in the box and then highlighting the cells where the data is located. Include labels when highlighting and select the “Labels” box. Click in the box beside output range and then click in the cell on the sheet where you would like the output displayed. Click OK.
4. A number of tables will appear as output from the test. Before we look at the hypothesis test, we first want to make sure we can report the regression line. In the output, look for the Coefficients column. There is a row for the intercept and a row for the X variable. The values there are the fitted intercept and slope. For this example, we found that:
5. Now, we look to see if the regression is significant. You can do so by looking at the F and corresponding P-values in the ANOVA table of the output. The P-value is indicated by “Significance F”. In this case, P<0.05, so we say that the regression is significant or that there is a significant slope.
6. The statistic R2 in the output can give us information about how well the model fits. In particular, R-squared measures how much of the variation in Y may be accounted for by its linear relationship with X (the model). Larger values indicate a stronger association between X and Y. Look for the R squared value in the Regression Statistics table. Here, the value is 0.39, so here we would say about 39% of the variation in territory size is attributable to its linear relationship with bite force. This indicates a moderate fit to the data (R squared values range from 0 to 1).
7. In the results portion of your paper, you would indicate that an increase in territory is associated with an increase in bite force for male collared lizards in this study (F= 5.8, P<0.05, R2=0.39). Note that the general conclusion would be that the association was significant (using a two-sided test), but you can look at the slope of +11.68 or the scatterplot here to understand the relationship is positive.