User Profile
Patrick2788
Silver Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: 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.Re: How to sum chain values from N-number cells?
How about a generalized Lambda to handle this task? I've included a workbook with GroupbyBinλ. All you would need to do is move your data to this workbook and run the function. I've included a few examples to get started. Edit: it looks like this forum is still not accepting attachments. Here is the workbook: GroupbyBinλ Demo.xlsx For those interested, here's the function: // Author: Patrick H. // Date: 11/29/2025 // [Note: part of a larger module to include GroupbyDateλ, Histogramλ, Streakλ, // Modeλ, and more!] //--- GroupbyBinλ --- // // Description: // Aggregate one or more value arrays into fixed-width bins // derived from a numeric field. // // Parameters: // array - Numeric array (1D) used to define bin boundaries. // values - Array(s) to be aggregated (1D or 2D). // bin_width - Positive integer specifying bin width (≥1). // function - ETA-compatible function or single-parameter LAMBDA. // // Optional Parameters: // [field_header] - TRUE if input arrays include headers. // [chart_prep] - TRUE to return a concise chart-ready layout // (bin start/end joined into one column). // // Notes: // • If bin_width = 1, behaves like GROUPBY (no binning). // • If bin_width ≥ (max-min), collapses to a single bin. // • Chart mode produces {"Bin","Aggregate"} output for plotting. GroupbyBinλ= LAMBDA( array,values,bin_width,function,[field_header],[chart_prep], LET( //Dimensions i, ROWS(array), j, COLUMNS(array), i_,ROWS(values), j_, COLUMNS(values), //Header check Headers?, NOT(ISOMITTED(field_header)), //Halting scenarios Is2D?, AND(i>1,j>1), ShapeMismatch?, i<>i_, InvalidBin?, OR(ISTEXT(bin_width),bin_width<1), InvalidFn?, ISERROR(OR(BYROW(SEQUENCE(3,3,1,0),function))), NonNumeric?, NOT(AND(ISNUMBER(IF(Headers?,DROP(array,1),array)))), //Logic gate IF(Is2D?, "#1D-ARRAY!", IF(ShapeMismatch?, "#DIMENSIONS!", IF(NonNumeric?, #NUM!, IF(InvalidBin?, "#BIN-WIDTH!", IF(InvalidFn?, "#FUNCTION!", //Proceed LET( NoChart?, ISOMITTED(chart_prep), arr, IF(Headers?,DROP(array,1),array), val, IF(Headers?,DROP(values,1),values), //Assign headers row_header, HSTACK("Bin Start "&IF(Headers?,TAKE(array,1),"")&" Value","Bin End"), val_header, EXPAND(IF(Headers?,TAKE(values,1),"Total"),,j_,""), header, HSTACK(row_header,val_header), //Bin arrays min, MIN(arr), start, FLOOR(min,bin_width), max, MAX(arr), end, CEILING(max,bin_width), k, (end-start)/bin_width, bin, SEQUENCE(k+1,,start,bin_width), bin_start, DROP(bin,-1), bin_end, DROP(bin,1), //Bin assignments i, XMATCH(arr,bin_start,-1), row_start, INDEX(bin_start,i), row_end, INDEX(bin_end,i), row_fields, HSTACK(row_start,row_end), //Aggregation agg, GROUPBY(row_fields,val,function,,0), //Chart prep a, CHOOSECOLS(agg,1)&" - <"&CHOOSECOLS(agg,2), b, DROP(agg,,2), chart_labels, {"Bin","Aggregate"}, //Return modes data, VSTACK(header,agg), chart, VSTACK(chart_labels,HSTACK(a,b)), delivery, IF(NoChart?,data,chart), delivery ))))))));Re: Another issue with Value transformation.
For Excel 365, this will strip out just about anything (even stuff CLEAN can't handle). =--REGEXREPLACE(B6,"[^A-Za-z0-9 .]|[\x{00A0}\x{200B}-\x{200D}]|[\t\n\r]|[\x{1F600}-\x{1F64F}]","") This was part of a diagnostic lambda I had created called Revealλ which provided a cell-for-cell description of what Excel saw in each cell (e.g. Number, number stored as text, special character, etc.).Re: Advanced Formula Editor: converting functions Names into a module
I don't believe there's an elegant solution at the moment. You could scoop each function out of the name manager, place them in a cell (it will evaluate to a #CALC! error), and then import from grid from the Modules tab of AFE. I used to build functions through Names | Functions but the space is very limited. Everything begins and ends in the workbook module!Re: The Diagonal Suite: Gentle thunking goes a long way!
It looks like there's a bit more room for some gentle thunking to reduce calculation times even more. For example, with Traverseλ 'result' is deferred until the very end. I snuck the () in at the end so it's not needed at the sheet level. // --- Workbook module --- // A file of name definitions of the form: // name = definition; //Author: Patrick H. //Date: 10/27/2025 //Version: 1.0 //Repo: Please see The Diagonal Suite for a full suite of functions. //------------------------------------------------------------------------------------------- //Traverseλ - Directional Axis Remapper //------------------------------------------------------------------------------------------- //The selected axis is remapped to the top-left traversal order. //Accepted directions: // "NE" or 1 → Northeast (↗) // "SE" or 2 → Southeast (↘) // "SW" or 3 → Southwest (↙) //Parameters: //array → 2D input array (scalars not accepted) //new_axis → Axis direction ("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 but defer results until function invocation result, LAMBDA(INDEX(array,x(),y())), result ) ))()); This is fascinating in how a simple LAMBDA() tells Excel's formula parser to not read/fully evaluate a line and to treat it as "unrealized code". Timings: Array New axis Calc Time Calc Time - 1 more thunk 100000x100 SE 1.98 1.7Re: The Diagonal Suite: Gentle thunking goes a long way!
Thank you for taking a look! I've been taking a lot of my unfinished projects and rolling them into polished generalized functions lately. At one time I was determined to find an elegant way to unwrap a row of thunks, but I think you have that covered! (I'll need to revisit that module soon. My methods tend to change over time as I write more functions and learn new things.) The eye opener was messing with Spiralλ in thunking all sequencing and arithmetic wherever possible and watching the calc time go down. The analogy I like to think of with using thunks in this manner is buying food in bulk at the grocery store. It's not mentioned at checkout usually, but the cost of the bag's weight (tare weight) is subtracted from the cost of food when determining the price. The cost of using LET ("the bag") and not heavily-nested direct evaluation is slightly slower calc times, but we never notice it with small data sets. The gentle thunks give us back the tare weight.Re: The Diagonal Suite: Gentle thunking goes a long way!
I had considered including Traverseλ in the signature of ByDiagλ but pulled back because I thought anti-diagonal aggregations would not be common (and it would be one too many parameters). There seems to be a dearth of good diagonally arranged examples online (Unless I'm not searching with the right terms). I'll have a look at accounts receivable and depreciation schedules. Thank you!The Diagonal Suite: Gentle thunking goes a long way!
I've become a big advocate for gentle thunking - using thunks to delay eager evaluation wherever possible in generalized Lambda development. The timings are quicker and the logic is cleaner. On the other hand, thunking the results of MAP, BYROW, or BYCOL - especially when it leads to rows of thunks - tends to introduce recombination overhead and complexity. I think thunking is often dismissed as “too complex,” and that’s understandable if someone’s first exposure involves unwrapping a row of thunks. When used gently thunking becomes indispensable. Typically, I introduce the thunks after the initial benchmarking to see the difference in the calculation times and the after is always quicker. To illustrate, I’ll share The Diagonal Suite - a collection of functions where thunking is used at every opportunity. Simple, clean, deferred logic. What are your thoughts on gentle thunking? Where have you found it helpful/harmful in your own Lambda development? //The Diagonal Suite - Version 1.0 - 10/27/2025 //Author: Patrick H. //Description: Directional traversal and diagonal logic for 2D arrays. // Functions: // • Traverseλ - Directional traversal engine // • ByDiagλ - Diagonal-based aggregation // • DiagMapλ - Wrapper for diagonal matrix extraction // • DiagIndexλ - Targeted diagonal extraction // • Staircaseλ - Construct diagonal staircases from a vector or 2D array //──────────────────────────────────────────────────────────── //------------------------------------------------------------------------------------------- //Traverseλ - Directional Axis Remapper //------------------------------------------------------------------------------------------- //The selected axis is remapped to the top-left traversal order. //Accepted directions: // "NE" or 1 → Northeast (↗) // "SE" or 2 → Southeast (↘) // "SW" or 3 → Southwest (↙) //Parameters: //array → 2D input array (scalars not accepted) //new_axis → Axis direction ("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 ) ))); //------------------------------------------------------------------------------------------- //ByDiagλ - Diagonal-based aggregation //------------------------------------------------------------------------------------------- //Apply an ETA function or Lambda to diagonals //Parameters: //array → 2D input array (scalars not accepted) //[function] → ETA function or Lambda applied to diagonals //[row_wise_stack?] → Optional: Display results as a vertical stack ByDiagλ = LAMBDA( array, [function], [row_wise_stack?], //Check array input ValidateDiagλ(array,,function,row_wise_stack?, LET( //Optional parameters No_Function, ISOMITTED(function), No_row_wise_stack,ISOMITTED(row_wise_stack?), //Dimensions i, ROWS(array), j, COLUMNS(array), //Diagonal count k, MIN(i,j), //Indices - deferred r, LAMBDA(SEQUENCE(k)*SEQUENCE(,j,1,0)), y, LAMBDA(SEQUENCE(k)+SEQUENCE(,j,0,1)), c, LAMBDA(IF(y()>j,NA(),y())), //Unwrap indices, shape, and aggregate result, IFNA(INDEX(array,r(),c()),""), shaped, IF(No_row_wise_stack,result,TRANSPOSE(result)), final, IF(No_Function,shaped, IF(No_row_wise_stack,BYCOL(shaped,function), BYROW(shaped,function))), final ))); //------------------------------------------------------------------------------------------- //DiagMapλ - Wrapper (Calls ByDiagλ) to extract diagonals as 2D matrix //------------------------------------------------------------------------------------------- //Calls ByDiagλ to extract the diagonals from a 2D array. //Parameters: *Please see ByDiagλ for descriptions.** DiagMapλ = LAMBDA( array, [row_wise_stack?], ByDiagλ(array,,row_wise_stack?) ); //------------------------------------------------------------------------------------------- //DiagIndexλ - Targeted diagonal extraction //------------------------------------------------------------------------------------------- //Extract a diagonal or anti-diagonal vector from a 2D array. //Parameters: //array → 2D input array (scalars not accepted) //col_index → Column number to start from. Negative = anti-diagonal DiagIndexλ = LAMBDA( array, col_index, //Input checks ValidateDiagλ(array,col_index,,, LET( //Dimensions i, ROWS(array), j, COLUMNS(array), //Diagonal direction: +1 = SE, –1 = SW s, SIGN(col_index), //Determine diagonal length based on bounds k, IF(s>0, MIN(i, j + 1 - col_index), MIN(i, ABS(col_index))), start, IF(s<0,ABS(col_index),col_index), //Indices - deferred x, LAMBDA(SEQUENCE(k)), y, LAMBDA(SEQUENCE(k,,start,s)), //Unwrap indices and extract vector deliver, INDEX(array,x(),y()), deliver ))); //------------------------------------------------------------------------------------------- //Staircaseλ — Construct diagonal staircases from a vector or 2D array //------------------------------------------------------------------------------------------- //Parameters: //array → Input array (flattened to vector row-wise) //block_size → Number of rows/columns per staircase block //[block_offset] → Optional padding between staircases //[IsHorizontal?] → Optional toggle for column-wise orientation //[IsAntiDiag?] → Optional toggle to display staircase anti-diagonal. Staircaseλ = LAMBDA( array, block_size, [block_offset], [IsHorizontal?], [IsAntiDiag?], //Check inputs ValidateStaircaseλ(array,block_size,block_offset, LET( //Check optional parameters no_Block_Offset, ISOMITTED(block_offset), zero_Offset, block_offset=0, col_offset, IF(No_Block_Offset,0,block_offset), IsVertical?, ISOMITTED(IsHorizontal?), Not_Anti_Diag, ISOMITTED(IsAntiDiag?), //Convert to vector and get dimensions flat, TOCOL(array), k, COUNTA(flat), seq, LAMBDA(SEQUENCE(k)), V, TOROW(EXPAND(WRAPROWS(seq(),block_size),, block_size+block_offset,0)), width, COLUMNS(V), //Anchors and indices - deferred i, LAMBDA(SEQUENCE(block_size)*SEQUENCE(,width,1,0)), col_arr, LAMBDA(IF(Not_Anti_Diag,SEQUENCE(,width), SEQUENCE(,width,width,-1))), j, LAMBDA(MOD(col_arr(),block_size+block_offset)), j_, LAMBDA(IF((no_Block_Offset)+(zero_Offset), IF(j()=0,block_size,j()),j())), idx, LAMBDA(IF(i()=j_(),V,NA())), //Obtain results, shape, and calculate result, DROP(IFNA(INDEX(flat,idx()),""),,-col_offset), final, IF(IsVertical?,TRANSPOSE(result),result), final ))); //---------------------Error Handling & Validation--------------------------- //Validates inputs for Staircaseλ. Please see Staircaseλ for parameter //descriptions. ValidateStaircaseλ = LAMBDA( array, block_size, [block_offset], [on_valid], LET( //Checks NotArray,TYPE(array)<>64, Invalid_block_size, OR(ISTEXT(block_size),block_size<=0,block_size>COUNTA(array)), Invalid_block_offset, OR(ISTEXT(block_offset),block_offset<0), //Logic gate IF(NotArray, "#NOT-ARRAY!", IF(Invalid_block_size, "#BLOCK-SIZE!", IF(Invalid_block_offset,"#BLOCK-OFFSET", on_valid)))) ); //---------------------Error Handling & Validation--------------------------- //Validate inputs for ByDiagλ, DiagMapλ, and DiagIndexλ. //*Please see those functions for parameter descriptions.* ValidateDiagλ= LAMBDA( array, [col_index], [function], [row_wise_stack?], [on_valid], LET( //---Checks--- //Array input IsArray?, TYPE(array)=64, Not_Array, NOT(IsArray?), //Col_index No_Col_Index, ISOMITTED(col_index), Col_Index_Included, NOT(No_Col_Index), Not_Valid_Col_Index?, NOT(AND(col_index<>0, ABS(col_index)<=COLUMNS(array))), //Function No_Function, ISOMITTED(function), Function_Included, NOT(No_Function), Invalid_Function?, AND(ISERROR(BYROW({1,1},function))), //Shaping input RowWiseStack?, NOT(ISOMITTED(row_wise_stack?)), //Deterine which function is being validated DiagIndex, Col_Index_Included, ByDiag, AND(No_Col_Index, Function_Included), DiagMap, AND(No_Col_Index, No_Function), //Logic gates //DiagIndexλ a, IF(Not_Array, "#NOT-ARRAY!", IF(Not_Valid_Col_Index?,"#COLUMN-INDEX!", on_valid)), //ByDiagλ b, IF(Not_Array, "#NOT-ARRAY!", IF(Invalid_Function?, "#FUNCTION!", on_valid)), //DiagMapλ c, IF(Not_Array, "#NOT-ARRAY!", on_valid), //Logic gate selection decide, IF(DiagIndex,a, IF(DiagMap,c, IF(ByDiag,b, "#UNROUTED!"))), decide )); //End of The Diagonal Suite - Version 1.0 //Author: Patrick H.Re: Delete cells with exactly three syllables
I think this is what you're looking to do (the 3 consonants don't have to be consecutive): =BYROW(words,LAMBDA(w,COUNTA(REGEXEXTRACT(w,"[^aeiou]",1,1))=3)) Regexextract checks for anything not a vowel (case insensitive) and COUNTA is there to count the consonants. The function is applied with BYROW. You could then filter for TRUE and delete as needed.Re: Double Thunking Works Wonders!
I've re-visited this task determined to pull this off with a bit more elegance. BlockMapλ is the result: // Function: BlockMapλ // Author: Patrick H. // Version: 1.0 // Published: 10/3/2025 // Repo: https://github.com/Patrick2788/Excel-Lambda/blob/main/BlockMap%CE%BB.xlsx // Description: BlockMapλ reshapes a matrix row or column-wise by specified // block size (depth x width) with optional parameters for // shaping by column and transposing anchors. BlockMapλ = LAMBDA( matrix, //Input matrix depth, //Height of each block width, //Width of each block [re_shape_by_column?], //Optional: If TRUE, reshapes column-wise; else row-wise [transpose_anchors?], //Optional: If TRUE, transpose row and col anchors; else normal anchors //Validate inputs Validateλ(matrix,depth,width,re_shape_by_column?, //Proceed LET( //----------Re-shape logic--------------------------------- x,ROUNDUP(ROWS(matrix)/depth,0), //Block rows y,ROUNDUP(COLUMNS(matrix)/width,0), //Block columns //Anchors i,SEQUENCE(x,,1,depth)*SEQUENCE(,y,1,0), j,SEQUENCE(,y,1,width)*SEQUENCE(x,,1,0), row_anchor,IF(ISOMITTED(transpose_anchors?),i,TRANSPOSE(i)), col_anchor,IF(ISOMITTED(transpose_anchors?),j,TRANSPOSE(j)), //Indices row_indices,TOCOL(row_anchor)+INT(SEQUENCE(,depth*width,0,1/(width))), col_indices,TOCOL(col_anchor)+TOROW(IFS(SEQUENCE(depth),SEQUENCE(,width,0,1))), //Output ReShapedMatrix,INDEX(matrix,row_indices,col_indices), output_matrix,IF(re_shape_by_column?,TRANSPOSE(ReShapedMatrix),ReShapedMatrix), output_matrix ))); //----------Error Handling--------------------------------- //Validate inputs for BlockMapλ. Validateλ = LAMBDA( matrix, depth, width, re_shape_by_column?, on_valid, //Halt if not an array. IF(NOT(TYPE(matrix)=64),"#MATRIX!", //Halt if re_shape_by_col? is TRUE and result would return #SPILL! error. IF((re_shape_by_column?)*(ROWS(matrix)*(COLUMNS(matrix))/(width*depth)^2>16384),"#SPILL-RISK!", //Halt if result would not be a clean re-shaping. IF((MOD(ROWS(matrix), depth) <> 0) + (MOD(COLUMNS(matrix),width)<>0),"#BLOCK MISMATCH!", //Halt if block size exceeds matrix dimensions or is text. IF(OR(AND(width=1,depth=1), depth>ROWS(matrix),width>COLUMNS(matrix), ISTEXT(width),ISTEXT(depth)),"#DIMENSIONS!", on_valid )))))Re: Delete cells with exactly three syllables
This is what it would take to solve in Excel. Pull together a list of the most common syllables found in English. Pull together a list of the most common words (or words most relevant to your data) Create all possible permutations (with repeats) from the list. Discard words which do not match words in your dictionary Discard words with precisely 3 syllables. My advice is to use the best version of Copilot at: https://copilot.microsoft.com/ You can fit about 12,000 characters in a single chat post.
