User Profile
Patrick2788
Silver Contributor
Joined 6 years ago
User Widgets
Recent Discussions
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));Re: Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
I considered making a new discussion for this but since it's a bit off the beaten path for MTC, the best audience for it is probably in this discussion (and it relates to Conway with the need to find the Moore neighborhood and do some math). I recently created Terrainλ - a modular terrain analysis engine. The visuals are being handled with some creative conditional formatting. If any of this sounds interesting, please let me know here (or through a private message) and I'd be happy to provide an .xlsx to take for a spin. Thanks for taking a look! -Details- Terrainλ Return terrain analysis metrics by supplying square elevation matrices. Metrics: Aspect (radians), Classify, Curvature, Gradient Magnitude, Hillshade, Profile Curvature, Roughness, Slope (degrees), Slope (radians), ZTPI =Terrainλ(DEM,cellsize,function,[azimuth],[altitude],[z_factor])Re: Formula Challenge: The most efficient way to generate a Number Spiral (Ulam spiral) ?
One way to speed up the spiral is to use thunks/lazy evaluation all the way down: Spiralλ = LAMBDA(n, [start_at], LET( start_at, IF(ISOMITTED(start_at), 1, start_at), i, LAMBDA(SEQUENCE(n * 2 + 1, , n, -1)), j, LAMBDA(SEQUENCE(, n * 2 + 1, -n, 1)), x, LAMBDA(j() * SEQUENCE(n * 2 + 1, , 1, 0)), y, LAMBDA(i() * SEQUENCE(, n * 2 + 1, 1, 0)), a_, LAMBDA((ABS(x()) > ABS(y())) * (x() > 0)), b_, LAMBDA((ABS(x()) > ABS(y())) * (x() <= 0)), c_, LAMBDA((ABS(x()) <= ABS(y())) * (y() > 0)), d_, LAMBDA((ABS(x()) <= ABS(y())) * (y() <= 0)), a, LAMBDA(4 * x() ^ 2 - 3 * x() + y()), b, LAMBDA(4 * x() ^ 2 - x() - y()), c, LAMBDA(4 * y() ^ 2 - y() - x()), d, LAMBDA(4 * y() ^ 2 - 3 * y() + x()), result, start_at + IF(a_(), a(), IF(b_(), b(), IF(c_(), c(), d()))), result ) ); This generates a spiral with 51,854,401 elements in about 78 seconds.Re: Stacking The Beatles in Excel: An Exercise in 3D stacking
I like the first the one the best. It's very straightforward and efficient with the work mostly done with sequencing and some shaping functions. You may convince me to give IFS another look as it's never been one of my go-to functions. It is used smartly here in a way where the evaluating all arguments thing doesn't matter. With the first option I don't mind keeping errors and blanks if the function is speedy. Once the data is in better shape that can be dealt with if needed. Another point in regards to re-pivoting data. This sample data... ...is interesting to me because it's all text and can be analyzed a few different ways: unpivoted and aggregated, converting it to an adjacency matrix (Showing relational data by Instrument or by Person, for example), unpivot/sort/create tree map/sunburst chart, etc. That may be a topic for another discussion!Re: Stacking The Beatles in Excel: An Exercise in 3D stacking
Another go at this for 2025! **Edited - cleaned it up a bit more. StackNSortλ= LAMBDA(three_dimensional_ref, LET( stack, HSTACK(three_dimensional_ref), sorted, SORT(stack, , , 1), wrapped, WRAPCOLS(TOCOL(sorted, , 1), ROWS(sorted) * (SHEETS()-1)), header, TAKE(wrapped, 1), result, UNIQUE(FILTER(wrapped, (TAKE(wrapped, , 1) <> 0))), result ))Re: Best method to find variances between checkbook deposits and GL deposit transactions
There are elegant solutions to finding the subset sum of a given check book deposit total. However, the fact the checkbook date of 8/1, for example, may have GL deposits from multiple dates is the part that makes this difficult. If we disregard the GL dates in your example, both 2500 and 4500 have several ways to arrive at those totals. There must be something in those GL rows that would associate a given entry with the checkbook dates of 8/1 or 8/2, for example.Re: Tech Gurus I need your help.
If you need this capability often then it makes sense to define a Lambda to handle the task: RepeatRowsλ = LAMBDA(rows, repeat, LET( k, ROWS(repeat), rep, MAX(INDEX(repeat, k),1), seq, SEQUENCE(rep, , k, 0), next, TAKE(rows, -k), repeated, CHOOSEROWS(next, seq), acc, VSTACK(repeated, rows), unpack, DROP(acc, -1), IF(k = 1, unpack, RepeatRowsλ(unpack, DROP(repeat, -1))) ) ); The function can also unpack tables: If the repetition is not specified for an item or row the function defaults to 1. Attached is a demo.Re: How to optimize a large XLOOKUP table with volatile data sources?
A fully optimized XLOOKUP works with sorted data and is arranged to spill. XLOOKUP performs a linear search by default which means it starts from the top and checks each element until a match is found or it reaches the end of the array. If you want to speed up the calculations, you'd have to either sort the data in the sheet(s) or within the formula with SORT. A binary lookup is faster because the search needn't check each element. If you search an ID that begins with 4, the lookup checks it against the middle value of the list and determines where the return would come from, etc.
