Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

How can I calculate the Net Promoter Score (NPS) with one formula in Airtable?

Topic Labels: Formulas Views
1933 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Kimnaike
4 - Data Explorer
4 - Data Explorer

I have automated the responses of our NPS survey into my Airtable Base.

I have one row showing me what the people have clicked on, on the scale of 0-10. And I have one row showing me what this means as "NPS" value, here is an example:

NPS Scale Vote | NPS 

10                        | 100

9                          | 100

4                          | - 100

7                           | 0

8                           | 0

Now I would like to add another row that shows me, whether someone is a Promoter, Detractor or Passive (Those who rate 9 or 10 are called “Promoters,” 7 or 8 are “Passives,” and 0 to 6 are “Detractors.”) And another row that calculates the final NPS in % for me:  Net Promoter Score = (Number of Promoter Scores/Total Number of Respondents) – (Number of Detractor Scores/Total Number of Respondents)*100

 

Example of how it would look then:

NPS Scale Vote | NPS      | Promoter/Detractor    | Overall NPS

10                        | 100        | Promoter                      | 20

9                          | 100        | Promoter

4                          | - 100      | Detractor

7                           | 0            | Passive

8                           | 0            | Passive

What formulas do I need to use to get these two rows?

 

Thank you already so much in advance!

 

2 Replies 2
Megan_Bandy
6 - Interface Innovator
6 - Interface Innovator

Hi Kimaike,

If you wan to do this in a single table you'd need to use a script for this. However you can do the bulk of the work by splitting it into 3 tables like so:


Megan_Bandy_0-1703091154508.png

Edit: Just to clarify - you can get the Promoter/Detractor field using an IF statement: 
IF(
OR({NPS Scale Vote}=10,{NPS Scale Vote}=9),'Promoter',
IF(
OR({NPS Scale Vote}=7,{NPS Scale Vote}=7),'Passive',
'Detractor'))

But you will struggle to do the percentages in one table without a script

Tharun_Pasam
4 - Data Explorer
4 - Data Explorer

Create NPS Surveys that get real answers - https://www.surveysort.com/nps-survey-prompts