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.

Diese Übung ist Teil des Kurses

Introduction to Statistics in Google Sheets

Kurs anzeigen

Anleitung zur Übung

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.

Interaktive Übung

In dieser interaktiven Übung kannst du die Theorie in die Praxis umsetzen.

Übung starten