# Excel Student Grades Practice — File, Tasks & Answers

Version: 2026-10-08.1

https://formulasmith.com/en/sample-data/student-grades/

Use a long-format table of independent score records to practise summaries, illustrative grade bands and ranking. Compare every derived value and each subject summary with the answer file.

## Before you start
- 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.

## 1. Confirm the data shape

Identify the score, subject and attendance columns; count the records.

```excel
=COUNT(E2:E181)
```

180 score records. E contains Score, C contains Subject, and F contains whole-percentage Attendance. Each Student ID identifies a record.

## 2. Calculate the overall mean

Average the score records and display two decimal places.

```excel
=AVERAGE(E2:E181)
```

65.81. Every record has equal weight; this is not a weighted student grade.

## 3. Summarize each subject

List each subject in an empty area and calculate its record count and mean.

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

## 4. Apply grade bands

Add Grade in G using the stated exercise thresholds.

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

## 5. Rank records with ties

Add descending Rank in H and keep the score range fixed.

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

## 6. Convert attendance correctly

Add Attendance fraction in I and apply Percentage format.

```excel
=F2/100
```

A stored value of 85 becomes 0.85, displayed as 85%. Formatting the original 85 directly would display 8500%.

## 7. Flag the practice pass rule

Add Result in J using the exercise pass threshold.

```excel
=IF(E2>=60,"Pass","Review")
```

105 records are Pass. A score exactly equal to 60 passes.

## 8. Reconcile band counts

Count the records in each grade band and compare with the answer sheet.

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

## Expected checks
- 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

## Teaching and reuse
Free for practice, teaching, demos and commercial use. No registration. Attribution is welcome, not required. These records are synthetic. Share the source page so learners can obtain the matching file version.