Microsoft Office Tutorials and References
In Depth Information
1. The underlying relationship between the two variables under study (X and Y) is
linear in the sense that a straight line, and not a curved line, can ﬁt among the
data points on the chart.
2. The errors of measurement are independent of each other (e.g. the errors from a
speciﬁc time period are sometimes correlated with the errors in a previous time
3. The errors ﬁt a normal distribution of Y-values at each of the X-values.
4. The variance of the errors is the same for all X-values (i.e., the variability of the
Y-values is the same for both low and high values of X).
A detailed explanation of these assumptions is beyond the scope of this topic,
but the interested reader can ﬁnd a detailed discussion of these assumptions in
Levine et al. ( 2011 , pp. 529–530).
Now, let’s create a chart summarizing these data.
Whenever you are preparing a chart, we strongly recommend
that you put the predictor variable (X) on the left, and the
criterion variable (Y) on the right in your Excel spreadsheet, so
that you do not get these variables backwards in your Excel
steps and make a mess of the problem in your computations. If
you do this as a habit, you will save yourself a lot of grief.
Let’s suppose that you would like to use weight as the predictor variable, and
that you would like to use it to predict the number of eggs produced in this species
of ﬁsh. Since the correlation between these two variables is +0.87, this shows that
there is a strong, positive relationship and that weight is a good predictor of eggs
1. Open the ﬁle that you saved earlier in this chapter:
6.3.1 Using Excel to Create a Chart and the Regression Line
Through the Data Points
Objective: To create a chart and the regression line summarizing the relationship
between weight and eggs produced.
2. Click and drag the mouse to highlight both columns of numbers (B8:C19), but
do not highlight the labels at the top of Column B and Column C.
Highlight the data set: B8:C19
Insert (top left of screen)
Scatter (at top of screen)
Click on top left chart icon under ‘‘scatter’’ (see Fig. 6.14 )
Search JabSto ::