Forum Discussion
Alternative formulas for expanding a column of row thunks.
Second variant is much faster. 200x90 array
Second variant gives approximately the same time on 1400x900 array
I tested on =SEQUENCE( n, m ) array and on slow PC.
First variant works on per-element basis, second one with arrays loaded into memory as one object. My guess.
Second variant is much faster. 200x90 array
Second variant gives approximately the same time on 1400x900 array
I tested on =SEQUENCE( n, m ) array and on slow PC.
First variant works on per-element basis, second one with arrays loaded into memory as one object. My guess.
Thanks for that. It confirms my observations.
Apart from being slower, the first solution is growing as O(n²), so even at a couple of hundred rows it is passing into the unusable zone. The REDUCE and VSTACK should be good for over 1000 rows. Sad, because I liked the first solution.
I think that removing the nested arrays constraint should be rising to the top of the 'to-do' list. If not universally through Excel, at least for BYROW and BYCOL which should be able to stack arrays just like VSTACK and HSTACK.
In addition I tried to compare your second variant (sortRowsA below) with simple by row number iteration:
fnRow=LAMBDA( x, SORT( UNIQUE(x,1),,,1) ); sortRowsA=LAMBDA( array, fn, LET( fakeVector, LAMBDA( vector, LAMBDA(vector) ), setRow, LAMBDA(x, fakeVector(fn(x))), set2D, BYROW( array, setRow ), DROP( REDUCE( 1 , set2D, LAMBDA(a, v, VSTACK(a, v()) ) ), 1) )); sortRowsB=LAMBDA(array, fn, REDUCE( "", SEQUENCE(ROWS(array)), LAMBDA(a, k, IF( SEQUENCE(k) = k, fn( INDEX(array, k, ) ), a ) ) ) );On the same array average time of calculation
varA - 773 ms
varB - 1303 ms (i.e. two times slowly)
To compare, Solution 1 on the same array gives 57070 ms (or 57 sec) in average. That is huge difference.
- Is the Excel team considering lifting this constraint in the future?
To underline SergeiBaklan reply the last information I had came from JoeMcDaid last September.
It was the first response to my discussion
When dynamic arrays appeared I was suddenly set free from the rigidity of CSE (many of my workbooks comprised nothing but CSE formulae are re-dimensioning used to be a nightmare. I rapidly found the lack of array breakup made accumulation difficult (corkscrews in finance) and having to use MMULT for row or column sums was painful. Now I have SCAN and BYCOL, I should be happy, but now the 'nested array problem' keep on rearing its head! Thunks do provide some sort of answer, but not one I would care to cover in a basic training course!
I think I would settle for BYROW and BYCOL acting as wrappers in which nested arrays were supported. With a but of luck that removes issues of backward compatibility because Excel 2016 does not support the functions anyway.
- I've only read through page 1 of that thread but must say your ideal for GLOBAL is exciting. I hope someone was taking notes.
I don't think so. That means new calc engine for Excel. Build expanding into existing functions means to make it universal, i.e. workable in all possible scenarios and on different platforms. I could be wrong, but that is significant investments.
