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. :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! :slight_smile:

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

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.