In this post we will make ourselves a simple but effective Net Promoter Score calculator in Excel and Google Sheets 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 and Google Sheets
NPS is calculated by using the responses to the following customer survey question:
You will notice the response is an integer (whole number) between 0 and 10 – in the standard questions no fractional responses are allowed, e.g. 9.5.
We then define three groups of respondents:
- Promoters: are responses of 9 or 10
- Neutrals: are responses of 7 or 8; and
- Detractors are responses of 0 to 6.
Then the formula for NPS is:
It will make our spreadsheet easier to create if we apply a little algebra and re-write the equation as:
This score, although it looks like a percentage, should be referred to as a number.
You can see that NPS ranges all the way from:
- -100: where you have 100% Detractors to
- +100: where you have 100% Promoters
Building an NPS Calculator in Excel or Google Sheets
Now we have the basic equation we can calculate the score in a couple of different ways in Excel and Google Sheets.
This function allows you to count the number of times the cell contents meets a certain criteria.
In this case we’ll count the responses in each of the three categories we are interested in: Detractors, Neutrals and Promoters.
For Promoters we’re looking for 9’s or 10’s and so the function looks like this: =COUNTIF(R:R,”>=9″)
For Detractors we’re looking for 0’s up to 6’s so the formula is : =COUNTIF(R:R,”<=6″)
And lastly for Neutrals we’re looking for just 7’s and 8’s: =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 have the number of Promoters, Neutrals and Detractors you can add the equation for NPS to the spreadsheet.
This is how the calculation looks in the spreadsheet itself. Remember to include the “(” and “)” or the calculation will be wrong.
Of course, you don’t have to do these two calculations separately. You can combine them into the one formula. Here, again, R:R is the column where you have your scores.