Getting NaN results from Airtable conditional formula with numeric outputs

Other Questions Airtable
1

I have created a formula in Airtable that uses nested IF statements to convert text values into numbers based on what I find in another field. The formula itself appears to be working correctly when I look at it, but whenever I try to use these calculated values in other formulas or reference them elsewhere, I keep getting NaN (Not a Number) errors instead of the expected numeric results.

Here’s the formula I’m currently using:

IF(SEARCH('A',{Rating field}),-8,
IF(SEARCH('B',{Rating field}),-4,
IF(SEARCH('C',{Rating field}),0,
IF(SEARCH('D',{Rating field}),4,
IF(SEARCH('E',{Rating field}),8,"")))))

I attempted to wrap the numeric outputs with INT() function thinking it might help with the data type, but that actually made things worse and caused more errors. What could be causing this NaN issue? Is there something wrong with how I’m structuring this nested conditional statement?

2

Your SEARCH function is throwing an error when it doesn’t find a match, and that’s creating the NaN values. SEARCH doesn’t return false like you’d expect - it actually errors out, which breaks your nested IF structure. Switch to FIND and wrap each one in ISERROR to handle this properly. Try something like IF(NOT(ISERROR(FIND(‘A’,{Rating field}))),-8,IF(NOT(ISERROR(FIND(‘B’,{Rating field}))),-4… and continue from there. This catches the errors that happen when there’s no match and stops the NaN cascade.

3

hey, have you tried double-checking your ‘Rating field’? if there’s no match, it throws NaN. also, consider using FIND() for exact matches, and wrap your formula in ISERROR() to catch issues. hope this helps!