How to Calculate NPS: Formula, Worked Examples and Spreadsheet Steps
How to calculate Net Promoter Score step by step: the formula, worked examples, spreadsheet formulas, sample size cautions, and how to read the result against your reviews.
By Editorial Team
6 min read
To calculate NPS, sort your 0 to 10 survey responses into promoters (9 or 10), passives (7 or 8) and detractors (0 to 6). Divide the number of promoters by the total responses to get the promoter percentage, do the same for detractors, then subtract: NPS = % promoters minus % detractors. The result is a whole number between -100 and +100.
The math takes a minute. Getting a number you can trust takes a little more care, which is what the rest of this guide covers.
The NPS formula
Written out in full:
NPS = (promoters / total responses × 100) minus (detractors / total responses × 100)
Three rules keep the NPS calculation correct:
- Total responses includes everyone, passives too. Leaving passives out of the total is the most common error, and it inflates both percentages.
- Passives aren’t added or subtracted, but because they’re in the total, a lot of passives pulls the score toward zero.
- Report the result as a number, not a percentage. “Our NPS is 32,” not “32 percent.”
If you want background on what the score means before you calculate it, start with our guide on what net promoter score is.
Step by step
- Export your responses. One row per response, with the 0 to 10 score. Include the date, the location or team, and the comment if you have them.
- Remove anything that isn’t a valid answer. Blank scores, test submissions and obvious duplicates from the same customer for the same interaction.
- Count the total number of valid responses.
- Count promoters: responses of 9 or 10.
- Count detractors: responses of 0, 1, 2, 3, 4, 5 or 6.
- Count passives: responses of 7 or 8. You don’t need this for the formula, but promoters plus passives plus detractors should equal your total. If they don’t, something is miscounted.
- Calculate each percentage and subtract detractors from promoters.
- Round to a whole number for reporting.
Worked examples
These figures are illustrative.
Example 1: a typical month
A dental practice gets 80 valid responses: 44 promoters, 20 passives and 16 detractors.
- Promoters: 44 / 80 = 0.55, so 55
- Detractors: 16 / 80 = 0.20, so 20
- NPS: 55 minus 20 = 35
Example 2: fewer detractors, more passives
Suppose instead there are 30 promoters, 40 passives and 10 detractors out of 80.
- Promoters: 30 / 80 = 37.5
- Detractors: 10 / 80 = 12.5
- NPS: 37.5 minus 12.5 = 25
Fewer detractors than Example 1, but a lower score, because many customers were merely satisfied. That’s a useful insight: the business isn’t failing people, it’s just not giving them much to talk about.
Example 3: a negative score
A new location gets 40 responses: 8 promoters, 12 passives and 20 detractors.
- Promoters: 8 / 40 = 20
- Detractors: 20 / 40 = 50
- NPS: 20 minus 50 = -30
A negative score means more detractors than promoters. At a location level, it’s also a sign to check that location’s recent Google reviews, because unhappy customers there may already be posting.
Calculating NPS in a spreadsheet
If your scores are in column B, rows 2 to 201, these formulas work in both Google Sheets and Excel:
| What | Formula |
|---|---|
| Total responses | =COUNT(B2:B201) |
| Promoters | =COUNTIF(B2:B201,”>=9″) |
| Detractors | =COUNTIF(B2:B201,”<=6″) |
| Passives | =COUNTIFS(B2:B201,”>=7″,B2:B201,”<=8″) |
| NPS | =ROUND((COUNTIF(B2:B201,”>=9″)-COUNTIF(B2:B201,”<=6″))/COUNT(B2:B201)*100,0) |
COUNT only counts cells with numbers, so blank rows won’t distort the total. Check that nobody typed scores as text, such as “9 ” with a trailing space, because text won’t be counted.
To track NPS by location or month, add a column for each and use COUNTIFS with an extra condition, or a pivot table that counts responses in each group.
How many responses do you need?
There’s no single magic number, but small samples swing a lot. With 20 responses, each person moves the score by 5 points. With 200, each person moves it by half a point. So a jump from 30 to 45 on 20 responses may be noise, while the same jump on several hundred responses is more likely real.
Practical rules for smaller businesses:
- Combine periods when volumes are low. A rolling three-month score is steadier than a monthly one.
- Report the response count next to the score, every time. “NPS 40 (n = 35)” is honest; “NPS 40” alone invites overreaction.
- Look at the comments as well as the number. Five detractors all describing the same problem matter even when the score barely moves.
Not sure where to start?
Get a free audit of your search results and review profiles, with a prioritized fix list.
Get a free auditCommon calculation mistakes
- Leaving passives out of the total. With 50 promoters, 30 passives and 20 detractors, the correct NPS is 30. Excluding passives gives 50 / 70 minus 20 / 70, roughly 43, which is wrong.
- Averaging the scores. An average of 8.1 out of 10 isn’t an NPS. NPS is a difference of two percentages.
- Averaging location scores. If one location has 200 responses and another has 20, a simple average of their scores gives the small location too much weight. Recalculate from the combined raw counts instead.
- Using the wrong scale. Scores collected on 1 to 5 or 1 to 10 scales can’t be converted cleanly into NPS.
- Mixing survey methods. An email survey and an in-store tablet reach different people. Calculate them separately before comparing.
- Counting only responses you like. Removing “unfair” detractor responses breaks the metric. Remove only invalid entries.
Reading your score next to your reviews
A number is only useful if it leads to a decision. Once you’ve calculated NPS, put it beside two other things:
- The comments. Tag the reasons given by detractors and passives. The most common themes are your to-do list.
- Your public reviews. Compare the themes with what customers write on Google, Yelp or your industry sites. When a complaint appears in both places, it’s already affecting what prospective customers see.
Our guide on how to measure online reputation covers the public-side metrics, such as rating, review volume and response rate, that pair well with NPS. If you also track satisfaction, our guide to the CSAT score explains how that calculation differs.
A worked example: acting on the number
This is an illustrative scenario, not a real client.
A three-location auto repair shop calculates NPS each quarter from post-service texts. The combined score looks steady, but when the owner splits it by location, one shop is well below the other two. Its detractor comments mention the same issue: repairs costing more than the estimate without a phone call first.
The owner checks that location’s Google reviews and finds the same complaint in several recent ones. The fix is a rule that any change to an estimate needs a call and the customer’s approval before work continues. The shop replies to the relevant reviews explaining the new rule, and the next quarter’s score for that location improves, with fewer estimate complaints in both the survey and reviews.
The combined number alone would have hidden the problem. Splitting the calculation by location found it. For shops in this position, our auto repair reputation management page covers the review side.
Keep the survey honest so the number means something
Your NPS calculation is only as good as the responses behind it. Send the survey to every customer in the group you’re measuring, not a hand-picked list, and don’t coach people toward a 9 or 10. If your survey includes an invitation to leave a public review, show it to everyone, whatever their score. Showing it only to promoters is review gating, which Google’s policies prohibit. Our review management service sets up compliant review requests alongside survey programs.
Frequently asked questions
What is the formula for NPS?
NPS equals the percentage of promoters (scores of 9 or 10) minus the percentage of detractors (scores of 0 to 6). Both percentages are calculated from the total number of responses, including passives.
Can NPS be negative?
Yes. If you have more detractors than promoters, the score is negative. The lowest possible score is -100, where every respondent is a detractor.
Why are passives included in the NPS calculation?
Passives count toward the total number of responses, so they reduce both percentages. They aren’t added or subtracted directly, but a large share of passives pulls the score toward zero.
Is NPS a percentage?
No. It’s calculated from two percentages, but the result is reported as a whole number between -100 and +100, such as “an NPS of 35.”
How do I combine NPS across locations?
Add up the raw counts of promoters, detractors and total responses across all locations, then calculate NPS once from those totals. Averaging each location’s score gives small locations too much weight.
