Nested IF functions

I was hoping for help with something 1) general and something 2) specific;

  1. Is there a limited to the size of a formula that Composer Pro will accept, in terms of characters? I have built a massive formula of nested IF functions and it is too big (Composer Pro freezes when I attempt to paste it in and I need to refresh the browser…) and I was wondering whether I could get it ‘accepted’ if I reduced the size of the formula by changing the name of my variables to very short names!? Or the complexity of it just freezes Composer up?

  2. Having said that, I assume the formula is simply too big and there is a more efficient way to do what I want…which is…I have set up a timer to display with mm:ss:000. As the timer runs, another display indicates how many minutes have elapsed. The formula is (note everything is in milliseconds, and a minute is 60000 milliseconds);
    IF(DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart)<60000, 1,IF(DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart)<120000, 2,IF(DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart)<180000, 3,IF…

My only ‘coding’ background is with basic Excel VBA…and I know Excel has a limit of something like ~60 nested IF functions…so it doesn’t surprise me that I am crashing when I try to do 250! I imagine a potential alternative is some kind of lookup to a table (containing desired values and time periods, for example 0-60000 = 1, 60001-120000 = 2). Just not sure where to start. I can see the FIND function that works with a list but I have no idea where the list ‘lives’, etc? I can see it is a JSON list, but can someone please dumb it down for me?

Thanks in advance

Hi Paul, I unfortunately don’t have information on maximum formula length but might be able to help with a lighter approach to what you’re wanting to achieve.

To find the number of full minutes elapsed, I would first divide the number of milliseconds elapsed by 60000 to get the minutes elapsed as a real number, and then use the FLOOR formula function to round it down to the nearest integer. So something like:

FLOOR(DATETIME_DIFFERENCE(appVars.TimeNow, pageVars.TimerStart)/60000)