How to calculate NPS: a step-by-step guide with an example
How to calculate NPS from raw survey scores, with the spreadsheet formula, a worked example and three mistakes that skew the number.

You have a column of survey scores and someone wants the number today. This guide shows how to calculate NPS from those scores in a few minutes. It is for product managers who already have responses. At the end you will have your Net Promoter Score, a spreadsheet formula you can reuse, and a short list of checks before you share the figure.
- NPS comes from one question, answered on a scale from 0 to 10.
- Promoters score 9–10, passives 7–8, detractors 0–6.
- NPS = percentage of promoters minus percentage of detractors.
- The result sits between −100 and +100. It is not a percentage.
The six steps
Check the question
The standard wording is: how likely are you to recommend this product to a friend or colleague? Respondents answer from 0, not at all likely, to 10, extremely likely. If your survey used a different scale or a different question, the result is not an NPS, whatever the dashboard calls it.
Count every valid response
Put the scores in one column and count them. This count is your total. Include everyone who gave a score from 0 to 10. Leave out people who opened the survey and skipped the question.
Sort scores into three groups
Promoters gave 9 or 10. Passives gave 7 or 8. Detractors gave 0 to 6. Every response lands in exactly one group, so the three counts add up to your total.
Turn counts into percentages
Divide the number of promoters by the total, then the number of detractors by the total. Multiply each by 100. Both are divided by the same total, passives included.
Subtract
Promoter percentage minus detractor percentage. That is the score. Write it as a whole number, with a plus sign if it is positive, for example +20.
Note the context
Next to the number, record the date range, the moment you asked, and the number of responses. A bare score cannot be compared with anything later.

The three groups at a glance
| Group | Scores | What it does to NPS |
|---|---|---|
| Promoters | 9–10 | Raises the score |
| Passives | 7–8 | Adds to the total, so it pulls both percentages down |
| Detractors | 0–6 | Lowers the score |
Worked example
A survey returns 200 valid responses. 90 people scored 9 or 10, 60 scored 7 or 8, and 50 scored 0 to 6.
Promoters: 90 ÷ 200 = 45%.
Passives: 60 ÷ 200 = 30%.
Detractors: 50 ÷ 200 = 25%.
The three shares add up to 100%, which confirms the sort was clean.
45 − 25 = +20
The NPS is +20.
Want to check your own figures without a spreadsheet? Put your three counts into the free NPS calculator and compare the result with your hand calculation.
The spreadsheet formula
Say your scores sit in cells A2 to A201, one per row. This formula does steps 2 to 5 in one go:
=(COUNTIF(A2:A201,">=9")-COUNTIF(A2:A201,"<=6"))/COUNT(A2:A201)*100The first COUNTIF counts promoters. The second counts detractors. COUNT gives the total of numeric answers. For the 200 responses above, the formula returns 20. Change the range to match your data. If you export from a survey tool, check that scores are stored as numbers and not as text, or COUNT will skip them.
If you have not yet run the survey, Survey can collect the answers for you. Everything after that is the arithmetic above.
Why passives still count in the total
Passives never appear in the subtraction, so it is tempting to drop them. Do not. They are people who answered, and they dilute both groups. If a large share of your customers is lukewarm, you should not look as strong as a product whose customers are mostly delighted or upset.
Try it on the example. Remove the 60 passives and you have 140 responses. Promoters become 64.3% and detractors 35.7%, which gives +29 instead of +20. The product did not improve. The sum simply ignored a third of the people who answered.

Three mistakes that skew the number
Reporting it as a percentage
NPS is a difference between two percentages. It runs from −100 to +100. Write +20, not 20%. A percent sign makes people read it as 20% of customers, which is wrong.
Comparing different moments
A score asked right after onboarding measures a different feeling from one asked after a year of use. The same goes for in-app prompts against emailed surveys. Compare scores only when the question, audience and timing match.
Too few responses
With 20 responses, one person changing their answer moves the score by 5 points. With 200 it moves by half a point. There is no single safe minimum, but state the count beside the score so readers can judge for themselves.
Before you share it
- The question and the 0–10 scale match the standard NPS wording.
- The three group counts add up to the total.
- The score is written as a whole number between −100 and +100, with no percent sign.
- The date range, the moment you asked and the response count are stated next to it.
- Any comparison is against a score collected in the same way.
That is the whole method. Count, sort, convert to shares, subtract. The number is easy to produce. The harder part is keeping the conditions steady, so that next quarter you are comparing like with like.


