Statistics › AVERAGEIF26 / 144

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

MediumFreeAVERAGEIF

AVERAGEIF: average salary by division

Rizky from the HR team of a distribution company in Surabaya is preparing material for a salary review meeting. The director wants to see the average base salary of each division.

Task

Fill F2:F4 with the average salary for each division in E2:E4, using AVERAGEIF. Write the formula in F2, then copy it down.

Cells to fillF2:F4

Required functionsAVERAGEIF

Expected result

Loading the expected result…

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

Hint 1
AVERAGEIF(criteria_range, criteria, average_range) has the same shape as SUMIF, but returns an average.
Hint 2
Criteria range = the Division column (B2:B11), criteria = E2, average range = the Base salary column (C2:C11).
Hint 3
Lock both ranges with $ so they stay on rows 2 to 11 when copied.

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:F4

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

Write it in F2, then fill down to F4.

Explanation

=AVERAGEIF($B$2:$B$11,E2,$C$2:$C$11) averages only the salaries of employees whose division matches E2. The IT result (Rp11,625,000) is the average of four people, while Finance and Marketing have three each. If a division does not appear at all, AVERAGEIF returns #DIV/0!. For several conditions, such as division and city, use AVERAGEIFS.

Starting worksheet: Sheet1
Row	A	B	C	E	F
1	Name	Division	Base salary	Division	Average salary
2	Rina	Finance	8,500,000	Finance
3	Dimas	IT	12,000,000	IT
4	Ayu	Marketing	7,200,000	Marketing
5	Fajar	IT	10,500,000
6	Nadia	Finance	9,100,000
7	Bagas	Marketing	6,800,000
8	Putri	IT	14,250,000
9	Yoga	Marketing	7,900,000
10	Wulan	Finance	11,300,000
11	Sinta	IT	9,750,000
12
13
14

Fill in the requested cell, then press Check answer.

Validation result