User Profile
Patrick2788
Silver Contributor
Joined Oct 18, 2019
User Widgets
Recent Discussions
Re: Error Opening the Excel
What's the size of the file? If the file size is very small it could be hosed. Some other things to try: Re-name the file to be .zip and attempt to open with File Explorer. If the file is a valid .xlsx, you'll be able to pick through the XML. Re-name the file to be .txt and open with notepad. If there's anything of substance left in the file then it will show it to you in notepad (Even if it's mixed in with some symbols).Re: Excel 365 is UNUSABLE for professional work — performance has catastrophically degraded
You've included a lot of good details and have tried quite a few solutions already. One thing you have not yet included is a typical sample workbook with anonymized data. I understand you may not want to upload a workbook with confidential information and anonymizing the data can be a chore. My suggestion: run Inquire's Workbook Analysis to audit a workbook and share the .xlsx here. This will provide an extensive report about the workbook but not include the data. — Moving a simple shape (a circled number annotation) across a worksheet takes SECONDS. This is not a complex object. It is a circle with a number in it. — Scrolling is laggy and jerky, even on sheets with minimal data. — Pressing ALT to activate ribbon shortcuts has a delay so severe that subsequent keystrokes are DROPPED. I have to sit and wait for your UI to catch up before I can use keyboard shortcuts I've relied on for over a decade. — Selecting and editing cells is noticeably slower than it was two years ago. — Interacting with comments is painfully slow. — Chart objects are sluggish to select, move, or resize. These are classic rendering issues. Others have already suggested checking the selection pane for object count (or use the immediate window). The entire workbook can be combed to check this pane for each sheet. It only takes 1 sheet with a few thousand objects to slow the navigation. I do use OFFSET, INDEX/MATCH, among others. OFFSET is best avoided in 365 because there are better options. INDEX/MATCH can usually be done with XLOOKUP. A full audit of a workbook with Inquire will list all the formulas in a workbook and give us an idea of how your workbook is setup.Re: Looking for use cases of function TYPE
That's the short of it. The ability to supply functions like this ETA arrangement for example: =BYROW(array,SUM) Is a recent change from how things have been for a long time. This same ETA arrangement can be used in Lambdas and that's where TYPE comes in to check the input.Re: Looking for use cases of function TYPE
TYPE is helpful for two things mostly: 64 - array detection (can also use ROWS and COLUMNS for dimension check) 128 - compound data This is a basic example of using TYPE to validate the function input of a Lambda: MyLambda= LAMBDA( array, [function], LET( // Check optional function input, default to SUM if omitted function, IF(ISOMITTED(function),SUM,function), // Compound data - TYPE = 128 - valid function InValidFn?, TYPE(function) <> 128, // Halt function if supplied function is invalid IF(InValidFn?,"#INVALID-FUNCTION!",function(array)))); // Sheet level formula: =MyLambda(arr,AVERAGE)Re: Formula Challenge: The most efficient way to generate a Number Spiral (Ulam spiral) ?
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() ));Re: Formula Challenge: The most efficient way to generate a Number Spiral (Ulam spiral) ?
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-PlaygroundRe: Excel array question - spill VOWD based on forecast month
My approach is to use a mesh grid of column coordinates and pick out what you need. MGridλ is a generalized function that returns row or column numbers. Here it's returning column numbers. =LET( j, MGridλ(MonthlyData, 1), mdata, IF(j <= ForeMonth, MonthlyData, 0), totals, BYROW(mdata, SUM), totals ) This is what it looks like step-by-step:Re: WrapRows2Dλ / WrapCols2Dλ: Fast, efficient 2D wrapping without flattening
From the start my approach was to avoid flattening the input array. I have several other functions in my library that use pure i/j indexing and the times are always fast (Especially when thunked. Excel will not evaluate 'i' or 'j' twice but only when used within INDEX(array,i(),j() for example ). I try to avoid Lambda helpers for re-shaping.WrapRows2Dλ / WrapCols2Dλ: Fast, efficient 2D wrapping without flattening
Background One of Excel's biggest weaknesses is in working with 2D arrays as objects that can be re-shaped. WRAPROWS/WRAPCOLS do not accept 2D arrays (#VALUE!) and are strictly for shaping 1D arrays. The usual workarounds involve flattening with TOROW/TOCOL then re-shaping with WRAPROWS/WRAPCOLS, REDUCE used as an iterator to stack (do-able but slow), and even MAKEARRAY (do-able, but not instinctive and slow). The Goal Fast, efficient 2D wrapping without flattening. The Approach Pure deferred i/j indexing with modular math and sequencing. The function and sample workbook is provided below. I welcome any and all feedback: suggestions for improvement, your approach to 2D shaping, etc. // Fast, efficient 2D wrapping without flattening //----------------------------------------------------------------------------------- //---WrapCols2Dλ--- //----------------------------------------------------------------------------------- //Author: Patrick H. //Date: 1/28/2026 //Version: 1.0 // //Description: //Wrap a 2D array into column blocks of a specified width while preserving row height. //The wrapped blocks are stacked vertically in the output. //Jagged or uneven final blocks are padded with NA() by default, unless a fill value //is supplied via the optional pad_with parameter. // //----------------------------------------------------------------------------------- //Parameter Description //array - 2D array to be wrapped (1D arrays not supported) //new_width - Number of columns in each wrapped block // //Optional Description //pad_with - Fill value used to pad incomplete blocks. If omitted, NA() is used. // //Lambda called: Echoλ WrapCols2Dλ= LAMBDA( array, new_width, [pad_with], //Check inputs LET( //Shape h, ROWS(array), w, COLUMNS(array), blocks, CEILING(w/new_width,1), //Optional pad_with, IF(ISOMITTED(pad_with),NA(),pad_with), //Total rows when wrapped r, blocks * h, //Scenarios Is1D?, OR(h = 1,w = 1), IsScalar?, AND(h = 1, w = 1), InvalidDim?,new_width >= w, SpillRisk?, r > 1048576, //Logic gate IF(IsScalar?,#VALUE!, IF(Is1D?,#VALUE!, IF(InvalidDim?,"#WIDTH!", IF(SpillRisk?,#NUM!, //Proceed LET( //Indices - deferred modulo, LAMBDA(MOD(SEQUENCE(r),h)), i, LAMBDA(IF(modulo() = 0, h, modulo()) * SEQUENCE(,new_width,1,0)), j, LAMBDA(Echoλ(SEQUENCE(r / h,,1,new_width),h) + SEQUENCE(,new_width,0,1)), //Wrapped array result, IFERROR(INDEX(array,i(),j()),pad_with), result ))))))); //----------------------------------------------------------------------------------- //---WrapRows2Dλ--- //----------------------------------------------------------------------------------- //Author: Patrick H. //Date: 1/28/2026 //Version: 1.0 // //Description: //Wrap a 2D array into row blocks of a specified height while preserving column width. //The wrapped blocks are stacked horizontally in the output. //Jagged or uneven final blocks are padded with NA() by default, unless a fill value //is supplied via the optional pad_with parameter. // //----------------------------------------------------------------------------------- //Parameter Description //array - 2D array to be wrapped (1D arrays not supported) //new_height - Number of rows in each wrapped block // //Optional Description //pad_with - Fill value used to pad incomplete blocks. If omitted, NA() is used. // //Lambda called: Echoλ WrapRows2Dλ= LAMBDA( array, new_height, [pad_with], //Check inputs LET( //Shape h, ROWS(array), w, COLUMNS(array), blocks, CEILING(h/new_height,1), //Optional pad_with, IF(ISOMITTED(pad_with),NA(),pad_with), //Total columns when unwrapped c, blocks * w, //Scenarios Is1D?, OR(h = 1,w = 1), IsScalar?, AND(h = 1, w = 1), InvalidDim?,new_height >= h, SpillRisk?, c > 16384, //Logic gate IF(IsScalar?,#VALUE!, IF(Is1D?,#VALUE!, IF(InvalidDim?,"#HEIGHT!", IF(SpillRisk?,#NUM!, //Proceed LET( //Indices - deferred i, LAMBDA(TOROW(Echoλ(SEQUENCE(,blocks,1,new_height),w)) + SEQUENCE(new_height,,0,1)), modulo, LAMBDA(MOD(SEQUENCE(,w * blocks),w)), j, LAMBDA(IF(modulo()=0,w,modulo()) * SEQUENCE(new_height,,1,0)), //Wrapped array result, IFERROR(INDEX(array,i(),j()),pad_with), result ))))))); //----------------------------------------------------------------------------------- //Echoλ //----------------------------------------------------------------------------------- //Author: Patrick H. //Date: 11/7/2025 //Version: 1.0 //Description: //Repeat each element in a supplied 1D array by specifying the repeat counts. //Arrays and scalars are supported. //----------------------------------------------------------------------------------- //vector - 1D array or scalar to be echoed //repeat - 1D array of repeat counts (must be numeric and ≥1) Echoλ = LAMBDA( vector, repeat, //Check inputs IF(OR(ISTEXT(repeat),repeat<=0),#VALUE!, LET( //Flatten inputs vector, TOCOL(vector), repeat, TOCOL(repeat), //Dimensions and row indexing V↕, ROWS(vector), R↕,ROWS(repeat), r, IF(V↕<>R↕,EXPAND(repeat,V↕,,@TAKE(repeat,-1)), repeat), i, SEQUENCE(ROWS(r)), m, MAX(r), idx, LAMBDA(TOCOL(IF(SIGN(r-SEQUENCE(,m,0,))=1,i,NA()),2)), //Unwrap idx but defer delivery until function invocation deliver, LAMBDA(INDEX(vector,idx())), deliver ))()); Workbook attached and linked in case this forum gobbles it up! Patrick2788/Excel-Lambda: Excel Lambda modules Excel-Lambda/Wrap2D Demo.xlsx at main · Patrick2788/Excel-Lambda Excel Lambda modules. Contribute to Patrick2788/Excel-Lambda development by creating an account on GitHub. github.comRe: How does Excel calculate named formulas and materialized named ranges?
Excel favors eager evaluation in most situations. In your two examples, the timings were about the same. The example using trim range was the quickest. ManualAllocRng = OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5); // Average of 5 timings // Elements 1.25 million // TestAλ 0.21 // TestBλ 0.21 // TestCλ 0.18 TestAλ = LAMBDA( x, LET( rng, ManualAllocRng, a, DROP(rng,,2), b, TAKE(rng,,-2), result, HSTACK(a,b)+x, result )); TestBλ = LAMBDA( x, LET( a, DROP(ManualAllocRng,,2), b, TAKE(ManualAllocRng,,-2), result, HSTACK(a,b)+x, result )); TestCλ= LAMBDA( x, LET( rng, Sheet1!$A:.$E, a, DROP(rng,,2), b, TAKE(rng,,-2), result, HSTACK(a,b)+x, result)) The way in which the first two examples are authored may have an effect on the calculation speed, but it really depends on what you're doing with the arrays. Excel seems to prioritize allocating memory to spill re-sizing but it's not easy to re-produce the situation. I've seen functions take 12 seconds to calculate even with the function terminating within an IF even before the LET block comes in. There are ways to stop the eager evaluation and even speed up functions with some well-placed thunks. In the examples A/B above, there were no differences in the calc speed.
