User Profile
lori_m
Iron Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: Hyperlink function do not support Dynamic Arrays
For future reference: based on comments below, a method to spill any HYPERLINK formula of the form, =HYPERLINK(link_location,friendly_name) is to wrap link_location in an INDEX function: =HYPERLINK( INDEX(link_location, ROW()-ROW(RC)+1, COLUMN()-COLUMN(RC)+1), friendly_name ) Note: this formula is given in RC-notation so that RC refers to the formula cell e.g. G4. In the general case link_location and friendly_name may be 2D calculated arrays and the formula may be freely moved aroundRe: Hyperlink function do not support Dynamic Arrays
m_tarler Thanks for sharing - no way I'd ever have thought of that! This INDEX method seems to provide a generic approach for spilling a HYPERLINK formula with column arrays in both arguments. To improve efficiency I guess you could replace the XMATCH part with a range id column (C1#). My approach would have been to make the link_location parameter relative to the current row, e.g. =HYPERLINK("#"&ADDRESS(ROW(),COLUMN(A1#)),B1#) =HYPERLINK("#r[0]c"&COLUMN(A1#), B1#) These both look ok on office desktop but the second one seems to have an issue on web version.Re: Working with Arrays of Ranges
Related to the DICTIONARYλ suggestion, I wonder if it might be worth defining a nestable dictionary such as, DICT = LAMBDA(array, LAMBDA([key], IF(ISOMITTED(key), array, XLOOKUP(key, VALUETOTEXT(TAKE(array, , 1)), TAKE(array, , -1)) ) ) ); which, like in python, returns a dictionary from a two column array. e.g. one could try =countries() =countries("United Kingdom")() =countries("France")("Paris") where countries =DICT( WRAPROWS(VSTACK( "United Kingdom", DICT(Table1), "Germany", DICT(Table2), "France", DICT(Table3), "Italy", DICT(Table4) ),2) ); Though I'd still tend to side with the earlier PIVOTBY suggestion.Re: Working with Arrays of Ranges
Hi Peter - I think a natural way to define a data dictionary in Excel is as an (n x 2)-array consisting of key/value pairs. Given definitions as in the earlier attachment, a data dictionary could be defined pairwise, dic = WRAPROWS(VSTACK( "United Kingdom", LAMBDA(Table1), "Germany", LAMBDA(Table2), "France", LAMBDA(Table3), "Italy", LAMBDA(Table4) ),2) or alternatively as a pair of lists, dic = LET( keys, FILTER(TAKE(allocatedRange,,1),ISTEXT(markers)), values, StructureDataλ(markers, allocatedRange), HSTACK(keys, values) ) In either case TAKE(dic,,1) returns the keys and (@VLOOKUP("United Kingdom",dic,2,0))() returns the values corresponding to the key. This appears much like the GETλ / KEYSλ functions in your comment. However, I wouldn't advise creating DICTIONARYλ as I don't know of any way to specify an extensible list of arguments within a lambda definition - the first method above seems like a reasonable compromise in my view. For comparison the equivalent definitions in Python would be, dic={"United Kingdom":Table1,"Germany":Table2,"France":Table3,"Italy":Table4} or otherwise utilising tuples, dic = dict(zip( ("United Kingdom","Germany","France","Italy"), (Table1,Table2,Table3,Table4) )) dic.keys() and dic.get("United Kingdom") return keys / values. A custom data dictionary could be defined by passing arguments using unpacking operators (*args / **kwargs) - something Excel currently lacksRe: Working with Arrays of Ranges
DROP(range,{0}) appears to have the effect of 'boxing' the range within a 1x1 array, i.e. range -> {range}, thus preventing it from being dereferenced in some situations. Conversely the @ operator has the effect of 'unboxing' the range, i.e. {range} -> range. I'd need to track down some other examples where lambda helper functions dereference range inputs and this trick was required. Not that I have anything against thunks about which you will certainly be more informed than me. To be honest my use of spreadsheets hasn't progressed beyond copy/paste exercises of late so I thought it would be an interesting challenge to see if any of the old techniques could be leveraged here. One thought was that it may be possible to improve efficiency through manipulation of references. I haven't looked to see what the limit on areas is but I'd think there are scenarios where forming a union of range references can be more efficient than forming a union of range arrays particularly given hstack/vstack support multi-area ranges as parameters. PS. Clarification to earlier formula: this is designed to return an array of sums for a multi-area range but currently throws #CALC! on beta version.Re: Working with Arrays of Ranges
Interesting scenario and method. I played around a little looking to see if it was possible to avoid the use of thunks by combining references into arrays and had partial success. If the names of the ranges are Table1,..,Table4 we can define a multi-area range: Tables=Table1,Table2,Table3,Table4 and try the following formula to convert this to an array of ranges. =SUMIF(LAMBDA(T,MAP(SEQUENCE(AREAS(T)),LAMBDA(a,DROP(INDEX(T,,,a),{0}))))(Tables),"<>") Strangely, this appears to work fine in Monthly Enterprise Channel but not Beta Channel. An alternative that was more successful was to build a multi-area reference via REDUCE as shownRe: Your thoughts serializing arrays to manage state in excel (by way of inventory tracking)
This is an interesting discussion and I believe points to a limitation in Excel. What I would like to see is formulas being able to return more general 'Data Types' to cells including lambda definitions or array results so that data could be held in memory rather than output to the sheet. I've seen a few options over recent years that may be of relevance to this kind of scenario: - The Python in Excel extension is taking steps in this direction by supporting dataframes within cells: https://support.microsoft.com/en-gb/office/python-in-excel-dataframes-a10495b2-8372-4f0f-9179-32771fe0dc04 - Add-in libraries that support 'object handles' (e.g. XLL+/XLW frameworks) which are of particular relevance with Real-Time Data where array sizes may be variable and for efficiency only dependent cells need be recalculated. - Without any extensions, I think the suggested text serialisation approach is a decent workaround for smaller data sets e.g. using ARRAYTOTEXT / TEXTJOIN functions. Another possibility might be to store transactional data as records (#rows, #cols, {data}) via array shaping functions TOROW / WRAPROWS - which might overcome the 32k data restriction though tables could not then be utilised due to the need for data spilling.Re: Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
Ok, so after several weeks of head banging, i feel like there may finally be some progress! First, as far as i'm aware the list of scalable alternatives to the basic REDUCE/VSTACK method for stacking arrays now includes: nested reduce, recursive bisection, thunk arrays and lambda accumulators. Several examples are given in earlier comments; a relatively efficient implementation utilising lambda accumulators could be BYROWλ =LAMBDA(array, function, LET( n, ROWS(array), k, CEILING.MATH(LOG(n,2)), REDUCE( LAMBDA(r,function(CHOOSEROWS(array,r))), SEQUENCE(k,,0), LAMBDA(a,i,LAMBDA(j,IF(j+2^i<=n,VSTACK(a(j),a(j+2^i)),a(j)))) )(1) ) ) Second, for enumerating combinations, tests suggest lookups can be faster than stacking when returning longer result sets and that 2D-arrays may be needed to overcome decimal precision limitations such as: =LAMBDA(n,k, REDUCE(SEQUENCE(n-k+1),SEQUENCE(k-1,,2),LAMBDA(a,j, LET(r,ROUND(COMBIN(SEQUENCE(n-k+1,,j),j),), i,SEQUENCE(@TAKE(r,-1,1)), HSTACK( CHOOSEROWS(a,i-XLOOKUP(i,r,VSTACK(0,DROP(r,-1)),,1)), XMATCH(i,r,1)+j-1 ) ) )))(20,10) P.S. was there a Towers of Hanoi lambda posted here previously? Maybe I was hallucinating but I had been hoping to check it out when it seemed to vanish!Re: A generalised Lambda helper function that return arrays of arrays using bisection.
PeterBartholomew1 Great news about new regex functions! I had not been aware of this development. And I like the use of shaping functions with arrays of thunks, something along these lines was included in one of the functions in the gist. I hope to be able to revert regarding the other topics raised but briefly for now relating to How/Why of lambda constructions, How? Consider the following two implementations where parameters may be values, arrays, lambdas, etc. Thunk = LAMBDA(HSTACK(a,b,c,d)) Tuple = LAMBDA(i,CHOOSE(i,a,b,c,d)) In the Fibonacci example we have a 2-tuple or pair (x, y) that maps to (y, x+y) with initial value (0,1). The mapping is represented as i*x+y (i=0,1) or equivalently IF(i, x+y, y) with initial values x=0, y=1. Why? Both constructions can be nested to work around array of arrays. I've got less experience with thunks than you but my impressions are both have pros/cons including, Thunks - Easily modified / reshaped by updating the array and wrapping in lambda - Extendable to longer lists via stacking functions. Tuples/records: - Arrays stored individually e.g. an accumulator can be built containing several variables. - Efficient retrieval of data since IF/CHOOSE only evaluate selected arguments (versus indexing an arrays of thunks which can lead to inefficiencies) Perhaps one could make a similar distinction between lists (mutable) vs tuples (immutable) in some other languages. I guess that other link you mention might be related though the terminology looks non-standard - at least to me.Re: A generalised Lambda helper function that return arrays of arrays using bisection.
PeterBartholomew1 Given we now have a few different implementations of BYROW available that will support 2D-arrays of results, I thought it might be of interest to compare some of these approaches by consolidating into a gist: byrow · GitHub In some basic tests using an input array SEQUENCE(10000,10) and sorting columns in descending order, results suggested recursive bisection was fastest (0.3s) followed by 3-level nested REDUCE/VSTACK (0.5s), and the BYROW/Thunk method - which I think is quite similar to your approach above - was not far behind (1.1s). As an alternative to thunks, one might also consider 'records' or 'tuples' that are essentially indexed lambdas that may contain any type of data including arrays and lambdas. For example a Fibonacci sequence implementation might be, =BYROW( 0, SCAN( LAMBDA(i, i), SEQUENCE(1000), LAMBDA(a, i, LET(x, a(0), y, a(1), LAMBDA(i, x * i + y))) ) ) I believe building an indexed lambda as accumulator in this way could be one way to extend the REDUCE/VSTACK beyond three levels - though admittedly this has been quite a struggle so far!Re: Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
PeterBartholomew1 That thunk method looks pretty neat - I was impressed by the speed too. In previous attempts working with thunks I had experienced performance issues that were difficult to circumvent. Passing the thunk array as an array parameter to SCAN is clever and appears much more efficient than passing as a lambda parameter - within MAKEARRAY for example. My first attempt to improve efficiency was to apply memoization which is a standard technique to avoid recomputing values within the recursion. I started looking at converting some online code examples to lambdas by building a table of thunks before realising that this was going to be similar to your method but probably less fast! A follow-up thought was to generate all decimal combinations in a single row of Pascal's triangle iteratively, =SORT(REDUCE(0,SEQUENCE(n),LAMBDA(a,_,VSTACK(a*{1,2},a*{1,2}+1))),{1,2}) With n = 12 for example this returns 2^12=4096 rows, the first column is the number of combinations k and the second is the decimal representation of the combination. To return just the COMBIN(n,k) combinations we only need keep combinations less than k, =LAMBDA(n, k, LET( combs, REDUCE( 0, SEQUENCE(n), LAMBDA(a, i, SORT( VSTACK( DROP(a, IF(i > n - k, INT(COMBIN(i - 1, n - k)), 0)) * {1, 2}, DROP(a, IF(i > k, -INT(COMBIN(i - 1, k)), 0)) * {1, 2} + 1 ), {1, 2} ) ) ), BASE(TAKE(combs, , -1), 2, n) ) ) I expected this might be faster than the thunk approach but in practice it fell short due to the repeated SORT operations so likely needs further refinements. I was also surprised that COMBIN returns non-integers in some cases (e.g. try removing 'INT' with n=12 and k=6)Re: Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
PeterBartholomew1 That looks impressive and not a method I think I ever could have foreseen! And I see what you mean about the repetition in the binary tree. The method of m_tarler in the previous link also looks elegant with acceptable performance which I suspect could be further improved with some minor alterations. I will need to set some time aside to look at all this in more detail... PS. Just to clarify in my last post it looks like a closing parenthesis was omitted from the first formula and, in the screenshot, the cell beneath 'Residual' contains a spilled array formula: =MinSubset(seats, 122)Re: Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
PeterBartholomew1 I finally managed to take a look at your file attachment today and I think the BITXOR approach works well when applied to the problem of finding the minimal subsets for smaller data sets like the one in question. A couple of related thoughts occurred to me while looking through the lambda definitions... 1. To generate the list of possible sums one might also try: =REDUCE(0,seats,LAMBDA(a,n,VSTACK(a,a+n)) which appears very efficient compared to MMULT [0.33s with seats=sequence(20)]. Within the LET definition, this modification returned equivalent results for me: combinations, SEQUENCE(2^n,,0), totalSeats, REDUCE(0,seats,LAMBDA(a,n,TOCOL(a+{0,1}*n))) 2. For generating the minimal subsets, I also tried recursion with a threshold condition. Binary trees appear pretty powerful and applicable to a wide range of problems - but as you suggest the main issue is trying to keep a clear head!Re: How to find which combination of multiples of a set of 5 numbers will return a given value
mcinqb3 For finding the closest match in the given example, I think we can list all combinations of up to 9 of the values including replacements. For listing combinations I used a lambda function courtesy of m_tarler . The closest I could find to 18000 with this method was 1922 * 5 + 2404 + 3013 * 2 = 18040 [ref: 'example of counting multi-subsets' section of Combination - Wikipedia article]Re: Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
Just to add I should have checked the archives before posting the above formula. Two good alternative methods that came up in search results were (not coincidentally by both aforementioned contributors) https://techcommunity.microsoft.com/t5/excel/efficient-approach-to-generate-list-of-combinations-with-no/m-p/3786960/highlight/true#M187366 https://techcommunity.microsoft.com/t5/excel/all-combinations-of-5-numbers-from-1-to-35/m-p/3717447/highlight/true#M177211 Evidence that there are sufficient high quality examples available now to build general purpose libraries of lambdas.Re: Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
m_tarlerYes, having a repeatable random function can come in handy, I've quite often divided by 2^32 to get a non-volatile RAND alternative PeterBartholomew1 Indeed, another possible candidate might be a 'combinations' function, =combinations({"a";"b";"c";"d"},2) which would return {"a","b";"a","c";"b","c";"a","d";"b","d";"c","d"}. I had been thinking about this off and on for a while but like SergeiBaklan couldn't see any reasonable route. I revisited again in light of the bisection method you provided recently based on a recursion over an array version of, COMBIN(n,k) = COMBIN(n-1,k-1) + COMBIN(n-1,k) For example with the following lambda definition =combinations(sequence(20),10) returns COMBIN(20,10)*10=1847560 elements. combinations =LAMBDA(arr, k, IF( k <= ROWS(arr), IF( k = 1, arr, DROP( VSTACK( combinations(DROP(arr, -1), k), EXPAND(combinations(DROP(arr, -1), k - 1), , k, @TAKE(arr, -1)) ), k = ROWS(arr) ) ) ) )Re: Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
Peter - you have a better memory than me, it was only after ten minutes of searching I found this (Code Golf) formula for checking if a string contains a repeated character: =COUNT(SEARCH(BASE(({0,1,2,3,4,5}+{0;1;2;3;4;5}*6)*37,36,2),A1))>0 I remember avoiding the dreaded CSE entry and unnecessary range dependencies introduced by ROW, etc. were an integral part of such challenges. Things are much more straightforward these days - Excel formulas being a bone fide language and all - though maybe not quite as fun 🙂 ( https://dhexcel1.wordpress.com/2017/10/03/boolean-formula-for-repetitive-characters-in-a-string/ )
