Net Promoter Score (NPS) can be a helpful snapshot of satisfaction and to learn more about consumers who are detractors, promoters, and passives. If you are collecting NPS data from multiple sources, you may be wondering how to calculate this score manually.
If you’re not familiar, NPS is a score that measures satisfaction. It’s based on one question you may see often on customer feedback surveys, asked on phone interviews, or even see on mystery shopping reports.
The question is quite simple: “On a scale of 0 to 10, how likely are you to recommend this company’s product or service to a friend or colleague?”
There are two data points to look at – the actual score given and the NPS score.
The actual scores, of course, range from 0 to 10, with 10 being the most satisfied. This is a helpful data point to look at for determining which customers, or how many customers, are detractors, promoters, or passive. This is how each category is defined:
Detractors are those giving ratings 6 and below. They are not particularly thrilled by the product or the service. They, with all likelihood, won’t purchase again from the company, could potentially damage the company’s reputation through negative word of mouth.
Passives are those giving ratings of 7 or 8. They are somewhat satisfied but could easily switch to a competitor’s offering if given the opportunity. They probably wouldn’t spread any negative word-of-mouth, but are not enthusiastic enough about your products or services to actually promote them.
Promoters are those giving ratings of 9 or 10. They love the company’s products and services. They are the repeat buyers, are the enthusiastic evangelist who recommends the company products and services to other potential buyers.
The second data point is the actual NPS score, which can range from -100 to 100. This is calculated by subtracting the detractors from the promoters – sounds easy, right? But what happens when you are collecting NPS data from multiple sources and end up with a spreadsheet of data? It could take all day to try to calculate manually. There is an easy formula to calculate this in Excel, and it only takes a few minutes.
Once you have your column of NPS data, you’ll want to add a formula to calculate your score.
The formula is: =100*(COUNTIF(BU2:BU27,”>8″) COUNTIF(BU2:BU27,”<7″))/COUNT(BU2:BU27)
In the example above, it assumes that your NPS scores are located in column B, rows 2 through 27. To make this formula work for you, all you need to do is change out BU2 and BU27 to the column and row numbers that contain your data.
Let’s take a look at a quick example of how the formula would change based on your data. If this is what your spreadsheet looks like, with the last column (E) being the data for NPS, which goes from row 2 through row 43:
Then your formula would look like this:
The formula is: =100*(COUNTIF(E2:E43,”>8″)-COUNTIF(E2:E27,”<7″))/COUNT(E2:E27)
All it took was a quick replace of BU with E.
NPS is a great tool to get a quick snapshot of satisfaction levels; it’s no longer a chore to calculate it manually across multiple touch points, so make sure you’re asking that very important question at every opportunity possible!
