Paying every teacher a flat rate per class ignores the difference between five students and twenty-five. Paying pure per-student rewards a packed 6am class and punishes a teacher who covers a legitimately small 9pm restorative slot. A tiered structure — a floor for a thin class, a standard rate for a normal one, and a per-student bonus once attendance clears a threshold — balances both, and IFS lets you write all three tiers in one readable formula.
5 students pays the $30 floor; 12 students pays the flat $40; 20 students pays $40 plus $5 for each of the 5 students over 15, or $65.
The example
One teacher, five classes across a week. The floor protects a thin restorative class; the bonus rewards the packed Saturday flow.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Class | Day | Students | Pay |
| 2 | 9pm Restorative | Mon | 5 | $30 |
| 3 | Noon Flow | Tue | 12 | $40 |
| 4 | Sat Community | Sat | 20 | $65 |
| 5 | 6am Power | Wed | 15 | $40 |
| 6 | Thu Evening | Thu | 8 | $40 |
The formula
IFS reads left to right and stops at the first TRUE condition, so tier order matters:
How it works
Three conditions, checked in order:
C2<8catches a thin class first. Under 8 students pays the $30 floor regardless of how far under — even 1 student pays $30, protecting the teacher for showing up.C2<=15is checked only if the first test failed (so C2 is already ≥8). Between 8 and 15 students pays the flat $40 standard rate.TRUEis the catch-all for everything else — more than 15 students. It pays the $40 base plus $5 for every student above 15.- Order the tiers from most-restrictive to least; IFS stops at the first TRUE, so a wider condition placed first would swallow the narrower ones after it.
Add a fourth tier with a cap (say, no bonus above 30 students) by inserting one more condition before TRUE.
Try it: interactive demo
Enter how many students attended the class.
Variations
Use nested IF instead of IFS on older Excel
IFS needs Excel 2019 or Microsoft 365. On earlier versions, nest nested IFs to the same effect.
Different bonus rate for a premium class
Look up the per-student bonus rate by class type instead of hard-coding $5, so a specialty workshop can pay a richer bonus than a regular flow class.
Pitfalls & errors
IFS returns #N/A if none of the conditions are TRUE. Always end with a TRUE,... catch-all tier, not a specific number, or a student count you did not anticipate will error the whole payroll sheet.
Check your tier boundaries for gaps or overlaps. C2<8 then C2<=15 covers 8 through 15 cleanly with no gap at exactly 8 — test the boundary values themselves, not just the middle of each tier.
Put the $30, $40, $5 and 15 as named cells on a rates tab instead of hard-coding them in the formula. A pay-rate change then means editing one cell, not finding every formula that mentions 40.
Practice workbook
Frequently asked questions
Why not just pay per student with no floor?
Can I use IFS with text results instead of numbers?
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