Operations & Analytics
Student Course Registration: Capacity and Overflow
Count registration requests, remaining capacity and registrations above capacity; an actual waitlist requires separate status data.
Prevent negative seat counts when student demand overflows class capacity
Count registrations for each course with COUNTIF and compare the result with capacity. MAX(0, capacity - registrations) shows remaining capacity; MAX(0, registrations - capacity) shows how many requests exceed it. This arithmetic cannot tell which student is enrolled or waitlisted unless the source data includes a separate status or ordering rule.
Combining COUNTIF(Roster_Course, Section_Code) with two asymmetric MAX(0, ...) clamps separates available seats cleanly from waitlist demand: Open_Seats = MAX(0, Cap - COUNTIF($B$2:$B$7, D2)) floors at 0 once the room fills, while Waitlist_Count = MAX(0, COUNTIF($B$2:$B$7, D2) - Cap) activates only when registrations exceed capacity. Pair both with an IF status banner ("Open", "Full — Cap Reached", or "Waitlist Active (+N)").
| Course Section | Room Cap | Registered Roster COUNTIF | Open Seats / Capacity Status |
|---|---|---|---|
| FIN-201 (4 Roster Signups) | 3 Seats | 4 Students | 0 Open | Over Capacity (+1) |
| SQL-105 (2 Roster Signups) | 4 Seats | 2 Students | 2 Open | Open (2 Seats Left) |
| ML-310 (0 Roster Signups) | 5 Seats | 0 Students | 5 Open | Open (5 Seats Left) |
Clamped Remaining Open Seats: MAX(0, Seat_Cap - COUNTIF(Roster, Course))
Counts matching course enrollments in B2:B7 against section D2 and floors remaining seats at zero.
=IF(E2 < 1, "Invalid Seat Cap", MAX(0, E2 - COUNTIF($B$2:$B$7, D2)))Registration Count Compared With Capacity
Distinguishes available capacity, exact capacity, and the number of registrations above capacity; it does not identify enrolled or waitlisted students.
=IF(COUNTIF($B$2:$B$7, D2) > E2, "Over Capacity (+" & (COUNTIF($B$2:$B$7, D2) - E2) & ")", IF(COUNTIF($B$2:$B$7, D2) = E2, "Full — Cap Reached", "Open (" & (E2 - COUNTIF($B$2:$B$7, D2)) & " Left)"))Try the example
Use the original values or change them to test your understanding.