Forum Discussion
Formula Challenge: The most efficient way to generate a Number Spiral (Ulam spiral) ?
The goal: Ulam spiral - Wikipedia The trick is creating a function capable of producing the largest matrix of numbers possible (this may rule out the recursive approach). The approach ...
It looks good! I put your function through the accuracy checks (elements, number of elements, max element - did not include the check on the top-left element because the wrapping is done differently) and it passes every time.
I put both our functions through some stress testing.
The record for n seems to be about 1,250,000 for each. The functions may be capable of handling a bit more but it's at a point where the calculation time takes a few minutes.
It's funny how this function bonked on 1.25 million but your Spiral did not:
=COUNT(UNIQUE(TOCOL(D1#)))
Patrick2788 As I mentioned it wasn't optimized much at all. So here is one slightly more optimized (almost 1/2 the time). Basically I halfed the loops so I do 2 sides each time instead of only 1:
uSpiral2 = lambda(n, IF(n >4,
LET(
turns, INT(SQRT(n - 1)) - 1,
s, SEQUENCE(turns,,2),
all, REDUCE(
{4,3; 1, 2},
s,
LAMBDA(p, q,
let(mx, q*q,
if(
iseven(q),
VSTACK(hstack(SEQUENCE(q, 1, mx + 1),p), SEQUENCE(1, q+1, q + mx+1)),
VSTACK(SEQUENCE(1, q+1, mx + 2*q + 1, -1), hstack(p, SEQUENCE(q,1, mx + q,-1)))
))
)
),
final, IF(all > n, "", all),
final
),
"Parameter 'n' must be >4"
));
- That is much more efficient. Maybe the only way to speed it up even more would be remove the stacking functions and go purely on EXPAND, if that's even possible (EXPAND would probably need recursion which would defeat the purpose).
It took about four 3-4 minutes to finish, but I was able to reach Sheets' 10 million cell threshhold using that formula with each side having length 3162. Overall N = 9,998,244
