Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 30, 2020 07:17 PM
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!!
Solved! Go to Solution.
Sep 30, 2020 07:52 PM
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! :slightly_smiling_face:
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. :slightly_smiling_face:
Sep 30, 2020 07:52 PM
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! :slightly_smiling_face:
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. :slightly_smiling_face:
Sep 30, 2020 08:48 PM
Lifesaver!! Thank you!!