3 Replies Latest reply on Jan 9, 2018 9:37 AM by swaroop.gantela

    Creating calculated field with conditions (if value is null)

    Magan Thigpen

      I have three fields that I want to include in a calculation. It seems relatively simple but I can't figure out what the syntax should be. I have 3 different test scores for students but not all students have scores for all 3. I want to take the most recent test score and subtract their previous score. In other words, I want to subtract score 2 from score 3 and if the student doesn't have score 2, then subtract score 1 from score 3.

       

      Here is the formula I tried:

      IF isnull([LetterWordID - GE - BOY (1617)])

        THEN([LetterWordID - GE - EndOY (1617)]-[LetterWordID - GE - EndOY (1516)])

        ELSE

      ([LetterWordID - GE - EndOY (1617)]-[LetterWordID - GE - BOY (1617)])

      END

       

      That ends up giving me a field that shows results for

        ([LetterWordID - GE - EndOY (1617)]-[LetterWordID - GE - EndOY (1516)])

      but does not include results for [LetterWordID - GE - EndOY (1617)]-[LetterWordID - GE - BOY (1617)] when the student is missing data for [LetterWordID - GE - BOY (1617)]