How to calculate NPS, worked through an export

To calculate NPS, sort every 0 to 10 answer into three groups: promoters (9 or 10), passives (7 or 8) and detractors (0 to 6). Divide the promoters and the detractors by the total number of responses to get two percentages, then subtract the detractor percentage from the promoter percentage. The result is a score between minus 100 and plus 100.

The formula

The Net Promoter System, which Fred Reichheld introduced in the Harvard Business Review in 2003, defines the score as:

NPS = percentage of promoters minus percentage of detractors

The question behind it is always the same: how likely are you to recommend us to a friend or colleague, answered on a scale from 0 to 10. The Net Promoter System’s own page explains the zero based scale: it avoids confusion over where the scale starts and gives enough room that people do not default to the top score for an ordinary experience.

A worked example: 40 answers from a bike shop

Spoke and Sprocket, a fictional Hobart bike shop, sent a short survey after each service and received 40 answers in a month. The table under this article lists them by score. Counting the groups:

• Promoters: 9 people scored 10 and 8 scored 9, so 17 promoters. 17 out of 40 is 42.5%.
• Passives: 7 scored 8 and 5 scored 7, so 12 passives, 30%.
• Detractors: 4 scored 6, 3 scored 5, and one each scored 4, 3, 2 and 1, so 11 detractors, 27.5%.

NPS = 42.5 minus 27.5 = 15.

Three checks catch most mistakes: the groups add up to the total (17 + 12 + 11 = 40), both percentages are divided by the same total including passives, and the score is a number, not a percentage.

Doing it in a spreadsheet

Once responses are exported to a CSV file, open it in Excel or Google Sheets and find the column for the recommend question. Suppose the 40 scores sit in B2 to B41:

• Promoter share: =COUNTIF(B2:B41,">=9")/COUNT(B2:B41)
• Detractor share: =COUNTIF(B2:B41,"<=6")/COUNT(B2:B41)
• NPS: =(COUNTIF(B2:B41,">=9")-COUNTIF(B2:B41,"<=6"))/COUNT(B2:B41)*100

Microsoft’s COUNTIF help page shows the criteria syntax, a comparison inside quotes. Two things trip people up. If the export brings scores in as text, COUNT returns zero; convert the column to numbers first. If the answer options were labelled with words, such as “10, extremely likely”, pull the number out before counting, or rename the options to plain numbers before the survey goes live.

Reading the result

A score on its own says little. Compare it with the same question, asked the same way, in earlier months, and with the reasons people gave. The NPS survey example on this site follows the score with “What is the main reason for your score?”, and that column is where the fixes are. The NPS benchmarks by industry post sets published medians beside your own, and the NPS survey best practices post covers when to send it.

Small samples move a lot. With 40 responses, one customer changing from a 6 to a 9 moves the score by 5 points. The margin of error page explains why, and the free margin of error calculator shows how wide the range is for your sample.

Quick checks with a calculator

If you only have the counts, the free NPS calculator takes the number of promoters, passives and detractors and returns the score, plus how many more promoters you would need to move it. The NPS against CSAT page explains when a satisfaction score is the better question.

Segments and trends

The overall score is the start, not the end. Split the export by anything you recorded that might matter: the type of service, the store, the month, new against returning customers. In the bike shop example, a split by service might show that full services score well and quick puncture repairs score poorly because people waited longer than promised. That is something the shop can fix; an overall 15 is not.

When you split, watch the size of each group. A segment of eight responses can swing by more than 20 points on a single answer, so mark small segments and avoid acting on them alone.

For a trend, calculate the score the same way each month and plot it with the number of responses beside each point. Look for changes that last for two or three periods rather than reacting to one month. And keep the reasons column next to the score: when the score moves, the reasons people give in that period usually say why.

Common calculation mistakes

Four errors account for most wrong scores. Dividing by promoters plus detractors instead of all responses leaves out the passives and inflates the result. Counting 7 and 8 as promoters, often because a five point habit leaks into the ten point scale, makes the score look far better than it is. Reporting the answer as a percentage, such as 15%, confuses readers, because NPS is a number on a scale from minus 100 to 100. And mixing responses from two different questions, for example a recommend question about the shop and one about a single mechanic, produces a score that measures neither. Check the groups add up to the total every time, and keep one question per score.

In the product

The recommend question is a single choice field with the options 0 to 10, and the form records the chosen option. The forms themselves do not calculate scores or totals. Responses export to CSV with a Submitted At column first and then one column per question, with each answer written as its option label, so plain numeric options export as the numbers you need. The CSV export page describes the file, and the guide to viewing and exporting responses shows where the download is.