Case studies › ROUND, IFS141 / 144

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

MediumFreeROUNDIFS

Case study: report card scores and grades

Melinda, homeroom teacher of class 7B at a junior high school in Bogor, is filling in report cards. The final score is a weighted average of assignments, midterm and final exam, rounded to a whole number, and then given a grade from A to D.

Task

Fill in two columns:

E2:E8 Final score: Assignment × B10 + Midterm × C10 + Final × D10, rounded to a whole number with ROUND. Lock the weights row with $.
F2:F8 Grade with IFS from the final score: A if ≥ 90, B if ≥ 80, C if ≥ 70, otherwise D.

Cells to fillE2:E8F2:F8

Required functionsROUNDIFS

Expected result

Loading the expected result…

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

Hint 1
The final score is the sum of each score times its weight. The weights are in row 10, so lock that row: B$10 or $B$10.
Hint 2
IFS checks the conditions in order and stops at the first one that is true. Start from the highest threshold (90), then 80, then 70.
Hint 3
For "otherwise", use TRUE as the last condition: =IFS(E2>=90, "A", …, TRUE, "D").

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

E2:E8

=ROUND(B2*$B$10+C2*$C$10+D2*$D$10,0)

Write it in E2, then fill down to E8.

F2:F8

=IFS(E2>=90,"A",E2>=80,"B",E2>=70,"C",TRUE,"D")

Write it in F2, then fill down to F8.

Explanation

=ROUND(B2*$B$10+C2*$C$10+D2*$D$10,0), then =IFS(E2>=90,"A",E2>=80,"B",E2>=70,"C",TRUE,"D"). The grade uses the rounded score, so Umi, with a raw 79.6, gets 80 and a B. The order of the IFS conditions matters: if E2>=70 comes first, every score above 70 stops at C. Tip: SUMPRODUCT(B2:D2,$B$10:$D$10) is a shorter way to write a weighted average.

Starting worksheet: Sheet1
Row	A	B	C	D	E	F
1	Name	Assignment	UTS	UAS	Final score	Grade
2	Raka	85	78	90
3	Salsa	92	94	91
4	Tegar	70	65	72
5	Umi	80	76	82
6	Vina	88	90	93
7	Wahyu	75	70	68
8	Zahra	78	74	80
9
10	Weight	30%	30%	40%
11
12
13
14

Fill in the requested cells, then press Check answer.

Validation result