Forum Discussion

Patrick2788's avatar
Patrick2788
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 ...
Excel
Formulas and Functions
office 365
djclements's avatar
djclements
Bronze Contributor
Mar 25, 2024

ShayCapehart When I first tried your formula, it didn't work in Excel as written. I just tried it again and realized the INDEX function was not needed. After removing that, it worked like a charm!

 

It's approximately twice as fast as my MAKEARRAY version, outputting a 1023x1023 spiral in 1 second, and a 7327x7327 spiral in 1 minute and 13 seconds. Again, 7328 rows and above returned the "Excel ran out of resources" error (I guess 7328^2 is too large).

 

The only little quirk I noticed was that the spiral order is different between odd and even numbers... with odd numbers, the first move from center is to the left, then down (as opposed to the right, then up). As a result, the final number is located in the top-left corner of the spiral, rather than the bottom-right. No big deal... it takes first prize in speed! 🙂

ShayCapehart's avatar
ShayCapehart
Copper Contributor
Mar 25, 2024
Oh that makes sense. I first started down this project building a reverse number spiral, A Pyramid Ascent, and I always started with 1 in the upper left corner. When I came across this post, I thought I could easily convert it into an Ulam Spiral by just adding n^2 at the beginning and start my ascent at 0 instead. But you're correct, the Ulam Spiral always moves in the same direction from where it starts, not where it ends.

I tried your formula in Sheets. Not only does Sheets not have the DROP formula yet, but even without that part, Sheets reaches it's calculation limit at 178 rows. That's the whole reason as to why I started looking for a direct one-pass approach. Thanks for running it.

Resources