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 ...
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).
It's close. It looks like 10 is being skipped. I played with adjusting the 2nd VSTACK within the CHOOSE to remove the +1, this adds 10 back to the matrix but then causes a duplication. It's a good start!
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" ));