=SUM(SEQUENCE(10000000))
The formula above is able to sum upto 10 million virtual array elements. We know that 10 million is the limit according to this question and answer. Now, if the same is implemented as Lambda using Lambda helper function REDUCE:
=REDUCE(,SEQUENCE(10000000),LAMBDA(a,c,a+c))
We get,
Calculation limit was reached when trying to calculate this formula
This can happen in 2 cases:
- The computation for the formula takes too long.
- It uses too much memory.
To resolve it, use a simpler formula to reduce complexity.
So, it says the reason is space and time complexity. But what is the exact space used to throw this error? How is this determined?
In the REDUCE function above, the limit was at around 66k for a virtual array:
=REDUCE(,SEQUENCE(66660),LAMBDA(a,c,a+c))
However, if we remove the addition criteria and make it return only the current value c, the allowed virtual array size seems to increase to 190k:
=REDUCE(,SEQUENCE(190000),LAMBDA(a,c,c))
After which it throws a error. So, what factors determine the memory limit here? I think it's memory limit, because it throws the error almost within a few seconds.
If you're affected by this issue, you can send feedback to Google:
Update Oct '22 (Credit to MaxMarkhov)
The limit is now 10x higher at 1.9 million
1999992. This is still less than 1/5th of 10 million virtual array limit of non-lambda formulas, but much better than before. Also non-lambda formulas's limit doesn't reduce with number of operations. But lambda helper formulas limit still does decrease with number of operations. So, even though it's 10x higher, that just means ~5 extra operations inside lambda(see table below).A partial answer
We know for a fact, the following factors decide the calculation limit drum roll:
LAMBDA()function callsThe base number for 1 operation seems to be
1999921 2(=REDUCE(,SEQUENCE(199992),LAMBDA(a,c,c))). But for a zero-op or a no-op(=REDUCE(,SEQUENCE(10000000),LAMBDA(a,c,0))), the memory limit is much higher, but you'll still run into time limit. We also know number of operations is a factor, because=REDUCE(,SEQUENCE(66664/1),LAMBDA(a,c,a+c))fails=REDUCE(,SEQUENCE(66664),LAMBDA(a,c,a+c))works.=REDUCE(,SEQUENCE(66664),LAMBDA(a,c,a+c+0))failsNote that the size of operands doesn't matter. If
=REDUCE(,SEQUENCE(39998),LAMBDA(a,c,a+c+0))works,=REDUCE(,SEQUENCE(39998),LAMBDA(a,c,a+c+100000))will also work.For each increase in number of operations inside the lambda function, the maximum allowed array size falls by
2n-1(Credit to @OlegValter for actually figuring out there's a factor multiple here):(inside lambda)
(from 199992)
Operations outside the
LAMBDAfunctions also count. For eg,=REDUCE(,SEQUENCE(199992/1),LAMBDA(a,c,c))will fail due to extra/1operation, but you only need to reduce the array size linearly by 1 or 2 per operation, i.e., this=REDUCE(,SEQUENCE(199990/1),LAMBDA(a,c,c))will work3.In addition
LAMBDAfunction calls itself cost more. So, refactoring your code doesn't eliminate the memory limit, but reduces it furthermore. For eg, if your code usesLAMBDA(a,c,(a-1)+(a-1)), if you add another lambda like this:LAMBDA(a,c,LAMBDA(aminus,aminus+aminus)(a-1)), it errors out with much less array elements than before(~20% less).LAMBDAis much more expensive than repeating calls.There are many other factors at play, especially with other
LAMBDAfunctions. Google might change their mind about these arbitrary limits later. But this gives a start.Possible workarounds:
LAMBDAitself isn't restricted. You can nest as much as you want to. Only LAMBDA Helper Functions are restricted. (Credit to player0)Named functions which don't use
LAMBDA(helper functions) themselves, aren't subjected to the same restrictions. But they're subject to maximum recursion restrictions.Another workaround is to avoid using
lambdaas aarrayformulaand use autofill or drag fill feature, by making the lambda function return only one value per function. Note that this might actually make your sheet slow. But apparently, Google is ok with that - multiple individual calls instead of a single array call. For example, I've written a permutations function here to get a list of all permutations. While it complains about "memory limit" for a array with more than 6 items, it can work easily by autofill/dragfill/copy+paste with relative ranges.