Forum Discussion

Silver Contributor

Jul 18, 2023

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 ...

Spiral.xlsx31 KB

excel

Formulas and Functions

office 365

m_tarler

Silver Contributor

Mar 09, 2026

I don't know if thunks could make this better but please give this a time trial:

=LET(halfside,C2,whole,2*halfside,
x,SEQUENCE(1,whole,0),y,TRANSPOSE(x),s,SEQUENCE(1,whole,whole-2,-2),sq,s^2,s_r,SEQUENCE(1,whole,-whole+1,2),sq_r,s_r^2,
west,(sq-x+y)*(x<y)*(x<whole-y),
north,(TRANSPOSE(sq)+4*(whole-2*y-1)+y-x)*(x>=y)*(y+1<whole-x),
east,(sq_r+x-y+1)*(x>y)*(x>=whole-y-1),
south, (TRANSPOSE(sq_r-s_r)+y-whole +x+2)*(x>=whole-y)*(x<=y),
west+north+east+south)

basically I looked at the spiral as 4 quadrants (north, south, east, west) (basically the colored square by ShayCapehart but with the red including the 1 and diagnol and the yellow including the 3 and that diagnol) and each line in those quadants is a simple series with an offset from a perfect square (odd or even). I then 'mask' each quadrant and then add the 4 quadrants together. I think I could further improve the logic and math a little. maybe only calculate the relevant 1/2 of the matrix and then pad it. But even in this 'crude' form it seems to perform pretty well.

Patrick2788

Silver Contributor

Mar 10, 2026

The timings show some good savings with using thunks:

Your function with thunks:

//Thunked variant

SpiralMTVariantλ=
LAMBDA(
    halfside,

LET(
    whole, 2 * halfside,
    x,     LAMBDA(SEQUENCE(1, whole, 0)),
    y,     LAMBDA(TRANSPOSE(x())),
    s,     LAMBDA(SEQUENCE(1, whole, whole - 2, -2)),
    
    sq,    LAMBDA(s() ^ 2),
    
    s_r,   LAMBDA(SEQUENCE(1, whole, -whole + 1, 2)),
    
    sq_r,  LAMBDA(s_r() ^ 2),
    
    west,  LAMBDA((sq() - x() + y()) * (x() < y()) * (x() < whole - y())),
    
    north, LAMBDA((TRANSPOSE(sq()) + 4 * (whole - 2 * y() - 1) +
           y() - x()) * (x() >= y()) * (y() + 1 < whole - x())),

    east,  LAMBDA((sq_r() + x() - y() + 1) * (x() > y()) * (x() >= whole - y() - 1)),
    south, LAMBDA((TRANSPOSE(sq_r() - s_r()) + y() - whole + x() + 2) *
           (x() >= whole - y()) * (x() <= y())),

    west() + north() + east() + south()
));

m_tarler
Silver Contributor
Mar 10, 2026
very nice. you didn't push it to the 51,854,401 elements you tested below but my testing seemed to perform very well but different machines and all, so wasn't sure. As for the Thunks, it seems to be like a 15% savings. I would be curious to see how much savings I could get from removing the 1/2 matrix on each quadrant that I mask out anyhow.