Calculating Pearson's r with CORREL

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which formula correctly calculates Pearson's r for hours studied in A2:A9 and test scores in B2:B9?

A =CORREL(A2:A9, B2:B9)

B =CORREL(A2:A9)

2. Which range of values can CORREL return for r?

A From -1 to +1

B From 0 to 100

3. Which pair of ranges can be used safely in CORREL?

A B2:B9 and C2:C12 (8 values and 11 values)

B B2:B9 and C2:C9 (8 values each)

4. CORREL returns -0.92. What does this tell you?

A A weak relationship, because the number is negative

B A strong negative linear relationship: as one variable rises, the other tends to fall

5. Which Python call matches the spreadsheet formula =CORREL(A2:A9, B2:B9)?

A statistics.correlation(hours, scores)

B statistics.mean(hours, scores)

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)		AThe Python function that returns Pearson's r for two equal-length lists, just like CORREL in a spreadsheet.
2statistics.correlation(x, y)		BPearson's r can never be greater than +1, so 1.7 is impossible. The calculation or the reading of the result must be wrong.
3CORREL needs two ranges with the same number of values so each value is paired with another		C=CORREL(B2:B9, D2:D9)
4The result r always sits between -1 and +1, so 0.9 shows a strong positive link		DReturns Pearson's r for the paired Maths and English marks in the two equal-length ranges B2:B9 and C2:C9.
5=CORREL(B2:B9, C2:C7)		EThe ranges have different lengths (8 values and 6 values), so the data cannot be paired and CORREL gives an error. Use C2:C9.
6r = 1.7 from =CORREL(B2:B9, D2:D9)		F=CORREL(B2:B9, C2:C9)

Definitions

=CORREL(range1, range2)

Fill in the Blanks

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

=______(B2:B9,C2:C9)

=CORREL(B2:B9,_____)

import statistics r = statistics.___________(x, y)

=CORREL(B2:B9,C2:__)

r = statistics.correlation(x, y) if abs(r) <= _: print("valid r")

Spot the Error

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

Question 1

A import statistics as st

B x = [2, 4, 6, 8]

C y = [50, 60, 75]

D r = st.correlation(x, y)

E print(r)

Question 2

A Hours in B2:B9

B Marks in C2:C9

C =CORRELATION(B2:B9, C2:C9)

Question 3

A Temp: B2:B11, Sales: C2:C11

B =CORREL(B2:B11 C2:C11)

C Result must be -1 to +1

Question 4

A Study hours: B2:B9

B =CORREL(B2:B9)

C r = 0.9 means strong positive

Question 5

A CORREL(B2:B9, C2:C9)

B Result: 0.85

C Strong positive link

Answer Key

Calculating Pearson's r with CORREL

Section A: Quick Questions

A — =CORREL(A2:A9, B2:B9)

A — From -1 to +1

B — B2:B9 and C2:C9 (8 values each)

B — A strong negative linear relationship: as one variable rises, the other tends to fall

A — statistics.correlation(hours, scores)

Section B: Match the Pairs

=CORREL(B2:B9, C2:C9) → Returns Pearson's r for the paired Maths and English marks in the two equal-length ranges B2:B9 and C2:C9.

statistics.correlation(x, y) → The Python function that returns Pearson's r for two equal-length lists, just like CORREL in a spreadsheet.

CORREL needs two ranges with the same number of values so each value is paired with another → =CORREL(B2:B9, D2:D9)

The result r always sits between -1 and +1, so 0.9 shows a strong positive link → =CORREL(B2:B9, C2:C9)

=CORREL(B2:B9, C2:C7) → The ranges have different lengths (8 values and 6 values), so the data cannot be paired and CORREL gives an error. Use C2:C9.

r = 1.7 from =CORREL(B2:B9, D2:D9) → Pearson's r can never be greater than +1, so 1.7 is impossible. The calculation or the reading of the result must be wrong.

Section C: Definitions

=CORREL(range1, range2) — Returns Pearson's r, from -1 to +1, showing the strength and direction of the linear link between two equal-length paired ranges of data.

Section D: Fill in the Blanks

=CORREL(B2:B9,C2:C9)

import statistics
r = statistics.correlation(x, y)

=CORREL(B2:B9,C2:C9)

r = statistics.correlation(x, y)
if abs(r) <= 1:
    print("valid r")

Section E: Spot the Error

C — The two lists must be the same length so each x value has a matching y value. x has 4 values but y has only 3, so st.correlation() raises an error. y should have 4 values, like [50, 60, 75, 85].

C — The spreadsheet function is called CORREL, not CORRELATION. Using the wrong name gives a #NAME? error. It should be =CORREL(B2:B9, C2:C9).

B — The two ranges must be separated by a comma. Without it the formula is invalid. It should be =CORREL(B2:B11, C2:C11).

B — CORREL needs two ranges, because r measures the link between a pair of data sets. With only one range the formula is incomplete. It should be =CORREL(B2:B9, C2:C9).

A — A spreadsheet formula must start with an equals sign. Without it the cell just shows text and nothing is calculated. It should be =CORREL(B2:B9, C2:C9).