6 Replies Latest reply: May 15, 2014 10:01 AM by Nick Osdale-Popa RSS

    Formula limits

    AmyNRA _

      I've noticed that the If() function can only have so many And/Or conditions or it starts to ignore some.  I don't know HOW many.  Does anyone here know?


      Also, is there a character-length limit for formulas?  Or any other limit, such as how many functions can be nested?


      I'd hate to take the time to write a whole, big, long one and have it say, "Sorry, Charlie"!

        • Formula limits
          Data Kruncher

          Hi Amy,


          I've built some pretty large expressions with If, .And. and .Or., and I've never had them fail. As long you're very careful about placing the ( and ) characters, I'd be very surprised to hear that Monarch didn't interpret the logic properly. Shocked, in fact.


          As to the character length limit, as of v9 it's a whopping 32,786 (32K)characters, up from the 4K limit of v8. Large enough that nobody seriously wants to debug a formula of that size.

            • Formula limits
              AmyNRA _

              Thanks for such a quick answer!


              Yes, 32,786 is big enough for ME! 


              I was really pretty sure that my parentheses were good.  I even tried changing the one it was skipping to the beginning instead of the end.  I got around it by using more Ifs.


              I will look again next time this happens and, if I don't see the cause, save some examples to discuss here.



                • Formula limits
                  Grant Perkins



                  Somewhere at the back of my mind I recall that the IF() function supports a maximum of 36 nested expressions.


                  However I think if one uses the patented Osdale-Popa method of conjoining multiple IF() statements (using the + sign) then you are only limited by the max number of characters available which, as Kruncher has pointed out, became quite a big number in Version 9.


                  Can anyone confirm this and save me a trawl back through the archives?



                  • Formula limits
                    Bruce _



                    Another possibility is that part of the formula is not evaluating how you think it should. For instance null (and you think it's zero), a string (and you think it's a number) or some other mis-match.


                    You can check using the "test" tab on the variable, or by breaking down the calculation step-by-step, or try posting the calculation on this site.


                    Even on version 8 it was very difficult to "blow" the limit on calculations.