User Profile
Patrick2788
Silver Contributor
Joined 6 years ago
User Widgets
Recent Discussions
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.
