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 ...
I thought it would be interesting to use the Ulam Spiral to demonstrate 2D element-wise rotation within rings.
I was curious what this solution would look like in Excel. The most practical function used within is simple ring extraction from a 2D array. I can't think of any other use cases for the rotational parts. It was something I had visualized and had to complete it regardless of practicality.
// Author: Patrick H.
// Date: 2/26/2026
// Title: Rotate2Dλ Demonstration
/*
This demonstration illustrates how Excel’s formula language is expressive
enough to implement a full 2D rotational engine - a small example of
Excel’s computational completeness.
The larger task can be broken into smaller, solvable tasks:
Extract ring by layer from a 2D grid - Ringλ
|
|
Rotate each ring with recursion (Clockwise or Reverse) RingRotateλ, RingRotateRevλ
|
|
Iterate through all rings in a 2D array and rotate - Rotate2Dλ
** Rotate2Dλ calls RingRotateλ or RingRotateRevλ, which call Ringλ **
Examples:
A ring is a perimeter layer of the grid: the outermost cells form ring 1,
the next layer inward is ring 2, and so on.
Letters is comprised of 3 rings
A B C D E
F G H I J
K L M N O
P Q R S T
U V W X Y
Clockwise rotation by ring
=Rotate2Dλ(Letters,1)
F A B C D
K L G H E
P Q M I J
U R S N O
V W X Y T
Reverse rotation by ring
=Rotate2Dλ(Letters,1,1)
B C D E J
A H I N O
F G M S T
K L Q R Y
P U V W X
*/
//-----------------------------------------------------------------------------------
// Rotate2Dλ
//-----------------------------------------------------------------------------------
// Author: Patrick H.
// Date: 2/18/2026
// Version: 1.0
//
// Description:
// Rotates all layers (rings) of a 2D grid by a specified number of rotations,
// either clockwise or counterclockwise. Rotation counts are optimized based on
// the perimeter of the outer ring so that no more rotation steps are performed
// than mathematically necessary.
//
// Example:
// For a 10×10 grid with 500 requested rotations:
//
// MOD(500, (10*2 + 10*2) - 4) = 32
//
// Only 32 effective rotations are required to produce the final state.
//
// Parameters:
// grid – Required 2D array representing the full grid.
// [rotations] – Optional number of rotations. Defaults to 1 if omitted.
// Negative or non-numeric values are treated as invalid.
// [reverse] – Optional Boolean. If TRUE, rotates each ring in the reverse
// (counterclockwise) direction. If omitted, rotation is clockwise.
//
// Notes:
// • This wrapper applies rotation optimization globally. Each ring receives the
// same effective rotation count.
// • Literal rotation behavior is handled by RingRotateλ and RingRotateRevλ.
// • Degenerate rings (1×N, N×1, 2×N, etc.) are safely handled within the ring
// rotation functions via Ringλ and Squeezeλ.
//
// Lambdas called:
// RingRotateλ, RingRotateRevλ
// (Ringλ and Squeezeλ are called internally by those functions.)
Rotate2Dλ=
LAMBDA(
grid,
[rotations],
[reverse],
LET(
//Dimensions
h, ROWS(grid), w, COLUMNS(grid),
//Set rotation count
rotations, IF(ISOMITTED(rotations),1,rotations),
//No-rotation scenarios
IsScalar?, AND(h = 1,w = 1),
IsID?, XOR(h = 1, w = 1),
//Halting scenario
InvalidRot?, OR(ISTEXT(rotations),rotations <= 0),
//Logic gate
IF(IsScalar?,grid,
IF(IsID?,grid,
IF(InvalidRot?,"#ROTATION!",
LET(
//How many rotations are needed?
passes, MOD(rotations,(h*2 + w*2) - 4),
//Set rotation function
IsClockwise?, OR(ISOMITTED(reverse),reverse = FALSE),
Rotateλ, IF(IsClockwise?,RingRotateλ,RingRotateRevλ),
//Ring count layers
layers, (MIN(h,w) + 1) / 2,
k, SEQUENCE(layers),
//Rotate each layer of 2D array
Iterateλ, LAMBDA(acc,each_layer,Rotateλ(acc,each_layer,passes)),
rotated, REDUCE(grid,k,Iterateλ),
rotated
))))));
//-----------------------------------------------------------------------------------
// RingRotateRevλ
//-----------------------------------------------------------------------------------
// Author: Patrick H.
// Date: 2/18/2026
// Version: 1.0
//
// Description:
// Rotates a single ring (layer) of a 2D grid in the reverse (counterclockwise)
// direction while preserving the grid’s shape. The function accepts a source
// grid, a target layer index, and an optional rotation count. The returned grid
// matches the dimensions of the input grid.
//
// Parameters:
// grid – Required 2D array representing the full grid.
// layer – Integer ≥ 1 indicating which ring to rotate.
// For an N×M grid, layer 1 is the outermost ring.
// Layers exceeding the innermost ring return the grid unchanged.
// [rotations] – Optional number of counterclockwise rotations.
// Defaults to 1 if omitted. Negative or non-numeric values
// are treated as invalid.
//
// Notes:
// • This function performs literal rotation steps; it does not optimize rotation
// counts. Optimization is handled by the Rotate2Dλ wrapper.
// • Ring extraction is performed via Ringλ, which ensures correct handling of
// degenerate rings (1×N, N×1, 2×N, etc.).
// • Clockwise rotation is implemented in the companion function RingRotateλ.
//
// Lambdas called:
// Ringλ, Squeezeλ
RingRotateRevλ=
LAMBDA(
grid,
layer,
[rotations],
LET(
//Set rotations and validate
rotations, IF(ISOMITTED(rotations),1,rotations),
InvalidRot?, OR(ISTEXT(rotations),rotations < 0),
//Mesh grid
i, MGridλ(grid), j, MGridλ(grid,1),
//Max dimensions
h, ROWS(grid), w, COLUMNS(grid),
//Ring extracted
ring, Ringλ(grid,layer,""),
//Ring without padding to detect 1D layers
squeezed, Squeezeλ(ring,""),
//No rotation scenarios
Is1D?, OR(ROWS(squeezed) = 1,COLUMNS(squeezed) = 1),
Two_x_Two, AND(ROWS(ring) = 2, COLUMNS(ring) = 2),
traversed, IF(Two_x_Two,Traverseλ(ring,3),FALSE),
IF(InvalidRot?,"#ROTATIONS!",
IF(Is1D?,grid,
IF(Two_x_Two,traversed,
LET(
//Identify ring parts for i/j offsets
occupied?, LEN(ring) <> 0,
//Clockwise masks
top, occupied? * (i = layer) * (j <= w - layer),
left, occupied? * (j = layer) * (i > layer) * (i <= h - layer + 1),
right, occupied? * (j = w - layer + 1) * (i >= layer) * (i < h - layer + 1),
bottom, occupied? * (i = h - layer + 1) * (j > layer) * (j <= w - layer + 1),
//Local navigation function ↑↓←
Decideλ, LAMBDA(
logic1,true1,
logic2,true2,
logic3,true3,
logic4,true4,
fallback, // 0 for non-ring elements
IF(logic1,true1,IF(logic2,true2,IF(logic3,true3,IF(logic4,true4,fallback))))),
//Indices
r, i + Decideλ(top,0,
bottom,0,
left,-1,
right,1,0),
c, j + Decideλ(top,1,
bottom,-1,
left,0,
right,0,0),
rotated, INDEX(grid,r,c),
IF(rotations = 0,grid, RingRotateRevλ(rotated,layer,rotations - 1)
)))))));
//-----------------------------------------------------------------------------------
// RingRotateλ
//-----------------------------------------------------------------------------------
// Author: Patrick H.
// Date: 2/18/2026
// Version: 1.0
//
// Description:
// Rotates a single ring (layer) of a 2D grid while preserving the grid’s shape.
// The function accepts a source grid, a target layer index, and an optional
// rotation count.
//
// Parameters:
// grid – Required 2D array
// layer – Integer ≥ 1 indicating which ring to rotate.
// For an N×M grid, layer 1 is the outermost ring.
// Layers exceeding the innermost ring return the grid unchanged.
// [rotations] – Optional number of clockwise rotations. Defaults to 1 if omitted.
// Negative or non-numeric values are treated as invalid.
//
// Notes:
// • This function performs literal rotation steps; it does not optimize rotation
// counts. Optimization is handled by the Rotate2Dλ wrapper.
// • Ring extraction is performed via Ringλ, which ensures correct handling of
// degenerate rings (1×N, N×1, 2×N, etc.).
// • Reverse rotation is implemented in the companion function RingRotateRevλ.
//
// Lambdas called:
// Ringλ, Squeezeλ
RingRotateλ=
LAMBDA(
grid,
layer,
[rotations],
LET(
//Set rotations and validate
rotations, IF(ISOMITTED(rotations),1,rotations),
InvalidRot?, OR(ISTEXT(rotations),rotations < 0),
//Mesh grid
i, MGridλ(grid), j, MGridλ(grid,1),
//Max dimensions
h, ROWS(grid), w, COLUMNS(grid),
//Ring extracted
ring, Ringλ(grid,layer,""),
//Ring without padding to detect 1D layers
squeezed, Squeezeλ(ring,""),
//No rotation scenarios
Is1D?, OR(ROWS(squeezed) = 1,COLUMNS(squeezed) = 1),
Two_x_Two, AND(ROWS(ring) = 2, COLUMNS(ring) = 2),
traversed, IF(Two_x_Two,Traverseλ(ring,3),FALSE),
IF(InvalidRot?,"#ROTATIONS!",
IF(Is1D?,grid,
IF(Two_x_Two,traversed,
LET(
//Identify ring parts for i/j offsets
occupied?, LEN(ring) <> 0,
//Clockwise masks
top, occupied? * (i = layer) * (j > layer) * (j <= w - layer + 1),
left, occupied? * (j = layer) * (i >= layer) * (i < h - layer + 1),
right, occupied? * (j = w - layer + 1) * (i >= layer + 1) * (i <= h - layer + 1),
bottom, occupied? * (i = h - layer + 1) * (j >= layer) * (j < w - layer + 1),
//Local navigation function ↑↓←
Decideλ, LAMBDA(
logic1,true1,
logic2,true2,
logic3,true3,
logic4,true4,
fallback, // 0 for non-ring elements
IF(logic1,true1,IF(logic2,true2,IF(logic3,true3,IF(logic4,true4,fallback))))),
//Indices
r, i + Decideλ(top,0,
bottom,0,
left,1,
right,-1,0),
c, j + Decideλ(top,-1,
bottom,1,
left,0,
right,0,0),
rotated, INDEX(grid,r,c),
IF(rotations = 0,grid,RingRotateλ(rotated,layer,rotations - 1))
))))));
//-----------------------------------------------------------------------------------
// Ringλ
//-----------------------------------------------------------------------------------
// Author: Patrick H.
// Date: 2/17/2026
// Version: 1.0
//
// Description:
// Extracts a single ring (layer) from a 2D grid while preserving the overall
// shape of the grid. The ring is identified by its layer index, where layer 1
// corresponds to the outermost perimeter and higher layers move inward. The
// returned array matches the dimensions of the input grid, with all non-ring
// cells filled using the specified fill value.
//
// Parameters:
// grid – Required 2D array representing the full grid.
// layer – Integer ≥ 1 specifying which ring to extract.
// For an N×M grid, layer 1 is the outermost ring.
// Layers exceeding the innermost ring return an empty array.
// [fill_value] – Optional scalar used to fill all non-ring cells in the output.
// Defaults to 0 if omitted.
//
// Notes:
// • This function performs no rotation; it only extracts the ring mask.
// • Degenerate rings (1×N, N×1, 2×N, etc.) are preserved exactly as extracted.
// • RingRotateλ and RingRotateRevλ rely on Ringλ to correctly identify ring
// boundaries before performing rotation.
// • The output always matches the dimensions of the input grid.
//
// Used by:
// RingRotateλ, RingRotateRevλ
Ringλ=
LAMBDA(
grid,
layer,
[fill_value],
LET(
//Dimensions
h, ROWS(grid), w, COLUMNS(grid),
//Validation check
IsScalar?, AND(h = 1, w = 1),
Is1D?, OR(h = 1, w = 1),
InvalidLayer?, OR(ISTEXT(layer),layer <= 0),
MaxLayer, INT((MIN(h,w) + 1) / 2),
//Logic gate
IF(IsScalar?,grid,
IF(Is1D?,"#1D-ARRAY!",
IF(InvalidLayer?,"#LAYER!",
IF(Layer > MaxLayer,"#MAX-LAYER: "&MaxLayer,
LET(
//Optional fill value
fill_value, IF(ISOMITTED(fill_value),0,fill_value),
//Mesh grid
i, MGridλ(grid), j, MGridλ(grid,1),
//Edges of ring
top, ((i = layer) + (i = 1 + h - layer)) *
((j > layer - 1) * (j <= 1 + w - layer)) ,
side, ((j = layer) + (j = 1 + w - layer)) *
((i > layer - 1) * (i <= 1 + h - layer)),
// 1/0 ring mask
mask, SIGN(top + side),
final, IF(mask,grid,fill_value),
final
)))))));
//---Utilities---
//-----------------------------------------------------------------------------------
// MGridλ
//-----------------------------------------------------------------------------------
// Author: Patrick H.
// Date: 2026‑02‑09
// Version: 1.0
//
// Description:
// Generates row‑index or column‑index grids matching the shape of a supplied
// array. This is the Excel equivalent of MATLAB’s meshgrid or NumPy’s indices.
// By default, MGridλ returns row indices. When the optional parameter is TRUE,
// it returns column indices instead.
//
// Parameters:
// grid – A 1D or 2D array whose dimensions determine the output size.
// [col_index] – If TRUE, return column indices. If omitted, return row indices.
//
// Example:
// Input grid:
// {1,2,3;
// 4,5,6;
// 7,8,9}
//
// =MGridλ(grid)
// {1,1,1;
// 2,2,2;
// 3,3,3}
//
// =MGridλ(grid, TRUE)
// {1,2,3;
// 1,2,3;
// 1,2,3}
//
// Notes:
// • Uses Resizeλ to broadcast SEQUENCE vectors across the grid dimensions.
// • Works for both 1D and 2D arrays.
// • Output always matches the geometry of the input grid.
//-----------------------------------------------------------------------------------
MGridλ=
LAMBDA(
grid,[col_index],
LET(
//Return mode
RowIdx?, ISOMITTED(col_index),
//Grid dimensions
h, ROWS(grid), w, COLUMNS(grid),
//Deferred index grids
i, LAMBDA(Resizeλ(SEQUENCE(h),,w)),
j, LAMBDA(Resizeλ(SEQUENCE(,w),h)),
//Select row or column index grid
idx, IF(RowIdx?,i(),j()),
idx
));
//-----------------------------------------------------------------------------------
// Resizeλ
//-----------------------------------------------------------------------------------
// Author: Patrick H.
// Date: 2/18/2026
// Version: 1.0
//
// Description:
// Repeats a 2D array vertically and/or horizontally by integer repeat counts.
// Users may specify height, width, or both. If either parameter is omitted,
// the omitted dimension defaults to 1. Geometry is preserved; no flattening
// occurs.
//
// Example:
// Input:
// {1,2;
// 3,4}
//
// =Resizeλ(array, 2, 3)
//
// Output:
// {1,2,1,2,1,2;
// 3,4,3,4,3,4;
// 1,2,1,2,1,2;
// 3,4,3,4,3,4}
//
// Parameters:
// array – Required 2D array to be repeated.
// [height] – Optional vertical repeat count. Defaults to 1.
// [width] – Optional horizontal repeat count. Defaults to 1.
//
// Notes:
// • Repetition is performed via index remapping, not concatenation.
// • Output geometry is (h × height) by (w × width).
//-----------------------------------------------------------------------------------
Resizeλ=
LAMBDA(
array,[height],[width],
LET(
//Dimensions
h, ROWS(array), w, COLUMNS(array),
//Omission checks
HeightOmitted?, ISOMITTED(height),
WidthOmitted?, ISOMITTED(width),
NoHWInput?, HeightOmitted? + WidthOmitted? = 2,
//Default repeat counts
height, IF(OR(NoHWInput?,HeightOmitted?),1,height),
width, IF(OR(NoHWInput?,WidthOmitted?),1,width),
//Scenarios
NoRepeat?, AND(height= 1,width = 1),
IsScalar?, AND(h = 1, w = 1),
ExpandScalar, EXPAND(array,height,width,array),
InvalidRepeat?, OR(ISTEXT(height),ISTEXT(width),
OR(AND(ISNUMBER(height),height <= 0),
AND(ISNUMBER(width),width <= 0))),
//Logic gate
IF(NoRepeat?,array,
IF(IsScalar?,ExpandScalar,
IF(InvalidRepeat?,array,
//Proceed
LET(
//Expanded dimensions
k, h * height,
k₂, w * width,
//Indices - deferred
i, LAMBDA(1 + MOD(SEQUENCE(k,,0,),h) * SEQUENCE(,k₂,1,0)),
j, LAMBDA(1 + MOD(SEQUENCE(,k₂,0),w) * SEQUENCE(k,,1,0)),
//Construct repeated array
new_arr, INDEX(array,i(),j()),
new_arr
))))));
//Author: Patrick H.
//Date: 10/27/2025
//Version: 1.0
//Repo: Please see The Diagonal Suite for a full suite of functions.
//-------------------------------------------------------------------------------------------
//Traverseλ - counter_clockwise Axis Remapper
//-------------------------------------------------------------------------------------------
//The selected axis is remapped to the top-left traversal order.
//Accepted counter_clockwises:
// "NE" or 1 → Northeast (↗)
// "SE" or 2 → Southeast (↘)
// "SW" or 3 → Southwest (↙)
//Parameters:
//array → 2D input array (scalars not accepted)
//new_axis → Axis counter_clockwise ("NE", "SE", "SW" or 1–3)
Traverseλ =
LAMBDA(
array,
new_axis,
//Input validation
IF(OR(ROWS(array)=1,COLUMNS(array)=1), "#2D-ARRAY!",
IF(AND(ISNUMBER(new_axis),OR(new_axis<=0,new_axis>3)),"#AXIS!",
LET(
//Dimensions
i, ROWS(array),
j, COLUMNS(array),
//Axis traversal indices (deferred)
x_NE, LAMBDA(SEQUENCE(j,,1,0)*SEQUENCE(,i)),
y_NE, LAMBDA(SEQUENCE(j,,j,-1)*SEQUENCE(,i,1,0)),
x_SE, LAMBDA(SEQUENCE(i,,i,-1)*SEQUENCE(,j,1,0)),
y_SE, LAMBDA(SEQUENCE(i,,j,0)+SEQUENCE(,j,0,-1)),
x_SW, LAMBDA(SEQUENCE(j,,i,0)+SEQUENCE(,i,0,-1)),
y_SW, LAMBDA(SEQUENCE(j,,1)*SEQUENCE(,i,1,0)),
//Axis mode selection
mode, IF(ISNUMBER(new_axis),new_axis,
SWITCH(new_axis,"NE",1,"SE",2,"SW",3,1)),
//Index selection
x, CHOOSE(mode,x_NE,x_SE,x_SW),
y, CHOOSE(mode,y_NE,y_SE,y_SW),
//Unwrap indices and get results
result, INDEX(array,x(),y()),
result
))));
//-----------------------------------------------------------------------------------
// Squeezeλ
//-----------------------------------------------------------------------------------
// Description:
// Removes null and straight zero vertical or horizontal vectors from a 2D array.
// Supports optional custom criteria for removal.
//-----------------------------------------------------------------------------------
// Parameters:
// array - array (1D or 2D) where rows or columns are removed.
// Optional parameters:
// custom_criteria - scalar criteria for removal of rows or columns.
// suppress_zeros - Set to TRUE will replace 0s with empty "" in final result.
//-----------------------------------------------------------------------------------
Squeezeλ=
LAMBDA(
array,
[custom_criteria],
[suppress_zeros],
LET(
HideZero?, NOT(ISOMITTED(suppress_zeros)),
//Shape
i, SEQUENCE(ROWS(array)), j, SEQUENCE(,COLUMNS(array)),
//Retain non-blank rows, columns. Straight zero vectors are purged.
Keepλ, LAMBDA(v,NOT(AND(TOCOL(v,2)=0))),
//Custom criteria
Customλ, LAMBDA(v,NOT(AND(TOCOL(v,2)=custom_criteria))),
//Function selectino
fn, IF(ISOMITTED(custom_criteria),Keepλ,Customλ),
a, TOCOL(IF(BYROW(array,fn),i,NA()),2),
b, TOROW(IF(BYCOL(array,fn),j,NA()),2),
IsEmpty?, AND(ISERROR(a),ISERROR(b)),
//Reset shape
x, ROWS(a),
y, COLUMNS(b),
//Mesh grid
r, a * SEQUENCE(,y,1,0),
c, b * SEQUENCE(x,,1,0),
//Get results, suppress 0s
result, INDEX(array,r,c),
result_b, IF(result=0,"",result),
deliver, IF(IsEmpty?,"#EMPTY-ARRAY!",IF(HideZero?,result_b,result)),
deliver
));Attaching a link in case the attachment is zapped: Excel-Lambda-Playground/Rotate2Dλ Demo.xlsx at main · Patrick2788/Excel-Lambda-Playground