Sort customer satisfaction scores into Promoter, Passive and Detractor with IFS
IFS checks its conditions in order and stops at the first one that holds, which is exactly the behavior three score bands need, and it reads as a flat list instead of a staircase of nested IFs.
Task:
You run customer experience reporting for a software company's support line. After every call, the customer rates the interaction from 0 to 10, and reporting sorts each response into an NPS band: 9 or 10 is a Promoter, 7 or 8 is a Passive, and 6 or below is a Detractor. In column C, classify each score from C2 to C7.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Satisfaction Score (0-10) | NPS Category |
| 2 | Dana Whitfield | 10 | |
| 3 | Marcus Oyelaran | 8 | |
| 4 | Priya Nair | 4 | |
| 5 | Tomas Ribeiro | 9 | |
| 6 | Elena Kowalski | 6 | |
| 7 | Sam Okafor | 7 |
What this exercise teachesMay contain the answer
IFS walks its conditions left to right and returns the result tied to the first one that's TRUE, then stops — it never checks the rest. Writing the Promoter test first is what keeps Tomas's 9 from being caught by a looser test like >=7, which would just as happily call it a Passive; because >=9 is asked about first, it wins before >=7 is ever evaluated. Sam's 7 fails the >=9 test, matches >=7, and stops there, while Elena's 6 fails both named tests and falls through to the TRUE at the end — which only ever gets reached once nothing more specific did, the same role a final ELSE plays at the bottom of any condition stack.