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). These COUNTIFS bounds assume responses have already been validated as whole integers 1–5. Values such as 2.5 satisfy the numeric bounds but are not valid Likert choices and need to be rejected during data cleaning.
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.