Statistical
Net Promoter Score (NPS) & CSAT Top-2-Box formulas with COUNTIF
Calculate NPS including passive respondents and a 1–5 top-two-box CSAT percentage from a validated eight-response sample.
Why Passives (7–8) count in the NPS denominator even though they score 0 in the numerator!
NPS groups integer ratings 0–6 as detractors, 7–8 as passives and 9–10 as promoters. Subtract the detractor share from the promoter share and multiply by 100. Passives stay in the denominator. Report NPS in points, not as a percentage.
For the separate 1–5 CSAT question, this exercise defines top-two-box satisfaction as ratings 4 or 5 divided by all valid responses. These are descriptive sample results, not evidence of causation or a representative population estimate. Each column here must contain exactly eight numeric integer ratings in its valid range. Missing or malformed ratings return Invalid inputs; clean or explicitly redefine the response range before calculating a different sample.
| CX Metric (8 Survey Responses) | Scores | Formula | Score |
|---|---|---|---|
| Promoters (9–10) | 10, 9, 9, 10 (4 of 8) | COUNTIF(A2:A9, ">=9") / 8 | 50.0% |
| Passives (7–8) | 8, 7 (2 of 8) | Included in denominator (8)! | 25.0% |
| Detractors (0–6) | 6, 4 (2 of 8) | COUNTIF(A2:A9, "<=6") / 8 | 25.0% -> NPS = +25.0 |
| CSAT Top-2-Box (4–5 on 1–5) | [5,4,5,5,3,4,2,5] (6 of 8) | COUNTIF(B2:B9, ">=4") / 8 | 75.0% (0.75) |
Net Promoter Score (NPS on -100 to +100 scale)
Evaluates %Promoters (>=9) minus %Detractors (<=6) across all numeric responses.
=IF(COUNT(A2:A9)<>8,"Invalid inputs",IF(OR(COUNT(A2:A9)<>8,MIN(A2:A9)<0,MAX(A2:A9)>10,A2<>INT(A2),A3<>INT(A3),A4<>INT(A4),A5<>INT(A5),A6<>INT(A6),A7<>INT(A7),A8<>INT(A8),A9<>INT(A9)),"Invalid inputs",ROUND(((COUNTIF(A2:A9, ">=9") - COUNTIF(A2:A9, "<=6")) / COUNT(A2:A9)) * 100, 1)))CSAT Top-2-Box satisfaction percentage (4 or 5 out of 5)
Computes the proportion of satisfied customers scoring >= 4.
=IF(COUNT(B2:B9)<>8,"Invalid inputs",IF(OR(COUNT(B2:B9)<>8,MIN(B2:B9)<1,MAX(B2:B9)>5,B2<>INT(B2),B3<>INT(B3),B4<>INT(B4),B5<>INT(B5),B6<>INT(B6),B7<>INT(B7),B8<>INT(B8),B9<>INT(B9)),"Invalid inputs",ROUND(COUNTIF(B2:B9, ">=4") / COUNT(B2:B9), 4)))Try the example
Use the original values or change them to test your understanding.