Forum Discussion
Formula Challenge: The most efficient way to generate a Number Spiral (Ulam spiral) ?
interesting. I already did something like this for that Fibonacci sequence challenge. I will have to pull out that file and see how I did it then
Ok so I used an approach inside out and here it is modified for this solution:
=IF(n > 1,
LET(turns, 2 * INT(SQRT(n)) - 1,
s, SEQUENCE(turns),
all, REDUCE({1, 2},s,LAMBDA(p, q,
CHOOSE(
MOD(q, 4) + 1,
HSTACK(p, SEQUENCE(ROWS(p), 1, MAX(p) + ROWS(p) + 1, -1)),
VSTACK(SEQUENCE(1, COLUMNS(p), COLUMNS(p) + MAX(p), -1), p),
HSTACK(SEQUENCE(ROWS(p), 1, MAX(p) + 1), p),
VSTACK(p, SEQUENCE(1, COLUMNS(p), MAX(p) + 1))
)
)
),
final, IF(all > n, "", all),
final
),
"Parameter 'n' must be >=1"
)
it is far from optimized as excel has to find the MAX each loop when I could potentially add some parameters to the accumulator to pass the max and # rows/columns and even the next position
regardless it works relatively well and can do significant numbers (just how long you want to wait).
Patrick2788 dang I thought I fixed that. the problem was on the first hstack. When you add ROWS() you don't need the extra +1. i fixed it on the vstack that uses COLUMNS() and thought I fixed it on that hstack but either forgot or lost the change when I did something else. Check the attached
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" ));