Promotion rules are almost always two rules: enough classes and enough time. A student who crams 52 classes into three months has not waited; a student who has waited six months but attended 33 has not trained. AND is the function that refuses to let either one pass alone.
46 classes and five months clears a 40-class, four-month requirement. 52 classes in three months does not.
The example
Three students up for the same rank, measured against the same class and time-in-grade minimums.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Classes | Months | Req classes | Req months | Status |
| 2 | Ava | 46 | 5 | 40 | 4 | Eligible |
| 3 | Ben | 33 | 6 | 40 | 4 | Not yet |
| 4 | Cruz | 52 | 3 | 40 | 4 | Not yet |
The formula
One AND inside one IF:
How it works
Why this shape and not two columns:
B2>=D2is the class-attendance test. Classes since the last promotion, not classes ever — keep the counter resetting at each rank.C2>=E2is time-in-grade, the rule that stops a student from buying rank with attendance.AND(...)returns TRUE only when both are TRUE. Nesting twoIFs would do the same thing with twice the parentheses and half the readability.- The requirement columns are per rank, so a lookup against a rank table is the natural next step once you have more than one belt on the sheet.
Keep this as a screening list, not a decision. The instructor still promotes; the sheet only says who is allowed to be considered.
Try it: interactive demo
Enter the student's classes and months since the last promotion, and the requirement for the rank.
Variations
Say what is missing
Replace the generic message with the specific shortfall.
Pull requirements from a rank table
Look the minimums up by belt so one formula serves every rank.
Pitfalls & errors
Counting total classes instead of classes since the last promotion makes every senior student permanently eligible. Reset the counter at each rank.
Add a third condition for curriculum sign-off with a simple AND(...,F2="Yes"). AND takes as many arguments as your rules require.
Using OR here is the classic mistake and it quietly promotes everyone. It returns Eligible the moment either test passes.
Practice workbook
Frequently asked questions
What are typical requirements?
How do I handle a student who trains twice a day?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class