FormulaSmith · Worked answers

Excel Student Grades Practice — File, Tasks & Answers

Version 2026-10-08.1 · 180 source rows
https://formulasmith.com/en/sample-data/student-grades/

Open this HTML file in your browser, then choose Print or Save as PDF. No sign-in, script or upload is needed. This file includes the worked answers.

Before you start

Use the unchanged default 180-row source file from the page above. Other file sizes require different ranges and totals.

Answers match the default 180-row file. Repeated names do not identify the same student; do not calculate a multi-subject student average from these rows.
Practice-only rules: A at 90, B at 80, C at 70, D at 60, otherwise F. Pass means score at least 60. These are exercise assumptions, not a school policy.
Attendance is a whole percentage: divide by 100 before applying Percentage format. Ranking is descending competition ranking: ties share a rank and later ranks are skipped.
Formulas use English function names and commas. Localized Excel may use different names and separators.

Confirm the data shape
Identify the score, subject and attendance columns; count the records.
```
=COUNT(E2:E181)
```
180 score records. E contains Score, C contains Subject, and F contains whole-percentage Attendance. Each Student ID identifies a record.
Calculate the overall mean
Average the score records and display two decimal places.
```
=AVERAGE(E2:E181)
```
65.81. Every record has equal weight; this is not a weighted student grade.
Summarize each subject
List each subject in an empty area and calculate its record count and mean.
```
=AVERAGEIF($C$2:$C$181,"Mathematics",$E$2:$E$181)
```
Repeat for each subject. COUNTIF gives record counts; the Subject answers sheet provides all expected counts and rounded means.
Apply grade bands
Add Grade in G using the stated exercise thresholds.
```
=IF(E2>=90,"A",IF(E2>=80,"B",IF(E2>=70,"C",IF(E2>=60,"D","F"))))
```
Fill down. Boundary scores 90, 80, 70 and 60 enter A, B, C and D respectively. The formula workbook keeps these thresholds on the Rules sheet.
Rank records with ties
Add descending Rank in H and keep the score range fixed.
```
=COUNTIF($E$2:$E$181,">"&E2)+1
```
The first record ranks 59. Equal scores receive equal ranks; the next rank skips the number of tied records.
Convert attendance correctly
Add Attendance fraction in I and apply Percentage format.
```
=F2/100
```
A stored value of 85 becomes 0.85, displayed as 85%. Formatting the original 85 directly would display 8500%.
Flag the practice pass rule
Add Result in J using the exercise pass threshold.
```
=IF(E2>=60,"Pass","Review")
```
105 records are Pass. A score exactly equal to 60 passes.
Reconcile band counts
Count the records in each grade band and compare with the answer sheet.
```
=COUNTIF(G2:G181,"A")
```
Repeat for A, B, C, D and F. Counts must sum to 180. Compare derived columns G:J record by record, not only the totals.

Matching these checks does not prove every row is correct. Use the reference workbook or CSV to compare the complete result. The tables below are fixed expected values, not interactive PivotTables.

Expected checks

Check	Expected
Score records	180
Mean score (2 decimals)	65.81
Minimum score	31
Maximum score	100
First record rank	59
Pass records (score >= 60)	105

Subject answers

Subject	Records	Average score (2 decimals)
Biology	36	66.83
Economics	35	61.86
History	37	67.86
Mathematics	36	66.53
Physics	36	65.81

Grade-band answers

Band	Records
A	30
B	29
C	23
D	23
F	75

Before you start

Confirm the data shape

Calculate the overall mean

Summarize each subject

Apply grade bands

Rank records with ties

Convert attendance correctly

Flag the practice pass rule

Reconcile band counts

Expected checks

Subject answers

Grade-band answers