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

[Kimnaike]

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!

[Megan_Bandy]

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:

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