Unexpected NaN result in Airtable formula using conditional statements

Other Questions Airtable
1

I’m having trouble with a formula in Airtable. I’m trying to assign numbers to strings from another cell using IF statements. Here’s what I’ve got:

IF(FIND('1',{Functional response}),-4,
IF(FIND('2',{Functional response}),-2,
IF(FIND('3',{Functional response}),0,
IF(FIND('4',{Functional response}),2,
IF(FIND('5',{Functional response}),4,'')))))

The formula seems to work, but when I try to use these columns elsewhere, I get NaN values. I tried wrapping the output values in INT(), but that messed everything up. What am I doing wrong? Is there a better way to do this?

2

I ran into a similar issue with Airtable formulas recently. The problem is likely that FIND() returns an error when the substring isn’t found, causing the whole formula to return NaN. A more robust approach is to use SEARCH() instead, which returns 0 if the substring isn’t found. Here’s a modified version that should work:

IF(SEARCH('1',{Functional response})>0,-4,
IF(SEARCH('2',{Functional response})>0,-2,
IF(SEARCH('3',{Functional response})>0,0,
IF(SEARCH('4',{Functional response})>0,2,
IF(SEARCH('5',{Functional response})>0,4,0)))))

This way, you’ll always get a number output, even if none of the conditions are met. You can adjust the default value (currently 0) at the end if needed. Also, make sure your {Functional response} field actually contains the numbers you’re looking for as strings.

3

Your approach is on the right track, but there’s a more efficient way to handle this in Airtable. Instead of nested IF statements, consider using the SWITCH function. It’s cleaner and easier to maintain. Here’s an example:

SWITCH(
  {Functional response},
  '1', -4,
  '2', -2,
  '3', 0,
  '4', 2,
  '5', 4,
  0
)

This assumes {Functional response} contains exact matches. If it might contain other text, you could combine it with LEFT() like this:

SWITCH(LEFT({Functional response}, 1), ‘1’, -4, ‘2’, -2, ‘3’, 0, ‘4’, 2, ‘5’, 4, 0)

The last 0 is the default value if no match is found, preventing NaN results. Adjust as needed for your use case.

4

hey there! ive had similar issues. try using VALUE() function to convert the string to a number before comparison. something like:

IF(VALUE({Functional response})=1,-4,
IF(VALUE({Functional response})=2,-2,
IF(VALUE({Functional response})=3,0,
IF(VALUE({Functional response})=4,2,
IF(VALUE({Functional response})=5,4,0)))))

this should avoid NaN errors. hope it helps!