Pearson's correlation coefficient (r) with CORREL
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula calculates Pearson's r for Maths marks (B2:B9) and English marks (C2:C9)?
2. CORREL returns 0.93 for two fields. What does this show?
3. Which result is impossible for =CORREL(B2:B9,C2:C9)?
4. Which pair of ranges is valid in CORREL?
5. CORREL returns -0.88 for temperature and hot chocolate sales. What does this mean?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=CORREL(B2:B9,C2:C9) | A=CORREL(B2:B9,D2:D9) | |
| 2r = -0.92 | BCalculates Pearson's r to show how strongly the two number columns are linearly related | |
| 3Find r for two fields using two ranges with the same number of values | CThe two ranges are different lengths (8 values and 6 values), so CORREL cannot pair the values and gives an error | |
| 4An r value near 0 means there is little or no linear relationship between the fields | DA strong negative correlation: as one variable increases, the other tends to decrease | |
| 5=CORREL(B2:B9,C2:C7) | E=CORREL(A2:A8,B2:B8) gives 0.03 | |
| 6r = 1.4 | FPearson's r can never be more than 1 or less than -1, so 1.4 is an impossible result |
C
Definitions
=CORREL(B2:B9,C2:C9)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=______(B2:B9,C2:C9)
2.
=CORREL(B2:B9,_____)
3.
=CORREL(B2:__,C2:C9)
4.
=CORREL(B2:B9,_____)
5.
=ROUND(______(B2:B9,D2:D9),2)
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
=CORREL(B2:B9,C2:C8)
B
=AVERAGE(B2:B9)
C
=AVERAGE(C2:C9)
Question 2
A
A11: r for Maths and Science
B
B11: =CORREL(B2:B9,D2:D9)
C
C11: =ROUND(A11,2)
Question 3
A
Maths in B2:B9
B
English in C2:C9
C
r goes in E2
D
E2: =CORREL(B2:B9)
Question 4
A
E2: =CORREL(B2:B9,C2:C9)
B
E3: =CORELL(B2:B9,D2:D9)
C
E4: =MAX(E2:E3)
Question 5
A
Max in B2:B8, Rain in D2:D8
B
F2: =CORREL(B2:B8,D2:D8)
C
F3: =ROUND(F2,2
Answer Key
Pearson's correlation coefficient (r) with CORREL
Section A: Quick Questions
Q1
B — =CORREL(B2:B9,C2:C9)
Q2
B — A strong positive linear relationship
Q3
A — r = 1.4
Q4
A — =CORREL(B2:B9,D2:D9)
Q5
A — As temperature rises, sales tend to fall
Section B: Match the Pairs
Q1
=CORREL(B2:B9,C2:C9) → Calculates Pearson's r to show how strongly the two number columns are linearly related
Q2
r = -0.92 → A strong negative correlation: as one variable increases, the other tends to decrease
Q3
Find r for two fields using two ranges with the same number of values → =CORREL(B2:B9,D2:D9)
Q4
An r value near 0 means there is little or no linear relationship between the fields → =CORREL(A2:A8,B2:B8) gives 0.03
Q5
=CORREL(B2:B9,C2:C7) → The two ranges are different lengths (8 values and 6 values), so CORREL cannot pair the values and gives an error
Q6
r = 1.4 → Pearson's r can never be more than 1 or less than -1, so 1.4 is an impossible result
Section C: Definitions
Q1
=CORREL(B2:B9,C2:C9) — Finds Pearson's r for two equal-length number ranges. The result is from -1 to 1: near 1 is strong positive, near -1 is strong negative, near 0 is no linear link.Section D: Fill in the Blanks
Q1
=CORREL(B2:B9,C2:C9)Q2
=CORREL(B2:B9,C2:C9)Q3
=CORREL(B2:B9,C2:C9)Q4
=CORREL(B2:B9,D2:D9)Q5
=ROUND(CORREL(B2:B9,D2:D9),2)Section E: Spot the Error
Q1
A — The two ranges must be the same length. B2:B9 has 8 cells but C2:C8 has 7, so CORREL gives #N/A. It should be =CORREL(B2:B9,C2:C9).
Q2
C — A11 holds a text label, not the r value, so ROUND returns #VALUE!. It should round the CORREL result in B11: =ROUND(B11,2).
Q3
D — CORREL needs two ranges, one for each field. Giving only one range is an error. It should be =CORREL(B2:B9,C2:C9).
Q4
B — The function name is misspelled as CORELL, which gives #NAME?. The correct name is CORREL.
Q5
C — The closing bracket is missing, so the formula is not valid. It should be =ROUND(F2,2).