Microsoft Office Tutorials and References

In Depth Information

**Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data**

1. If necessary, add a new, empty column to the worksheet to the right of

the Y value column.

Do this by clicking any cell in the column immediately to the right of

the Y value column, and then, on the Home ribbon, clicking the Insert

button and selecting Insert Sheet Columns.

2. Place the cursor in this column in the same row as the first X value.

3.Enteranequalsign(
=) to start a formula.

4. Click the cell where the SLOPE function is located to enter its address

in the formula.

5. Press F4 to convert the address to an absolute reference.

It displays with dollar signs.

6. Enter the multiplication symbol (
*).

7. Click the cell containing the X value for that row.

8. Enter the addition symbol (
+).

9. Click the cell containing the INTERCEPT function to enter its address

in the formula.

10. Press F4 to convert the address to an absolute reference.

It displays with dollar signs.

11. Press Enter to complete the formula.

12. Make sure the cursor is on the cell where you just entered the formula.

13. Press Ctrl + C to copy the formula to the clipboard.

14. Hold down the Shift key and press the
↓ key until the entire column is

highlighted down to the row containing the last X value.

15. Press Enter to copy the formula to all selected cells.

At this point, the column of data you just created contains the Y values for

the linear regression line. The final step is to create a chart that displays both

the actual data as well as the computed regression line.

1. Highlight all three columns of data — the X values, the actual Y

values, and the computed Y values.

2. Click the Insert tab on the Ribbon (shown in Figure 11-3).

3. Click the Scatter chart button.