NPS Excluding Blank Cells

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. 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.

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.

1 Like

Lifesaver!! Thank you!!

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.