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#)))
