User Profile
Patrick2788
Silver Contributor
Joined 7 years ago
User Widgets
Recent Discussions
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.Re: Stacking The Beatles in Excel: An Exercise in 3D stacking
In an effort to turn my work into a generalized function, I'm coming to the conclusion a Lambda to stack and sort across a 3D sheet reference is not feasible. My function checks successfully for: - uniformity in column names in all sheets even if not in same order - valid 3D reference - the sheet where the function is used is not within the sheet stack - row and column limits of Excel not exceeded by stack estimate (before dupes removed, filtering) The main issue is Excel is incredibly shaky when stacking beyond 10,000+ rows across even 2 sheets. Stacking 10,000 elements is quick at .25 seconds. Doubling the rows leads to an immediate crash. I think this stacking could be a bit more reliable without the multiple checks but if the function will accept any 3D stack without inspection (and fail) then there are smarter options.Re: Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
I'm never satisfied so here's another go at it! //MooreAggλ is a generalized Moore (or Von Neumann) neighborhood aggregation //function in the polishing stages. Lifeλ = LAMBDA( config, //Board consisting of 1s and 0s generations,//Cellular automata states to simulate IF(generations=0,config, LET( neighbors,MooreAggλ(config,SUM,,1), keep,(config)*(neighbors=2)+(neighbors=3), //Alive with 2 or 3 neighbors revive,((config=0)*(neighbors=3)), //Dead with 3 neighbors Lifeλ( IF(keep+revive,1,0), generations-1))))Re: Finding Possible Matches to a Solution
The key to begin to solve the problem is to recognize the type of problem. This is the classic subset sum problem which can be handled by generating all possible combinations without repetition and then selecting only those rows which = target sum. SubsetSumλ = LAMBDA( integers, //array of numbers target_sum, //return rows which = this target LET( //Total numbers k, COUNT(integers), //Total combinations C, SUM(COMBIN(k, SEQUENCE(k))), //Generate bin array using BITRSHIFT to avoid limits of DEC2BIN bin, MOD(BITRSHIFT(SEQUENCE(C), SEQUENCE(, k, k - 1, -1)),2), //Swap binary for integers M, bin * TOROW(integers), //Check which rows = target sum keep, BYROW(M, SUM) = target_sum, //Deliver results deliver, FILTER(M, keep, "None"), deliver));
