SLOPE and INTERCEPT for a prediction equation

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

SLOPE and INTERCEPT for a prediction equation

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula returns the gradient (m) of the best-fit line when the y values are in B2:B9 and the x values are in A2:A9?
A =SLOPE(A2:A9,B2:B9)
B =SLOPE(B2:B9,A2:A9)
2. Which formula returns the y-intercept (c) of the best-fit line when the y values are in B2:B9 and the x values are in A2:A9?
A =INTERCEPT(B2:B9,A2:A9)
B =INTERCEPT(A2:A9,B2:B9)
3. A best-fit line has SLOPE = 2.5 and INTERCEPT = 10. Which equation is the prediction equation?
A y = 2.5*x + 10
B y = 10*x + 2.5
4. What does INTERCEPT tell you about the best-fit line?
A How much y rises for each 1 increase in x
B The predicted y value when x is 0
5. The slope in cell E1 is 3 and the intercept in cell E2 is 5. Which formula predicts y when x is in A2?
A =E1*A2+E2
B =E1+A2*E2
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:B9,A2:A9) AThe arguments are the wrong way round. SLOPE needs known_y first, so the marks (B2:B9) must come before the hours (A2:A9).
2=INTERCEPT(B2:B9,A2:A9) BReturns m, the gradient of the best-fit line, using the y values in B2:B9 and the x values in A2:A9
3Once m is in E1 and c is in E2, a prediction for the x value in D2 is written as y = m*x + c CThe equation is built incorrectly. It treats the intercept as the gradient, so m is not multiplied by x and c is multiplied instead.
4To get the intercept c of a line where sales are y (C2:C13) and temperature is x (B2:B13) DReturns c, the value where the best-fit line crosses the y-axis (the predicted y when x is 0)
5=SLOPE(A2:A9,B2:B9) where A is x (hours studied) and B is y (test mark) E=INTERCEPT(C2:C13,B2:B13)
6=E1+E2*D2 (trying to predict y from m in E1 and c in E2) F=E1*D2+E2
C

Definitions

SLOPE and INTERCEPT
D

Fill in the Blanks

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

1.
=_____(B2:B9,A2:A9)
2.
=_________(B2:B9,A2:A9)
3.
=SLOPE(_____,A2:A9)
4.
=E1_A12+E2
5.
=E1*A12+__
E

Spot the Error

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

Question 1
A E1: =SLOPE(C2:C9,B2:B9)
B E2: =INTERCEPT(C2:C9,B2:B9)
C E3: =E2*F1+E1
Question 2
A E1: =SLOPE(C2:C9,B2:B9)
B E2: =INTERCEPT(C2:C9,B2:B9)
C E3: =E1+F1+E2
Question 3
A E1: =SLOPE(B2:B9,D2:D9)
B E2: =INTERCEPT(D2:D9,B2:B9)
C E3: =E1*F1+E2
Question 4
A E1: =SLOPE(D2:D9,B2:B9)
B F1: 70
C E3: =INTERCEPT(B2:B9,D2:D9)
D E4: =E1*F1+E3
Question 5
A E1: =SLOPE(C2:C9,B2:B9)
B E2: =SLOPE(C2:C9,B2:B9)
C E3: =E1*F1+E2

Answer Key

SLOPE and INTERCEPT for a prediction equation
Section A: Quick Questions
Q1
B — =SLOPE(B2:B9,A2:A9)
Q2
A — =INTERCEPT(B2:B9,A2:A9)
Q3
A — y = 2.5*x + 10
Q4
B — The predicted y value when x is 0
Q5
A — =E1*A2+E2
Section B: Match the Pairs
Q1
=SLOPE(B2:B9,A2:A9) → Returns m, the gradient of the best-fit line, using the y values in B2:B9 and the x values in A2:A9
Q2
=INTERCEPT(B2:B9,A2:A9) → Returns c, the value where the best-fit line crosses the y-axis (the predicted y when x is 0)
Q3
Once m is in E1 and c is in E2, a prediction for the x value in D2 is written as y = m*x + c → =E1*D2+E2
Q4
To get the intercept c of a line where sales are y (C2:C13) and temperature is x (B2:B13) → =INTERCEPT(C2:C13,B2:B13)
Q5
=SLOPE(A2:A9,B2:B9) where A is x (hours studied) and B is y (test mark) → The arguments are the wrong way round. SLOPE needs known_y first, so the marks (B2:B9) must come before the hours (A2:A9).
Q6
=E1+E2*D2 (trying to predict y from m in E1 and c in E2) → The equation is built incorrectly. It treats the intercept as the gradient, so m is not multiplied by x and c is multiplied instead.
Section C: Definitions
Q1
SLOPE and INTERCEPT — SLOPE gives m and INTERCEPT gives c of the best-fit line, so a prediction can be written as y = m*x + c. Both functions take known_y first, then known_x.
Section D: Fill in the Blanks
Q1
=SLOPE(B2:B9,A2:A9)
Q2
=INTERCEPT(B2:B9,A2:A9)
Q3
=SLOPE(B2:B9,A2:A9)
Q4
=E1*A12+E2
Q5
=E1*A12+E2
Section E: Spot the Error
Q1
C — The equation y = m*x + c needs the slope (m, in E1) multiplied by x and the intercept (c, in E2) added. The formula swapped them. It should be =E1*F1+E2.
Q2
C — In y = m*x + c the slope m is multiplied by x, not added to it. The line should be =E1*F1+E2.
Q3
A — SLOPE takes known_y first, then known_x. The arguments were swapped, so the slope is for the wrong direction. It should be =SLOPE(D2:D9,B2:B9).
Q4
C — INTERCEPT also takes known_y first, then known_x. The ranges were swapped, which gives the intercept of the wrong line. It should be =INTERCEPT(D2:D9,B2:B9).
Q5
B — E2 should hold c, the intercept, but SLOPE gives m again. The line should use =INTERCEPT(C2:C9,B2:B9).