Nested IF statement - sometimes it's working, and sometimes it's not?!

[hhspiel]

We had students respond to a number of self-reflection questions, and their answers are in table 1.  Table 2 uses lookup fields to pull in those answers, and then a formula field to attempt to score them.

Each question has 5 answer choices, and each answer choice is worth between 1 and 5 points.  Here's an example with the number of points each answer is worth in parentheses:  

1. How would you react to constructive criticism from professors in your host country?

   • a) I might take it personally and feel discouraged. (1)
   • b) I'd feel a bit upset but would try to learn from it. (2)
   • c) I'd take it as it is, without much emotion. (3)
   • d) I'd see it as valuable feedback for improvement. (4)
   • e) I'd welcome it and actively seek out feedback for growth. (5)

We're using a version of the same nested IF statement to score each question, though obviously changing the answer text:

IF({non-cog16}="I might take it personally and feel discouraged.",1,IF({non-cog16}="I'd feel a bit upset but would try to learn from it.",2,IF({non-cog16}="I'd take it as it is, without much emotion.",3,IF({non-cog16}="I'd see it as valuable feedback for improvement.",4,IF({non-cog16}="I'd welcome it and actively seek out feedback for growth.",5)))))

In this example, the formula correctly scores for values of 1, 2, 4, and 5, but leaves any answers that should have been scored a 3 blank.  On some of the questions, the formula correctly scores all of the results, in some of them it will score 4 of the 5 answer choices (most common), and in some of them, it only scores 1 or 2 of the answer choices. 

The text is copied and pasted directed from the answer choice in the next field, so I don't see how it could be a text issue even though that seems like the most likely explanation.  I've also tried retyping, repasting, etc, but the results are always the same.

Any ideas as to what could be causing this or suggestions as to how to troubleshoot would be much appreciated!

[Ben_Young1]

Hey @hhspiel!

I just want to confirm that the preceding a), b), c), etc. values are not a part of the included options.
When using the equality comparison operator in Airtable, strict equality is used, meaning that even trailing or leading whitespace would result in the expression evaluating to false.

Give this formula a shot and let me know how it plays out.
I tested this in an isolated example and was able to get it to behave as expected.

IF(
    {non-cog16},
    SWITCH(
        TRIM({non-cog16}),
        "I might take it personally and feel discouraged.",
            1,
        "I'd feel a bit upset but would try to learn from it.",
            2,
        "I'd take it as it is, without much emotion.",
            3,
        "I'd see it as valuable feedback for improvement.",
            4,
        "I'd welcome it and actively seek out feedback for growth.",
            5
    )
)

 

[hhspiel]

Thanks for the suggestion, @Ben_Young1 !  I wasn't familiar with SWITCH - that is so much easier to read, I will definitely use that moving forward!  Unfortunately, the formula you posted has the same results for me - all answers are scored EXCEPT  -  3 "I'd take it as it is, without much emotion." - which to me again suggests that it is a text issue, but "I'd take it as it is, without much emotion." is the exact text pasted from the answer field.  

Wonder why it would work for you and not for me - what does that tell us?

And yes, confirming that the preceding labels a), b), etc are not part of the answers.

Thanks again!