← VCE Data Analytics U4O1

Calculating Pearson's r with CORREL

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.

Part of: VCE Data Analytics - U4O1 - Development and Evaluation — Module 37

Understanding Calculating Pearson's r with CORREL in VCE Data Analytics U4O1

What it does: Both B2:B9 and D2:D9 hold 8 values, so every Maths mark pairs with a Science mark and CORREL works. A CORREL result is never below -1 or above +1. Values near +1 show a strong positive linear link. CORREL needs two ranges of paired data. One range alone gives an error because there is nothing to compare it with. Pearson's r always sits between -1 (perfect negative link) and +1 (perfect positive link). It is not a percentage. The two ranges must be the same length so that every value in one has a partner in the other. Unequal lengths give a #N/A error.

Common mistakes: 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]. The spreadsheet function is called CORREL, not CORRELATION. Using the wrong name gives a #NAME? error. It should be =CORREL(B2:B9, C2:C9).

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Calculating Pearson's r with CORREL to a class →

This module's key terms are also in a printable crossword (Modules 3–37) and word search (Modules 3–62).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which formula correctly calculates Pearson's r for hours studied in A2:A9 and test scores in B2:B9?
=CORREL(A2:A9)=CORREL(A2:A9, B2:B9)
Matches
=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.
Applying
Meanings
Term
=CORREL(range1, range2)
Meaning
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.
Practice
=CORREL(B2:B9,D2:D9)
Testing
Gaps
=(B2:B9,C2:C9)
Check Answer
Spots
2x = [2, 4, 6, 8]
3y = [50, 60, 75]

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Thorny Devil' badge — Detail Master, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

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)
CORREL needs two ranges of paired data. One range alone gives an error because there is nothing to compare it with.
2. Which range of values can CORREL return for r?
A. From -1 to +1 ✓
B. From 0 to 100
Pearson's r always sits between -1 (perfect negative link) and +1 (perfect positive link). It is not a percentage.
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) ✓
The two ranges must be the same length so that every value in one has a partner in the other. Unequal lengths give a #N/A error.
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 ✓
The sign gives the direction and the size gives the strength. A value of -0.92 is close to -1, so the link is strong and negative.
5. Which Python call matches the spreadsheet formula =CORREL(A2:A9, B2:B9)?
A. statistics.correlation(hours, scores) ✓
B. statistics.mean(hours, scores)
statistics.correlation(x, y) returns Pearson's r for two equal-length lists. mean finds an average and does not measure a relationship.

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.

Key Term

=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.

Fill in the Blanks

1. =CORREL(B2:B9,C2:C9)
Hint: Type the spreadsheet function name that returns Pearson's correlation coefficient r for two ranges.
2. =CORREL(B2:B9,C2:C9)
Hint: Type the second range of paired data. It must be the same length as the first range.
3. import statistics r = statistics.correlation(x, y)
Hint: Type the name of the statistics module function that returns Pearson's r for two lists.
4. =CORREL(B2:B9,C2:C9)
Hint: Type the last cell of the second range so it has the same number of values as the first range.
5. r = statistics.correlation(x, y) if abs(r) <= 1: print("valid r")
Hint: Type the number that is the largest size r can ever be, in either direction.

Spot the 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)
Line 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].
Question 2
A Hours in B2:B9
B Marks in C2:C9
C =CORRELATION(B2:B9, C2:C9)
Line 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).
Question 3
A Temp: B2:B11, Sales: C2:C11
B =CORREL(B2:B11 C2:C11)
C Result must be -1 to +1
Line B — The two ranges must be separated by a comma. Without it the formula is invalid. It should be =CORREL(B2:B11, C2:C11).
Question 4
A Study hours: B2:B9
B =CORREL(B2:B9)
C r = 0.9 means strong positive
Line 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).
Question 5
A CORREL(B2:B9, C2:C9)
B Result: 0.85
C Strong positive link
Line 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).

Student with a class code? Join your class →