Trendlines on a chart

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

Trendlines on a chart

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. What does a trendline show on a scatter plot?
A The overall direction of the data
B The exact value of every single point
2. Which trendline slope means the values are going up as time goes on?
A A negative slope, such as y = -2.5x + 10
B A positive slope, such as y = 2.5x + 10
3. A trendline's equation is y = 3x + 5. What does the 3 tell you?
A y is always equal to 3
B y goes up by about 3 for every 1 increase in x
4. Which R-squared value shows the trendline fits the points best?
A R² = 0.95
B R² = 0.15
5. Where would you add a trendline in a spreadsheet chart?
A Chart elements menu, then Trendline
B Sort the data from A to Z
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1=SLOPE(B2:B20,A2:A20) Ay = 2.5x + 10
2=RSQ(B2:B20,A2:A20) BA negative slope means the trend goes down as x increases, not up. Rising data has a positive slope.
3A trendline equation in the form y = mx + b, where m is the slope that shows the direction of the trend CGives the R-squared value, a number from 0 to 1 showing how well the trendline fits the points
4A very high R-squared means the trendline fits the data closely DAn R-squared near 0 means a poor fit. A perfect fit is close to 1.
5y = -3x + 8 means the data is rising EFinds the slope of the trendline, which is how much the y values change for each 1 step along x
6R² = 0.05 means the trendline fits perfectly FR² = 0.96
C

Definitions

trendline
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
=_____(B2:B12,A2:A12)
2.
=___(B2:B12,A2:A12)
3.
y = _x + b
4.
R-squared close to _ means a strong fit
5.
A positive slope means the trend goes __
E

Spot the Error

Shade in the letter of the line that contains an error.

Question 1
A chart = Scatter(A2:A20)
B chart.addTrendline("linear")
C chart.showEquation = true
Question 2
A m = SLOPE(B2:B20,A2:A20)
B b = INTERCEPT(B2:B20,A2:A20)
C y = m + x * b
Question 3
A IF slope > 0 THEN
B trend ← "rising"
C ELSE IF slope < 0 THEN
D trend ← "steady"
E ELSE trend ← "flat"
Question 4
A Trendline type: Linear
B Display equation: on
C Display R-squared: on
D R-squared = 1.93
E Fit: strong
Question 5
A sales ← [10, 14, 19, 25]
B weeks ← [1, 2, 3, 4]
C slope ← SLOPE(weeks, sales)
D OUTPUT slope

Answer Key

Trendlines on a chart
Section A: Quick Questions
Q1
A — The overall direction of the data
Q2
B — A positive slope, such as y = 2.5x + 10
Q3
B — y goes up by about 3 for every 1 increase in x
Q4
A — R² = 0.95
Q5
A — Chart elements menu, then Trendline
Section B: Match the Pairs
Q1
=SLOPE(B2:B20,A2:A20) → Finds the slope of the trendline, which is how much the y values change for each 1 step along x
Q2
=RSQ(B2:B20,A2:A20) → Gives the R-squared value, a number from 0 to 1 showing how well the trendline fits the points
Q3
A trendline equation in the form y = mx + b, where m is the slope that shows the direction of the trend → y = 2.5x + 10
Q4
A very high R-squared means the trendline fits the data closely → R² = 0.96
Q5
y = -3x + 8 means the data is rising → A negative slope means the trend goes down as x increases, not up. Rising data has a positive slope.
Q6
R² = 0.05 means the trendline fits perfectly → An R-squared near 0 means a poor fit. A perfect fit is close to 1.
Section C: Definitions
Q1
trendline — A line fitted through a chart's points that shows the overall direction of the data. A chart tool can add one with its equation, and the R-squared value shows how well it fits.
Section D: Fill in the Blanks
Q1
=SLOPE(B2:B12,A2:A12)
Q2
=RSQ(B2:B12,A2:A12)
Q3
y = mx + b
Q4
R-squared close to 1 means a strong fit
Q5
A positive slope means the trend goes up
Section E: Spot the Error
Q1
A — A scatter plot needs two columns, x values and y values, so the range should be A2:B20. A single column gives no pairs of points to fit a trendline through.
Q2
C — The equation of a straight trendline is y = m * x + b. The slope m multiplies x and the intercept b is added on the end.
Q3
D — A negative slope means the trendline goes down as x increases, so the trend is "falling". Line 4 should be trend ← "falling".
Q4
D — R-squared always sits between 0 and 1, so 1.93 is impossible. A value like 0.93 shows a strong fit.
Q5
C — SLOPE takes the y values first, then the x values. Sales (y) depend on weeks (x), so the call should be SLOPE(sales, weeks).