Adding a trend line

Earlier, you saw how a high opening price has a negative correlation with the number of collectibles sold. The resulting scatterplot has a distinct pattern sloping down.

You then visualized the positive correlation between "Seller Rating" and "Close Price" as shown here. Let's now take this further by calculating the slope, intercept, and adding a trend line to better explore the relationship between the two columns.

Spreadsheets have 3 formulas for calculating the y-intercept and slope given two variables.

SLOPE() - will return the slope of a trend line or linear regression representing the linear change in one unit to another.
INTERCEPT() - returns the value where the trendline will intersect the y-axis.
LINEST() - calculates both the slope & the intercept of two variables using the least-squares method.

This exercise is part of the course

Introduction to Statistics in Google Sheets

View Course

Exercise instructions

In cell D5, calculate the SLOPE() of "Close Price" (B2:B101) and "Seller Rating" (A2:A101).
Similarly, calculate the INTERCEPT() in E5.
In cell D9, pass in B2:B101 and A2:A101 to LINEST(). It will return a values in D9 and E9.
Customize the scatter chart series with a trendline by clicking the checkbox.

Hands-on interactive exercise

Turn theory into action with one of our interactive exercises

Start Exercise