Challenge Scenario
In academic testing and course grading, professors apply a standardized "curve bonus" to examination scores to adjust for difficult exam questions. However, to maintain grading integrity, curved scores must never exceed the maximum score cap of 100 points.
Using the MIN function to enforce the 100-point ceiling and nested IF formulas to convert scores into letter grades, instructors can automate class grade distributions seamlessly.
As a Teaching Assistant, you are grading 5 student tests with a 7-point curve bonus stored in cell $G$2:
- Curved Score (Column C): Add Raw Score to Curve Bonus (
$G$2), capped at 100 using =MIN(B2 + $G$2, 100).
- Letter Grade (Column D): Assign letter grades based on Curved Score:
- 90 or higher:
"A"
- 80 to 89:
"B"
- Below 80:
"C"
- Max Curved Score (Cell C8): Find the highest curved score in class using
MAX(C2:C6).
- Average Curved Score (Cell C9): Calculate class average curved score using
AVERAGE(C2:C6).
Your Sheet Layout
Understanding how the academic grading sheet is organized:
- Student Scores (Columns A & B): Shows Student Name and their raw test results (62 to 95 points).
- Curved Scores & Grades (Columns C & D): Calculates the capped score and assigns letter grades (A, B, C).
- Curve Settings (Columns F & G): The master grading parameter where the 7-point bonus is stored in cell
$G$2.
- Class Grade Summary (Rows 8 & 9): Computes the highest score achieved (100) and the overall class average (86.6).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B6 |
Student Name, Raw Score |
Test Scores |
Original test scores |
Read-only baseline student exam results. |
G2 |
Curve Bonus |
Curve Setting |
7 |
Master bonus points. Lock with dollar signs ($G$2). |
C2:C6 |
Curved Score |
Calculation |
=MIN(B2 + $G$2, 100) |
Adds 7 bonus points, capped at 100. |
D2:D6 |
Letter Grade |
Grade Tier |
=IF(C2 >= 90, "A", IF(C2 >= 80, "B", "C")) |
Converts score into letter grade A, B, or C. |
C8 |
MAX CURVED SCORE |
Class Summary |
=MAX(C2:C6) |
Finds highest curved test score (100). |
C9 |
AVERAGE CURVED SCORE |
Class Summary |
=AVERAGE(C2:C6) |
Finds average curved test score (86.6). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column C (C2:C6), calculate Curved Score capped at 100 using =MIN(B2+$G$2, 100).
2
In Column D (D2:D6), assign Letter Grade using =IF(C2>=90, "A", IF(C2>=80, "B", "C")).
3
In cell C8, find maximum curved score using MAX(C2:C6).
4
In cell C9, calculate average curved score using AVERAGE(C2:C6).
Solution & Formula Breakdown
Let's examine how score capping and letter grade tiers work step by step:
1. Applying Capped Curve with MIN (C2:C6)
=MIN(B2 + $G$2, 100)
Adding the locked bonus cell $G$2 (7 points) with a 100-point limit:
- Alice Wong (88):
88 + 7 = 95 → MIN(95, 100) = 95
- Brian Taylor (95):
95 + 7 = 102 → MIN(102, 100) = 100 (capped)
- Chloe Adams (74):
74 + 7 = 81 → MIN(81, 100) = 81
- Daniel Lee (62):
62 + 7 = 69 → MIN(69, 100) = 69
- Emma Watson (81):
81 + 7 = 88 → MIN(88, 100) = 88
2. Assigning Grades with Nested IF (D2:D6)
=IF(C2 >= 90, "A", IF(C2 >= 80, "B", "C"))
If score ≥ 90 assign "A"; else if score ≥ 80 assign "B"; otherwise assign "C".
3. Class Statistics Rollups (C8 & C9)
- Max Curved Score (Cell C8):
=MAX(C2:C6) returns 100.
- Average Curved Score (Cell C9):
=AVERAGE(C2:C6) returns 86.6.
Using MIN(value, ceiling) is the standard formula pattern for capping any calculation (such as maximum overtime hours, maximum bonuses, or test score limits)!