Statistical & Mathematical Formulas
Summarize Survey Results by Group: Top-2-Box % & Net Favorability (COUNTIFS)
Aggregate 1-to-5 CSAT or employee-pulse survey responses by department into Top-2-Box satisfaction percentage (scores 4 & 5) and Net Favorability (% Top-2 minus % Bottom-2) using COUNTIFS.
Why UX and market researchers report Top-2-Box % instead of raw Likert averages!
Reporting that Support scored a "3.75 average" on a 1–5 scale hides whether every respondent answered a lukewarm 4—or half gave rave 5s while half gave angry 2s! Customer-experience and employee-engagement researchers summarize grouped survey data via Top-2-Box % (the share of valid respondents choosing 4 "Satisfied" or 5 "Very Satisfied") and Net Favorability (% choosing 4–5 minus % choosing 1–2, on a -100 to +100 scale akin to NPS)!
Pairing COUNTIFS(Dept_Range, Target_Dept, Score_Range, ">=4") divided by valid department respondent count COUNTIFS(Dept_Range, Target_Dept, Score_Range, ">=1") works across every spreadsheet version without requiring Excel 365-only GROUPBY()! Across 6 Support responses [5, 4, 5, 2, 4, 1] (four 4–5s and two 1–2s), Top-2-Box = 66.7% and Net Favorability = 66.7% - 33.3% = +33.3!
| Department Cohort | Valid Responses (1–5) | Top-2-Box % (Scores 4–5) | Net Favorability (%4–5 minus %1–2) |
|---|---|---|---|
| Support ([5, 4, 5, 2, 4, 1]) | 6 responses | 66.7% (4 of 6) | +33.3 (66.7% - 33.3%) |
| Onboarding ([5, 5, 4, 3]) | 4 responses | 75.0% (3 of 4) | +75.0 (75.0% - 0.0%) |
| Unpolled Department (n = 0) | 0 responses | "No Responses" | Zero-Denominator Guard |
Department Top-2-Box Satisfaction Percentage (Scores 4 & 5)
Divides department scores >= 4 by total valid numeric scores (1–5) for the target department in E2.
=IF(COUNTIFS(A2:A11, E2, B2:B11, ">=1", B2:B11, "<=5") = 0, "No Responses", ROUND(100 * COUNTIFS(A2:A11, E2, B2:B11, ">=4", B2:B11, "<=5") / COUNTIFS(A2:A11, E2, B2:B11, ">=1", B2:B11, "<=5"), 1))Department Net Favorability Spread (% Top-2 Minus % Bottom-2)
Subtracts Bottom-2-Box count (scores 1–2) from Top-2-Box count (scores 4–5) as a percentage of valid department responses.
=IF(COUNTIFS(A2:A11, E2, B2:B11, ">=1", B2:B11, "<=5") = 0, "No Responses", ROUND(100 * (COUNTIFS(A2:A11, E2, B2:B11, ">=4", B2:B11, "<=5") - COUNTIFS(A2:A11, E2, B2:B11, ">=1", B2:B11, "<=2")) / COUNTIFS(A2:A11, E2, B2:B11, ">=1", B2:B11, "<=5"), 1))Try the example
Use the original values or change them to test your understanding.