# NPS Excluding Blank Cells

Topic Labels: Formulas
Solved
1107 2
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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

1 Solution

Accepted Solutions
18 - Pluto

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:

2 Replies 2
18 - Pluto

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:

6 - Interface Innovator

Lifesaver!! Thank you!!