Unexpected NaN output in Airtable formula with conditional logic

Other Questions Airtable
1

Hey everyone, I’m having trouble with a formula in Airtable. I’m trying to assign numbers to text values from another cell. The formula seems to work, but when I try to use these columns elsewhere, I get NaN (Not a Number) values. 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, '')))))

I thought wrapping the output values in INT() might help, but it just messed things up. Any ideas what I’m doing wrong or how to fix this? Thanks for any help!

2

Having dealt with Airtable formulas extensively, I can see why you’re encountering NaN issues. The problem likely stems from the FIND() function returning an error when it doesn’t locate the specified text. To resolve this, consider using the SEARCH() function instead, which returns 0 when the text isn’t found. Here’s a modified version of your formula 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 approach ensures a numerical output in all cases, preventing NaN errors in subsequent calculations. Remember to double-check your {Functional response} field for any unexpected values that might be causing issues.

3

hey tom, i’ve had similar probs w/ airtable. try using SEARCH() instead of FIND(). it’s less picky and won’t throw errors if it can’t find the text. also, make sure ur {Functional response} only has the values ur looking for. sometimes unexpected stuff sneaks in and messes everything up!

4

I’ve run into similar issues with Airtable formulas before, and it can be frustrating. From what I see, your formula might be returning an empty string (‘’) when none of the conditions are met, which could lead to NaN issues in other calculations.

Here’s a possible fix:

IFBLANK(
  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))))),
0)

This wraps the entire formula in IFBLANK() and returns 0 if the result is blank. It should prevent NaN errors when using these values in other formulas.

Also, make sure your {Functional response} field only contains the expected values. Any unexpected input could cause issues. Hope this helps!