How to calculate NPS in Excel: formula and worked example

The NPS formula

NPS = % promoters − % detractors

• Promoters answer 9 or 10. They are the customers most likely to buy again and recommend you.
• Passives answer 7 or 8. They are satisfied but not enthusiastic.
• Detractors answer 0 to 6. They are unhappy enough to warn others off.

Passives count in the total number of answers, but in neither group, so they pull the score towards zero. The result is written as a whole number, not a percentage: 46% − 24% gives an NPS of 22, or +22, never "22%". The scale runs from −100, when every answer is a detractor, to +100, when every answer is a promoter. The method was introduced by Fred Reichheld of Bain & Company in 2003.

Counting the three groups in Excel

The 50 answers sit in A2:E11, five per row. In a real survey export they are usually in one column, and the formulas work the same way on any range.

• Promoters: =COUNTIF(A2:E11,">=9") returns 23.
• Passives: =COUNTIFS(A2:E11,">=7",A2:E11,"<=8") returns 15. Two conditions on the same range give a "between" count.
• Detractors: =COUNTIF(A2:E11,"<=6") returns 12.
• Valid answers: =COUNT(A2:E11) returns 50. COUNT ignores blanks and text such as "n/a", so skipped questions drop out of the base automatically.

The last row of the example is a check: promoters + passives + detractors − n must be 0. If it is not, the range contains values outside 0–10, such as a 99 code for "don't know".

Worked example: NPS from 50 survey answers

The 50 answers to the 0–10 recommend question are the yellow inputs in A2:E11. Every result below them is a formula.

Worked example: NPS from 50 survey answers

	A	B	C	D	E
1	Score	Score	Score	Score	Score
2	8	7	9	7	9
3	2	9	9	8	8
4	6	6	8	10	10
5	4	9	9	9	5
6	3	9	0	10	6
7	9	7	10	6	9
8	7	9	5	8	8
9	10	10	9	7	5
10	7	8	10	7	10
11	10	6	8	10	9
12
13	Valid responses (n)				50
14	Promoters (9–10)				23
15	Passives (7–8)				15
16	Detractors (0–6)				12
17	% promoters				46%
18	% detractors				24%
19	Net Promoter Score				22
20	Margin of error, 95% (± points)				22.4
21	Mean score (not the NPS)				7.7
22	Check: groups add up to n (must be 0)				0

The 50 answers are fictional. The margin of error uses the normal approximation, which is rough for very small samples.

From the counts to the score

Divide each group by all valid answers: 23 ÷ 50 = 46% promoters and 12 ÷ 50 = 24% detractors. The NPS is (46% − 24%) × 100 = 22. In one cell:

=(COUNTIF(A2:E11,">=9")-COUNTIF(A2:E11,"<=6"))/COUNT(A2:E11)*100

Compare it with the mean score of 7.7. The mean suggests fairly satisfied customers, while the NPS points out that almost a quarter of them are detractors. NPS deliberately ignores the middle and treats a 6 like a 0, which is why it reacts more strongly to changes than a mean. With 50 answers, every single respondent is worth 2 points of NPS.

Margin of error: how precise is your NPS?

An NPS from a sample is an estimate. With P and D as the promoter and detractor shares, the 95% margin of error is approximately:

1.96 × √((P + D − (P − D)²) ÷ n) × 100

In Excel: =NORM.S.INV(0.975)*SQRT((E17+E18-(E17-E18)^2)/E13)*100. With 50 answers it is ±22.4 points, so the true score could be anywhere from about 0 to +44. With the same mix of answers, 400 responses bring the margin down to about ±7.9 points and 1,000 responses to about ±5.0. A change of a few points from one month to the next is usually noise, not news.

The margin of error only covers random sampling. If unhappy customers simply do not answer, no formula fixes that, so look at the response rate as well.

NPS by segment with COUNTIFS

With the segment in column B and the score in column C, add the segment as a second condition:

=(COUNTIFS(B:B,"Business",C:C,">=9")-COUNTIFS(B:B,"Business",C:C,"<=6"))/COUNTIFS(B:B,"Business",C:C,">=0")*100

The last COUNTIFS counts the valid answers in the segment, because ">=0" skips blanks. Put the segment names in a column and refer to the cell instead of typing "Business", and one formula fills a whole summary table, the same technique as in our SUMIFS examples. Report n next to every segment: small segments have much wider margins of error.

What is a good NPS?

Any score above 0 means you have more promoters than detractors. Beyond that, published benchmarks vary widely by industry, country and survey method. A relationship survey sent to all customers once a year scores differently from a survey sent right after a support call. The most useful comparisons are your own trend, with the same question, timing and channel, and competitors measured the same way.

Common mistakes

• Counting a 6 as a passive. Detractors are 0 to 6, passives only 7 and 8. Check the cut-offs: "<=6" and "<=8".
• Dividing by promoters + detractors only. Passives belong in the base. Leaving them out exaggerates the score in both directions.
• Reporting the NPS as a percentage. 46% − 24% is an NPS of 22, not 22%.
• Using the mean score as the NPS. A mean of 7.7 and an NPS of 22 measure different things. Calculate both if you like, but do not mix them up.
• Averaging the NPS of several segments. A simple average ignores segment sizes. Recalculate from the pooled counts of all answers.
• Reading trends from small samples. With 50 answers the margin of error is about ±22 points. Show n and the margin next to every score.