If then formula with Single Select

[Devon_Mckenney]

I am trying to create a scoring system for this table. For example IF my Status is “Interested” then Field 18 equals 5. IF Status equals “Sent High Level” then Field 18 equals 2…IF Status equals “NO - PASS” then Field 18 equals 0.

[Matthew_Moran]

IF({Status}=“NO - PASS”,0,
IF({Status}=“Sent High Level”,2,
IF({Status}=“Interested”,5,"")))

However, this has an issue that I’ve not resolved yet. When the result is 0, the field shows as blank.

The following works, as far as displaying the 0 but I don’t know how that might impact anything you are doing with the field later.

IF({Status}=“NO - PASS”,“0”,
IF({Status}=“Sent High Level”,2,
IF({Status}=“Interested”,5,"")))

[Justin_Barrett]

Matthew_Moran:

When the result is 0, the field shows as blank.

I believe this is due to the fact that the final IF() function ends in an empty string, while the first two return numeric values. The returned value across all nested IF() functions should be consistent, otherwise Airtable might create unpredictable results (like this).

Because the final part of an IF() function is optional (which lets Airtable insert the appropriate blank equivalent if the condition is false), that empty string can be removed, which fixes the problem:

IF({Status}="NO - PASS",0,
IF({Status}="Sent High Level",2,
IF({Status}="Interested",5)))

However, there’s still more that can be optimized here. Because the output is simply being switched based on the contents of {Status}, the SWITCH() function would be a slightly better option:

SWITCH({Status}, "NO - PASS", 0, "Sent High Level", 2, "Interested", 5)

[Devon_Mckenney]

Thank you for all the help guys. much appreciated.

[Devon_Mckenney]

Hi Justin,

I have tried both formulas and am getting the same result where only sent high level populates the cell.

[Justin_Barrett]

Check the details of your single-select options. My guess is that there might be an extra space either at the beginning or end of the text of those other two options. Airtable auto-trims that stuff for display purposes, but leaves it intact when passing to formulas, which would result in a failed match.

[Justin_Barrett]

Airtable is also case sensitive, so you’ll need to change “NO - PASS” in the formula to “No - Pass” for that one to match.

[Devon_Mckenney]

Justin_Barrett:

IF({Status}=“NO - PASS”,0, IF({Status}=“Sent High Level”,2, IF({Status}=“Interested”,5)))

Wow! Thanks for the quick reply! Damn you extra space :slightly_smiling_face: