I’m having trouble with a formula in Airtable. I’m trying to convert strings to numbers based on another cell’s content. The formula seems to work, but when I try to use these columns, I get NaN 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 adding INT() around the output values might help, but it just broke everything. What am I doing wrong here? How can I fix this NaN issue?
I’ve encountered similar issues with Airtable formulas before. The problem likely stems from the FIND() function returning errors when the substring isn’t found. To fix this, you can use the SEARCH() function instead, which returns 0 if the substring 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 you always get a valid number output, avoiding the NaN issue. The last 0 is a fallback value if none of the conditions are met. Adjust it as needed for your use case.
Also, make sure the field you’re putting this formula in is set to ‘Number’ type. That should resolve your NaN problems and give you the expected numeric output.
The NaN issue you’re experiencing is likely due to the FIND() function returning an error when the substring isn’t found. A more robust approach would be to use the ISNUMBER() function in combination with FIND(). This ensures you always get a valid output.
Try this modified formula:
IF(ISNUMBER(FIND('1',{Functional response})),-4,
IF(ISNUMBER(FIND('2',{Functional response})),-2,
IF(ISNUMBER(FIND('3',{Functional response})),0,
IF(ISNUMBER(FIND('4',{Functional response})),2,
IF(ISNUMBER(FIND('5',{Functional response})),4,0)))))
This should resolve the NaN issue while maintaining the logic of your original formula. The last 0 serves as a default value if none of the conditions are met. Adjust as needed for your specific use case.
Remember to set the field type to ‘Number’ in Airtable to ensure proper handling of numeric values.
hey, try using VALUE() function to force number conversion. like this:
VALUE(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 should fix the NaN issue. lmk if it works!