Getting NaN results when using nested IF conditions in Airtable

Other Questions Airtable
1

I have a formula that uses nested IF statements to convert text values into numbers based on what’s found in another field. The formula seems to work at first, but when I try to use these calculated values in other formulas or reference them elsewhere, I keep getting NaN (Not a Number) errors.

Here’s my current formula setup:

IF(FIND('A',{Response Type}),-10,
IF(FIND('B',{Response Type}),-5,
IF(FIND('C',{Response Type}),0,
IF(FIND('D',{Response Type}),5,
IF(FIND('E',{Response Type}),10,"")))))

I thought about wrapping the numbers with VALUE() function but that didn’t help. Has anyone run into this issue before? What could be causing the NaN output when these values are referenced in calculations?

2

Your FIND function is throwing errors when it can’t find the text, which breaks your nested IF statements. FIND doesn’t return a clean false - it just errors out. I’ve hit this same issue before. You need to wrap each FIND with IF(ISERROR()) to catch the errors first. Try this: IF(ISERROR(FIND(‘A’,{Response Type})), IF(ISERROR(FIND(‘B’,{Response Type})), IF(ISERROR(FIND(‘C’,{Response Type})), IF(ISERROR(FIND(‘D’,{Response Type})), IF(ISERROR(FIND(‘E’,{Response Type})), “”, -10), -5), 0), 5), 10). This catches each error before it can mess up your whole formula, so the IF statements can actually work through each condition properly.

3

Nested IF logic gets messy fast. Hit this same problem last year building a user feedback scoring system.

The issue is FIND throws an error when it can’t match instead of returning false. That breaks your whole chain.

I switched to SEARCH since it handles this better. But the cleanest solution is using SWITCH with REGEX_MATCH:

SWITCH(
REGEX_MATCH({Response Type}, "A"), -10,
REGEX_MATCH({Response Type}, "B"), -5,  
REGEX_MATCH({Response Type}, "C"), 0,
REGEX_MATCH({Response Type}, "D"), 5,
REGEX_MATCH({Response Type}, "E"), 10,
0
)

REGEX_MATCH gives you a clean boolean instead of dealing with FIND errors. Way easier to debug when things break.

The 0 at the end is your fallback value - prevents calculation issues later.

4

FIND returns error positions, not true/false values - that’s what’s breaking your nested formula. Hit the same issue last month with grade conversion formulas. Ditch FIND and use direct text matching instead. Try REGEX_EXTRACT or simple comparisons: IF({Response Type} = “A”, -10, IF({Response Type} = “B”, -5, IF({Response Type} = “C”, 0, IF({Response Type} = “D”, 5, IF({Response Type} = “E”, 10, 0))))) This skips search function errors completely. If you’ve got mixed content in Response Type and need partial matching, use IFERROR around your FIND calls instead of ISERROR - way more reliable for catching calculation issues. Just make sure every branch spits out a real number, never empty strings or errors.

5

hey, i had this issue too! the FIND() function fails when it doesn’t find a match, which is why you’re seeing NaN. you might wanna encapsulate each FIND() in an ISERROR() check. alternatively, consider switching to a SWITCH() function for clarity. hope this helps!