Another Nested IF Formula question

Hi @Christa_Stevenson,

The reason your formula is never returning anything greater than the “21-50” range is because your formula will always return either the first value or the second value, due to the way it is processed. When Airtable goes to process your formula, it sees the first IF() and checks the condition : {Number of ELLs} < 21 ? : … if the condition is met, then it returns your value of "0-20". If the condition is not met, it moves on to the next IF() statement and checks its condition : {Number of ELLs} > 20 ? : … since that statement will always be true if the first one is false, Airtable never makes it past that statement – it returns “21-50” for all values greater than 20.

A series of nested IF() statements is never processed beyond a matching condition - once a condition is matched, and a value is returned, the rest of the formula is ignored. For that reason, you have to write your formula in such a way that the failure of one condition leads naturally to checking the next one, and the final value is only returned if all other conditions fail – you could call this a “threshold pattern”. You are asking Airtable to evaluate, “Do we meet this top threshold? no… move on to the next… do we meet this next threshold? no… move on to the next… etc”. You have to write the formula in such a way that it’s possible to even make it to the final condition check, rather than have it subsumed by prior condition checks.

So let’s re-write that formula with a descending threshold pattern (because descending is the easiest way to write it):

IF(
   {Number of ELLs} >= 1501,
   "1500+",
   IF(
      {Number of ELLs} >= 1001,
      "1001-1500",
      IF(
         {Number of ELLs} >= 501,
         "501-1000",
         IF(
            {Number of ELLs} >= 301,
            "301-500",
            IF(
               {Number of ELLs} >= 101,
               "101-300",
               IF(
                  {Number of ELLs} >= 76,
                  "76-100",
                  IF(
                     {Number of ELLs} >= 51,
                     "51-75",
                     IF(
                        {Number of ELLs} >= 21,
                        "21-50",
                        "0-20"
))))))))

That should do what you want.

Thank you! That did the trick :slightly_smiling_face: