The AVERAGEIF function

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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)

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

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

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)

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

=AVERAGEIF(A2:A50,"Year 9",C2:C50)

Fill in the Blanks

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

=_________(A2:A50,"Year 9",C2:C50)

=AVERAGEIF(______,"Year 9",C2:C50)

=AVERAGEIF(A2:A50,"Year 9",______)

=AVERAGEIF(A2:A50,"Year 9",C2:C50) =AVERAGEIF(A2:A50,"Year 10",______)

=AVERAGEIF(B2:B30,"Boys",D2:D30) =_________(B2:B30,"Girls",D2:D30)

Spot the Error

Shade in the letter of the line that contains an 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)

Question 2

A =AVERAGEIF(A2:A9,"Red",C2:C9)

B =AVERAGEIF(A2:A9,"Blue",C2:C9)

C =AVERAGEIF(C2:C9,"Gold",A2:A9)

Question 3

A =AVERAGEIF(A2:A9,"Red",C2:C9)

B =AVERAGEIF(A2:A9"Blue",C2:C9)

C =AVERAGEIF(A2:A9,"Gold",C2:C9)

Question 4

A =AVERAGE(C2:C9)

B =SUMIF(A2:A9,"Y10",C2:C9)

C =AVERAGEIF(A2:A9,"Y11",C2:C9)

Question 5

A =AVERAGEIF(B2:B9,"15>",C2:C9)

B =COUNTIF(B2:B9,">15")

C =AVERAGE(C2:C9)

Answer Key

The AVERAGEIF function

Section A: Quick Questions

A — =AVERAGEIF(A2:A50,"Year 9",C2:C50)

B — C2:C50, the last range

B — It leaves that row's value out of the average

A — =AVERAGEIF(A2:A50,"Year 10",C2:C50)

A — Write one AVERAGEIF for each group and compare the two results

Section B: 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.

Section C: Definitions

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

Section D: Fill in the Blanks

=AVERAGEIF(A2:A50,"Year 9",C2:C50)

=AVERAGEIF(A2:A50,"Year 9",C2:C50)
=AVERAGEIF(A2:A50,"Year 10",C2:C50)

=AVERAGEIF(B2:B30,"Boys",D2:D30)
=AVERAGEIF(B2:B30,"Girls",D2:D30)

Section E: Spot the Error

D — The text criterion Y11 must be in double quotes. It should be =AVERAGEIF(A2:A9,"Y11",C2:C9).

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

B — There is a missing comma between the criteria range and the criterion. It should be =AVERAGEIF(A2:A9,"Blue",C2:C9).

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

A — The comparison operator must come before the number inside the quotes. It should be ">15", not "15>".