**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.

## 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:

## How to Calculate NPS Score in Excel: 2 Quick Ways

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**.

- 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.

- 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**.

- 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**.

- 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.

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

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

- 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**.

- 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**.

- 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.

- 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.

### 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 in the cell.

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

- Press
**Enter**.

- 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**.

**Download Practice Workbook**

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

## 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.

**<< Go Back to Scoring | Formula List | Learn Excel**