← VCE Data Analytics U3O1

Pearson's correlation coefficient (r) with CORREL

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.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 88, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 44

Understanding Pearson's correlation coefficient (r) with CORREL in VCE Data Analytics U3O1

What it does: Both ranges cover rows 2 to 9, so they are equal in length, which is what CORREL needs. A result close to 0 from CORREL shows almost no linear link between the two ranges. An r value close to +1 means that as one field increases, the other tends to increase too. Pearson's r always falls between -1 and 1, so 1.4 can never be returned. A negative r means the two fields move in opposite directions. A value near -1 makes this a strong link. Strength depends on how far r is from 0, not on its sign. -0.95 is much closer to -1 than 0.40 is to 1.

Common mistakes: 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). A11 holds a text label, not the r value, so ROUND returns #VALUE!. It should round the CORREL result in B11: =ROUND(B11,2).

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 Pearson's correlation coefficient (r) with CORREL to a class →

This module's key terms are also in a printable crossword (Modules 50–91) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula calculates Pearson's r for Maths marks (B2:B9) and English marks (C2:C9)?
=CORREL(B2:B9,C2:C9)=CORREL(B2:B9)
Matches
=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
Applying
Meanings
Term
=CORREL(B2:B9,C2:C9)
Meaning
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.
Practice
=CORREL(B2:B9,D2:D9)
Testing
Gaps
=(B2:B9,C2:C9)
Check Answer
Spots
1=CORREL(B2:B9,C2:C8)
2=AVERAGE(B2:B9)

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

The 'Void Whale' badge — Definition Drifter, 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 calculates Pearson's r for Maths marks (B2:B9) and English marks (C2:C9)?
A. =CORREL(B2:B9)
B. =CORREL(B2:B9,C2:C9) ✓
CORREL needs two ranges, one for each field, separated by a comma.
2. CORREL returns 0.93 for two fields. What does this show?
A. A strong negative linear relationship
B. A strong positive linear relationship ✓
An r value close to +1 means that as one field increases, the other tends to increase too.
3. Which result is impossible for =CORREL(B2:B9,C2:C9)?
A. r = 1.4 ✓
B. r = -0.6
Pearson's r always falls between -1 and 1, so 1.4 can never be returned.
4. Which pair of ranges is valid in CORREL?
A. =CORREL(B2:B9,D2:D9) ✓
B. =CORREL(B2:B9,D2:D6)
Both ranges must be the same length. B2:B9 has 8 values and D2:D6 has only 5, so the formula would give #N/A.
5. CORREL returns -0.88 for temperature and hot chocolate sales. What does this mean?
A. As temperature rises, sales tend to fall ✓
B. As temperature rises, sales tend to rise
A negative r means the two fields move in opposite directions. A value near -1 makes this a strong link.

Match the Pairs

=CORREL(B2:B9,C2:C9) → Calculates Pearson's r to show how strongly the two number columns are linearly related
r = -0.92 → A strong negative correlation: as one variable increases, the other tends to decrease
Find r for two fields using two ranges with the same number of values → =CORREL(B2:B9,D2:D9)
An r value near 0 means there is little or no linear relationship between the fields → =CORREL(A2:A8,B2:B8) gives 0.03
=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
r = 1.4 → Pearson's r can never be more than 1 or less than -1, so 1.4 is an impossible result

Key Term

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

Fill in the Blanks

1. =CORREL(B2:B9,C2:C9)
Hint: Type the function name that calculates Pearson's correlation coefficient r.
2. =CORREL(B2:B9,C2:C9)
Hint: Type the second range. It must hold the same number of values as the first range.
3. =CORREL(B2:B9,C2:C9)
Hint: Type the last cell of the first range so both ranges have eight values.
4. =CORREL(B2:B9,D2:D9)
Hint: Type the range for the Science column, which matches the size of the first range.
5. =ROUND(CORREL(B2:B9,D2:D9),2)
Hint: Type the function that finds r, nested inside the rounding function.

Spot the Error

Question 1
A =CORREL(B2:B9,C2:C8)
B =AVERAGE(B2:B9)
C =AVERAGE(C2:C9)
Line 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).
Question 2
A A11: r for Maths and Science
B B11: =CORREL(B2:B9,D2:D9)
C C11: =ROUND(A11,2)
Line 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).
Question 3
A Maths in B2:B9
B English in C2:C9
C r goes in E2
D E2: =CORREL(B2:B9)
Line D — CORREL needs two ranges, one for each field. Giving only one range is an error. It should be =CORREL(B2:B9,C2:C9).
Question 4
A E2: =CORREL(B2:B9,C2:C9)
B E3: =CORELL(B2:B9,D2:D9)
C E4: =MAX(E2:E3)
Line B — The function name is misspelled as CORELL, which gives #NAME?. The correct name is CORREL.
Question 5
A Max in B2:B8, Rain in D2:D8
B F2: =CORREL(B2:B8,D2:D8)
C F3: =ROUND(F2,2
Line C — The closing bracket is missing, so the formula is not valid. It should be =ROUND(F2,2).

Student with a class code? Join your class →