← ACARA Digital Technologies Years 9–10

The AVERAGEIF function

Finds the mean of only the values in the last range whose matching cell in the first range meets the criterion, such as the average mark for just Year 9 students.

Part of: ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 21

Understanding The AVERAGEIF function in ACARA Digital Technologies Years 9–10

What it does: Typing this formula next to the Year 9 version lets you compare the two group averages. Only the criterion changes. B2:B9 is checked for "Sport", and C2:C9 is the last argument, which holds the prices that get averaged. Rows that do not meet the criterion are skipped completely. They do not count as zero, so they do not drag the average down. The criterion range comes first and the range to average comes last. Swapping them checks the marks for the text "Year 10". Text criteria go inside straight double quotes. Without them, the spreadsheet will not read it correctly.

Common mistakes: The text criterion Y11 must be in double quotes. It should be =AVERAGEIF(A2:A9,"Y11",C2:C9). The ranges are swapped. The first range holds the group to test (column A) and the last range holds the numbers to average (column C). It should be =AVERAGEIF(A2:A9,"Gold",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 The AVERAGEIF function to a class →

This module's key terms are also in a printable crossword (Modules 333–377) and word search (Modules 333–412).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula finds the average of the marks in C2:C50 for only the Year 9 students listed in A2:A50?
=AVERAGE(A2:A50,"Year 9",C2:C50)=AVERAGEIF(A2:A50,"Year 9",C2:C50)
Matches
=AVERAGEIF(A2:A50,Year 9,C2:C50)
↓
The text criterion Year 9 is not in quotation marks, so the formula gives an error.
Applying
Meanings
Term
=AVERAGEIF(A2:A50,"Year 9",C2:C50)
Meaning
Finds the mean of only the values in the last range whose matching cell in the first range meets the criterion, such as the average mark for just Year 9 students.
Practice
=AVERAGEIF(B2:B9,"Tech",C2:C9)
Testing
Gaps
=(A2:A50,"Year 9",C2:C50)
Check Answer
Spots
3=AVERAGEIF(A2:A9,"Y10",C2:C9)
4=AVERAGEIF(A2:A9,Y11,C2:C9)

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

The 'Cosmic Dragon' badge — Orbit 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 finds the average of the marks in C2:C50 for only the Year 9 students listed in A2:A50?
A. =AVERAGEIF(A2:A50,"Year 9",C2:C50) ✓
B. =AVERAGE(A2:A50,"Year 9",C2:C50)
AVERAGEIF checks a criterion first. AVERAGE has no criterion, so it cannot pick out only the Year 9 rows.
2. In =AVERAGEIF(A2:A50,"Year 9",C2:C50), which range holds the values that get averaged?
A. A2:A50, the first range
B. C2:C50, the last range ✓
The first range is where the criterion is checked. The last range is the numbers that are averaged.
3. What does AVERAGEIF do with a row where column A says "Year 10" when the criterion is "Year 9"?
A. It counts that row's value as 0
B. It leaves that row's value out of the average ✓
Rows that do not meet the criterion are skipped completely. They do not count as zero, so they do not drag the average down.
4. Which formula gives the average mark for just the Year 10 group?
A. =AVERAGEIF(A2:A50,"Year 10",C2:C50) ✓
B. =AVERAGEIF(C2:C50,"Year 10",A2:A50)
The criterion range comes first and the range to average comes last. Swapping them checks the marks for the text "Year 10".
5. A teacher wants to compare how two groups performed. What is the best way to use AVERAGEIF?
A. Write one AVERAGEIF for each group and compare the two results ✓
B. Write one AVERAGEIF and it shows every group's average at once
One AVERAGEIF gives one group's mean. Use a separate formula for each group, then compare the answers.

Match the Pairs

=AVERAGEIF(B2:B9,"Tech",C2:C9) → Averages the prices in C2:C9 for only the rows where the category in B2:B9 is Tech
=AVERAGEIF(A2:A50,"Year 9",C2:C50) → Gives the average mark in C2:C50 for just the Year 9 students listed in A2:A50
To compare two groups, use one AVERAGEIF for each group and put the results side by side → =AVERAGEIF(A2:A50,"Year 10",C2:C50)
The average range is the last argument and holds the numbers to be averaged → =AVERAGEIF(B2:B9,"Sport",C2:C9)
=AVERAGEIF(A2:A50,Year 9,C2:C50) → The text criterion Year 9 is not in quotation marks, so the formula gives an error.
=AVERAGEIF(A2:A50,C2:C50,"Year 9") → The arguments are in the wrong order. The criterion should be second and the average range last.

Key Term

=AVERAGEIF(A2:A50,"Year 9",C2:C50)
Finds the mean of only the values in the last range whose matching cell in the first range meets the criterion, such as the average mark for just Year 9 students.

Fill in the Blanks

1. =AVERAGEIF(A2:A50,"Year 9",C2:C50)
Hint: Type the function name that averages only the values meeting a condition.
2. =AVERAGEIF(A2:A50,"Year 9",C2:C50)
Hint: Type the range that holds the group labels the criterion is checked against.
3. =AVERAGEIF(A2:A50,"Year 9",C2:C50)
Hint: Type the range of numbers that will be averaged for the matching rows.
4. =AVERAGEIF(A2:A50,"Year 9",C2:C50) =AVERAGEIF(A2:A50,"Year 10",C2:C50)
Hint: Type the range of marks to average, the same one used for the first group, so the groups can be compared.
5. =AVERAGEIF(B2:B30,"Boys",D2:D30) =AVERAGEIF(B2:B30,"Girls",D2:D30)
Hint: Type the function name used to find the average for a second group so the two groups can be compared.

Spot the Error

Question 1
A =AVERAGE(C2:C9)
B =AVERAGEIF(A2:A9,"Y9",C2:C9)
C =AVERAGEIF(A2:A9,"Y10",C2:C9)
D =AVERAGEIF(A2:A9,Y11,C2:C9)
Line D — The text criterion Y11 must be in double quotes. It should be =AVERAGEIF(A2:A9,"Y11",C2:C9).
Question 2
A =AVERAGEIF(A2:A9,"Red",C2:C9)
B =AVERAGEIF(A2:A9,"Blue",C2:C9)
C =AVERAGEIF(C2:C9,"Gold",A2:A9)
Line C — The ranges are swapped. The first range holds the group to test (column A) and the last range holds the numbers to average (column C). It should be =AVERAGEIF(A2:A9,"Gold",C2:C9).
Question 3
A =AVERAGEIF(A2:A9,"Red",C2:C9)
B =AVERAGEIF(A2:A9"Blue",C2:C9)
C =AVERAGEIF(A2:A9,"Gold",C2:C9)
Line B — There is a missing comma between the criteria range and the criterion. It should be =AVERAGEIF(A2:A9,"Blue",C2:C9).
Question 4
A =AVERAGE(C2:C9)
B =SUMIF(A2:A9,"Y10",C2:C9)
C =AVERAGEIF(A2:A9,"Y11",C2:C9)
Line B — SUMIF adds the Year 10 marks together, but we want the mean mark for the group. It should be =AVERAGEIF(A2:A9,"Y10",C2:C9).
Question 5
A =AVERAGEIF(B2:B9,"15>",C2:C9)
B =COUNTIF(B2:B9,">15")
C =AVERAGE(C2:C9)
Line A — The comparison operator must come before the number inside the quotes. It should be ">15", not "15>".

Student with a class code? Join your class →