Skip to main content

Hi! I’m going crazy trying to come up with an NPS formula. I am planning to have a formula return -100, 0, or 100 depending on the value so I can then average that column on dashboards.


I can get my NPS just fine, but I can’t figure out how to exclude blank cells in an IF statement.


In words, I want the following:

If {Program NPS Value} is between 0 (inclusive) and 6 (inclusive), -100

If {Program NPS Value} is 7 or 8, 0

If {Program NPS Value} is 9 or 10, 100

If {Program NPS Value} is BLANK, “”


There’s gotta be a way to factor out these blank cells.


I’m getting -100s for all my blank cells with this formula:

IF({NPS Value} < 7, -100, IF({NPS Value} > 8,100,0))


I appreciate your help!!

Welcome to the community, @Amy_Herman!


Ha, please don’t get me started on the madness of Airtable’s numerical evaluations. :crazy_face: Airtable is the only programming language I’ve ever worked with that evaluates a completely empty cell as a valid number to be evaluated! It’s very odd. :man_shrugging:


In any case, here’s what you would need to use for your formula:


IF(

{NPS Value}=BLANK(),BLANK(),

IF({NPS Value} < 7,-100,

IF({NPS Value} > 8,100,

0)))

Hope this helps! Let me know if it works for you! 🙂


And, if this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. 🙂

Lifesaver!! Thank you!!

Reply


Terms & Conditions