The goal:
The trick is creating a function capable of producing the largest matrix of numbers possible (this may rule out the recursive approach).
The approach I took:
The approach I took was creating 'frames' and working inward with REDUCE essentially performing repeated addition:
REDUCE works its way through an array of odd numbers in descending order (1 is omitted) with 3 different situations:
1. The odd number is the largest so the matrix does not require padding.
2. The odd number is 3, the anomaly, so a 'padded core' is created.
3. The odd number is not the greatest nor 3 so a padded matrix is generated
Spiral Lambda:
=LET(
s, ODD(INT(SQRT(n))),
arr, UNIQUE(ODD(SEQUENCE(s - 1, , s, -1))),
arrCore, {5, 4, 3; 6, 1, 2; 7, 8, 9},
IFERROR(
REDUCE(
0,
arr,
LAMBDA(a, v,
LET(
TopBottomPadding, EXPAND(0, (s - v) / 2, s, 0),
SidePadding, EXPAND(0, v, (s - v) / 2, 0),
top, SEQUENCE(, v, (v - 1) ^ 2 + 1, -1),
bottom, SEQUENCE(, v, (v - 1) ^ 2 + v, 1),
left_frame, EXPAND(SEQUENCE(v - 2, , (v - 1) ^ 2 + 2), , v - 1, 0),
right_frame, SEQUENCE(v - 2, , (v - 1) ^ 2 - (v - 1), -1),
core_stuffing, EXPAND(0, v, (s - v) / 2, 0),
core, VSTACK(
TopBottomPadding,
HSTACK(core_stuffing, arrCore, core_stuffing),
TopBottomPadding
),
center, HSTACK(left_frame, right_frame),
nopad, VSTACK(top, center, bottom),
pad, VSTACK(
TopBottomPadding,
HSTACK(SidePadding, VSTACK(top, center, bottom), SidePadding),
TopBottomPadding
),
a + IF(v = s, nopad, IF(v = 3, core, pad))
)
)
),
"Please enter a number 17 or greater"
)
)The accuracy checks
The highest number I've been able to get away with for n is 300,001. I'm interesting in any suggested improvements or different approaches to this task!
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#)))
Thanks for responding. About a month ago, someone needed some help creating the reverse spiral, or Pyramid Ascent as I like to call it. As you can imagine, that quickly turned into seeing who could come up with the shortest/fastest/most robust formula. Sheets kept reaching a calculation limit, and I knew that was highly affected by the use of LAMBDA helper functions.
After an all-nighter, I came up with this direct approach.
=lambda(n,
makearray(n,n,lambda(i,j,
let(a,min(n-i,j-1,i-1,n-j),
b,if(i>j,4*n-j-i-6*a,j+i-2*a),
4*a*(n-a)+b-1))))
It was still hitting the calc limit at 219 rows, so last week I was determined to:
- Get rid of MIN, since it's generating all four values at each coordinate.
- Remove the final LAMBDA helper function, MAKEARRAY.
That led to a formula similar to the one I subitted here. I thought that I could easily convert the Pyramid Ascent into the Ulam Spiral by simply adding n^1-1, but I now recognize that the Ulam Spiral is set by the spiral direction starting at 1 vs the Pyramid Ascent, which always begins with 1 in the upper left corner. I'm currently working on that fix.
As for any future improvements to efficiency, here's my thought. If the final matrix is generated row by row, would it be more efficient if at each row, stack together three arrays of numbers, which would avoid a bunch of conditional tests to determine which quadrant each coordinates is in. I've tried visualizing this below:
I've made a Named Function called SPIRAL that outputs the spiral (fix still pending from above) given the side length and type of spiral. This spreadsheet also includes some nice extras to visualize the prime numbers and demonstrate that it may not be the primes that have a pattern, but rather the non-primes. And when you remove a pattern from a canvas, what remains can appear to be a pattern.