In this post we will calculate Net Promoter Score in Excel using the COUNTIF function. Before we do that let’s have a quick review of the formula for NPS.
Calculating Net Promoter Score in Excel
NPS is calculated using the responses to the following question:
How likely are your to recommend Company A to a friend to colleague, where 0 is very unlikely and 10 is very likely.
The response is an integer (whole number) between 0 and 10.
We then define three groups of respondents:
- Promoters: as responses of 9 or 10
- Neutrals: as responses of 7 or 8; and
- Detractors as responses of 0 to 6.
Then the formula for NPS is:
Net Promoter Score = Percentage of Promoters – Percentage of Detractors
Another, more simple, way to write this is:
This score, although it looks like a percentage, should be referred to as a number.
NPS ranges from -100 (all Detractors) to +100 (all Promoters).
Building an NPS Calculator in Excel
Now we have the basic equation we can calculate the score in a couple of different ways in Excel.
The easiest way to calculate it is to use the Excel COUNTIF function.
This function allows you to count the number of times that the cell contents meets a certain criteria. In this case we’ll count the responses in each of the three catgories: Detractors, Neutrals and Promoters.
Here is how the function looks for Promoters: =COUNTIF(R:R,”>=9″).
It is similar for Detractors: =COUNTIF(R:R,”<=6″)
And lastly for Neutrals: =COUNTIF(R:R,”=7″) +COUNTIF(R:R,”=8″)
In this case
R:R is the whole column of responses that you have received for your survey. The nice thing about using this function is that you can just copy and paste your scores into the column and Excel will count them for you.
Now that you can count the numbers of Promoters, Neutrals and Detractors you add the equation for NPS to the spreadsheet. See below for the equation. Remember to include the “(” and “)” or the calculation will be wrong.