Search code examples
if-statementexcel-formulaintegerformulaairtable

Airtable If-statement outputting NaN

I'm using an If-statement to assign integers to strings from another cell. This seems to be working, but if I reference these columns, I'm getting a NaN value. This is my formula below. I tried adding INT() around the output values, but that seemed to break everything. Am I missing something? 

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,"")))))
Solution

  • Assuming Functional response can only store a number 1 to 5 as a string a simple option in excel would be to first convert the string to a number and then use the choose function to assign a value. this works as the numbers are are sequential integers. Assuming Cell K2 has the value of Functional response, your formula could be:

    =CHOOSE(--K2,-4,-2,0,2,4)
=CHOOSE(K2+0,-4,-2,0,2,4)
=CHOOSE(K2-0,-4,-2,0,2,4)
=CHOOSE(K2*1,-4,-2,0,2,4)
=CHOOSE(K2/1,-4,-2,0,2,4)

    Basically sending the string of a pure number through a math operation has excel convert it to a number. By sending it through a math operation that does not change its value, you get the string as a number.

    CHOOSE is like a sequential IF function Supply it with an integer as the first argument and then it will return the value from the subsequent list that matches the number. if the number you supply is greater than the number of options you will get an error.

    Alternatively you could just do a straight math convertion on the number stored as a string in K2 using the following formula:

    =(K2-3)*2

    And as my final option, you could build a table and use VLOOKUP or INDEX/MATCH.

    POC

    NOTE: If B2:B6 was stored as strings instead of numbers, K2 instead of --K2 would need to be used.