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