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

period).

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.

Important note:

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

produced.

1. Open the ﬁle that you saved earlier in this chapter:

eggs33

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 ::

Custom Search