How to Calculate NPS Score in Excel (2 Methods)

NPS stands for Net Promoter Score. It usually shows us the ratio of the difference between the total number of the most satisfactory data and the unsatisfactory data with respect to the total number of data. We can get such a result using Microsoft Excel easily. In this article, we are going to demonstrate two handy methods to calculate the NPS score in Excel. If you are also curious about it, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


Overview of NPS Score

NPS stands for Net Promoter Score. It usually shows us the ratio of the difference between the total number of the most satisfactory data and the unsatisfactory data with respect to the total number of data. The NPS has three particulars.

  • Promoters: Entities that are extremely satisfied and content with the goods or services they are offering are considered to be promoters. Promoters are ready to speak out on your behalf and encourage others to talk favorably about your entities.
  • Passives: The average of your entities, passives sit between promoters and dissenters. As their name implies, they are neutral or passive individuals who don’t exhibit excessive excitement or negatively affect others.
  • Detractors: Those organizations that give you poor ratings are considered detractors. All people are dissatisfied and unhappy with this data. Detractors will never further endorse you.

The mathematical expression of the NPS score is:

Moreover, you can also re-write the expression as shown below:


2 Quick Ways to Calculate NPS Score in Excel

To demonstrate the approach, we consider the mathematics examination marks of 50 students in column B. Moreover, we mention our NPS score criteria in column C. We consider 80-100 as the Promoters’ range, 50-70 as the Passive or Neutral range, and 0-40 as the Detractors’ range.


1. Using Conventional Formula

In this approach, we will use the conventional formula to calculate the NPS score in Excel. The steps of this process are given below:

📌 Steps:

  • First of all, we have to calculate the No. of Students per Criteria. For that, select cell D5.
  • Now, write down the following formula into the cell using the COUNTIF function.

=COUNTIF(B:B,"=0")

  • Press Enter.

Using Conventional Formula to Calculate NPS Score

  • You will get the number of students belonging to this criteria.
  • Similarly, write down a similar type of formula for each criterion.

  • Then, in cell D16, we are going to write down a formula using the SUM function to get the total number of students.

=SUM(D5:D15)

  • Press Enter.
  • You can see that the sum of all the criteria results is 50. Hence, we can say that we have categorized all of the students.

Using Conventional Formula to Calculate NPS Score

  • After that, we will estimate the Percentage of Students per Criteria. Write down the formula into cell E5. Make sure that you input the Absolute Cell Reference for cell D16.

=D5/$D$16

  • Similarly, press Enter.

Using Conventional Formula to Calculate NPS Score

  • Next, drag on the Fill Handle icon to copy the formula up to cell E15.

  • Additionally, you can also apply the SUM function in cell E16 to get the summation of this column.

=SUM(E5:E15)

  • Press the Enter.

Using Conventional Formula to Calculate NPS Score

  • Now, we are going to evaluate the three particulars of the NPS score. To calculate the value of the Promoters, write down the following formula into cell F5.

=COUNTIF(B:B,">=80")

  • Press the Enter key.

Using Conventional Formula to Calculate NPS Score

  • Then, to calculate the value of Detractors, write down the following formula into cell H5 and press Enter.

=COUNTIF(B:B,"<=40")

Using Conventional Formula to Calculate NPS Score

  • The number of Neutral is the deduction result of the sum of the other two particulars from the total number of students. Write down the following formula into cell G5 to get the value.

=D16-F5-H5

  • Press Enter.

Using Conventional Formula to Calculate NPS Score

  • Moreover, we can calculate their percentage with respect to the total number of students.
  • For that, write down the following formula in cell F6. Ensure that you input the Absolute Cell Reference for cell D16.

=F5/$D$16

  • Press the Enter.

Using Conventional Formula to Calculate NPS Score

  • Then, drag the Fill Handle icon to your right to copy the formula up to cell H6.

  • Finally, to calculate the NPS Score, write down the following formula into cell I5.

=((F5-H5)/D16)*100

  • Press Enter for the last time.

Using Conventional Formula to Calculate NPS Score

  • You will get the value of the NPS score.

Thus, we can say that all of our formulas worked perfectly, and we were able to calculate the NPS score in Excel.

💬 Things You Should Know

You can calculate the NPS from the percentage value of the Promoters and Detractors, which are shown in cells F6 and H6. The deduction value of both cells is numerically equal to the NPS score.

Read More: How to Calculate Total Score in Excel (4 Easy Ways)


Similar Readings


2. Combining COUNTIF and COUNT Functions

In this process, we are going to use the COUNTIF and COUNT functions to calculate the NPS score in Excel. The steps of this method are given as follows:

📌 Steps:

  • First, select cell D5.

  • Now, write down the following formula into the cell.

=((COUNTIF(B:B,">=80")-COUNTIF(B:B,"<=40"))/COUNT(B:B))*100

  • Press Enter.

Combining COUNTIF and COUNT Functions to Calculate NPS Score

  • You will get the value of the NPS score.

Finally, we can say that our formula worked successfully, and we were able to calculate the NPS score in Excel.

🔎 Breakdown of the Formula

We are breaking down the formula for cell D5.

👉 COUNTIF(B:B,”>=80″): The COUNTIF function will count the entities that are greater than or equal to 80 in column B. For this cell, the value is 22.

👉 COUNTIF(B:B,”<=40″): Here, the COUNTIF function will count the entities that are less than or equal to 40 in column B. For this cell, the value is 4.

👉 COUNT(B:B): The COUNT function will count the total number of entities. Here, the value is 50.

👉 ((COUNTIF(B:B,”>=80″)-COUNTIF(B:B,”<=40″))/COUNT(B:B))*100: Finally, this formula first deducts the 8 from 22, then divides the result with the result of the COUNT function, and multiples the result with 100. Finally, it will show the NPS score. Here, the result is 36.

Read More: How to Create a Scoring System in Excel (With Easy Steps)


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to calculate the NPS score in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo