Forum Discussion
Formula Challenge: The most efficient way to generate a Number Spiral (Ulam spiral) ?
Patrick2788 Interesting challenge. I took a slightly different approach, starting from the center of the spiral and working my way outwards, offsetting the row or column number by either 1 or -1 at each step. I used TOCOL / IF / SEQUENCE as the primary method of array manipulation, then SORTBY to sort the numeric sequence by row and column number, and WRAPROWS to output the final results. Due to the row limitations of the SEQUENCE function, the maximum output size is 1023 rows by 1023 columns (1,046,529 numbers), but it only takes 3 seconds to process.
The complete definition of the LAMBDA function is:
ULAMSPIRAL:
=LAMBDA(rows,
LET(
n, rows + ISEVEN(rows),
adj, DROP(TOCOL(IF({1,1}, SEQUENCE(n))), -1),
row, DROP(TOCOL(IF(SEQUENCE(n), {0,1})), -1),
off, DROP(DROP(TOCOL(IF(SEQUENCE(n), {1,1,-1,-1})), 1), -2),
rId, DROP(TOCOL(IFS(adj >= SEQUENCE(, MAX(adj)), SEQUENCE(ROWS(adj))), 2), -1),
r, INDEX(row, rId),
k, INDEX(off, rId),
s, ROUNDUP(n/2, 0),
POS, LAMBDA(int,arr, VSTACK(int, SCAN(int, arr, LAMBDA(a,v, a+v)))),
spiral, WRAPROWS(SORTBY(SEQUENCE(n^2), POS(s, r*k), 1, POS(s, NOT(r)*k), 1), n),
DROP(spiral, rows-n, n-rows)
)
)
It's set up a little different, in that you enter the total number of rows to be generated (n=rows^2). Entering an odd number of rows will generate a perfect spiral, whereas an even number of rows will be truncated (the last row and first column will be dropped)...
Patrick2788 Sorry, I wasn't happy with the row limitations of my first try, so I came up with another version using the MAKEARRAY function:
ULAMSPIRAL:
=LAMBDA(rows,
LET(
n, rows + ISEVEN(rows),
s, ROUNDUP(n / 2, 0),
spiral, MAKEARRAY(n, n, LAMBDA(r,c, LET(
off_r, r - s,
off_c, c - s,
lvl, MAX(ABS(off_r), ABS(off_c)) + 1,
lvl_n, lvl * 2 - 1,
lvl_r, lvl + off_r,
lvl_c, lvl + off_c,
IFS(
lvl_r = lvl_n, lvl_n ^ 2 - lvl_n + lvl_c,
lvl_c = 1, lvl_n ^ 2 - lvl_n * 2 + lvl_r + 1,
lvl_r = 1, lvl_n ^ 2 - lvl_n * 2 - lvl_c + 3,
TRUE, lvl_n ^ 2 - lvl_n * 3 - lvl_r + 4)))),
IF(ISODD(rows), spiral, DROP(spiral, -1, 1))
)
)
Basically, the value of each cell is determined by its row and column number in relation to the center of the spiral. Surprisingly, this version was more efficient that my first, returning a 1023x1023 spiral in only 2 seconds. The maximum size I was able to achieve was a 7327x7327 spiral (53,684,929 numbers) in 2 minutes and 7 seconds. Attempting 7328 rows and above returned the "Excel ran out of resources" error message.