Aggregation & criteria › AVERAGEIF6 / 67

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeAVERAGEIF

AVERAGEIF: average score per class

Mr. Wahyu, a math teacher at a high school in Palembang, teaches two parallel classes. The test scores of both classes are mixed in one list, and he wants to compare the average of XI-1 with XI-2.

Task

In F2:F3, calculate the average score for the class written in E2 and E3 using AVERAGEIF. Lock the data ranges with $ so the formula in F2 can be copied to F3.

Cells to fillF2:F3

Required functionsAVERAGEIF

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
AVERAGEIF(criteria_range, criterion, average_range): only rows whose class matches are averaged.
Hint 2
The criterion can point straight at cell E2, so you do not need to type "XI-1".
Hint 3
Pattern: =AVERAGEIF($B$2:$B$11, E2, …).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

F2:F3

=AVERAGEIF($B$2:$B$11,E2,$C$2:$C$11)

Write it in F2, then fill down to F3.

Explanation

=AVERAGEIF($B$2:$B$11,E2,$C$2:$C$11) finds the rows in column B that equal E2, then averages column C on those rows: XI-1 averages 85.4 and XI-2 76.2. The criteria range and the average range must be the same length. For more than one condition (for example class XI-1 and score above 75), use AVERAGEIFS. Note: in Google Sheets with an Indonesian locale the argument separator is usually a semicolon (;); in this practice both commas and semicolons are accepted.

Starting worksheet: Sheet1
Row	A	B	C	E	F
1	Name	Class	Score	Class	Average
2	Rina	XI-1	82	XI-1
3	Dodi	XI-2	75	XI-2
4	Salsa	XI-1	90
5	Yoga	XI-2	68
6	Putri	XI-1	77
7	Arif	XI-2	88
8	Melati	XI-1	85
9	Bagas	XI-2	79
10	Intan	XI-1	93
11	Reza	XI-2	71
12
13
14

Fill in the requested cell, then press Check answer.

Validation result