User Profile
tboulden
Iron Contributor
Joined Jan 18, 2021
User Widgets
Recent Discussions
Re: Would you recommend the use of Excel complex numbers where performance is required?
lori_m PeterBartholomew1 Apologies for my long absence! I'll have to dive-in on this over the weekend, but it looks pretty thrilling! Back once I've wrapped my head around it, or when I hit a brick wall and have a question!Re: long texts with arrays in conditional functions
Sergei, you honor me with thinking I can be of help 🙂 However, if I understand the problem correctly, I think you're on the right track with MAP, and I'd venture that MAP vs BYROW internal workings exemplify the issue between your examples using 1 vs {1}. Assuming single column array for arr, I think MAP takes the value from each row-cell, however BYROW takes each row-cell as an array.Re: Accumulating arrays
PeterBartholomew1Much appreciated! I attempted to revamp my formula to go a similar direction if I'm reading you correctly, however it caused Excel to crash repeatedly. It was on a different machine, but it was something like the below; trying to add the error-handling for init_vals_arr being omitted, or promoting a single scalar to a rows_-length array are where I began experiencing glitchiness. Even tried operating from Excel for Web so Excel wouldn't keep crashing; I was able to import the LAMBDA editor manifest xml for the add-in, but it has no name manager available, so was at an impasse there as well. SCANBYROW =LAMBDA(init_vals_arr,array,fn, LET( rows_,ROWS(array), cols_,COLUMNS(array), thunk,LAMBDA(x,LAMBDA(x)), row_thunks,BYROW(array,thunk), scan_thunks, MAP(init_vals_arr,row_thunks, LAMBDA(init,row_thunk, thunk(SCAN(init,row_thunk(),fn))), MAKEARRAY(rows_,cols_, LAMBDA(i,j, INDEX( INDEX(scan_thunks,i,1)(), 1, j ) ) ) ) )Re: Accumulating arrays
PeterBartholomew1Howdy Peter, season's greetings! I did something similar with thunks and MAKEARRAY, see https://www.mrexcel.com/board/threads/scanbyrow.1183272/ for more detail. It assumes init is a scalar rather than an array of values, but could work with a few minor tweaks.Re: A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
lori_m Ahh, thanks for the clarification, I had never noticed the areas param in INDEX; I've confirmed both constructions, MAP across an area index and MAP across the EVALUATEd array of ranges. I couldn't think of a real-world specific use-case for the REDUCE/union construction, but set up a table and unioned all rows and all columns separately, then intersected them. Was surprised to see that its non-commutative in terms of order, but also makes sense in retrospect.Re: A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
PeterBartholomew1I went down a rabbit hole trying to play with MAKEARRAY since you mentioned it can't have a 0 value. I learned that you can omit both of the first two parameters, and that led me even further trying to see what possibly could go in for those parameters, even providing SEQUENCE formulae, etc. I was hoping to reverse-engineer MAKEARRAY based off the similarity to MAP of 2 parameters, but what I developed with MAP had sensical behavior, but I couldn't quite figure out what was going on with MAKEARRAY; exhibited on the MAKEARRAY tab of attached. I'm not sure I follow how to make it work with the 0 parameter. If the thunks aren't your cup of tea, check out the array_storage tab where I've put together a storage scheme to get dimensions of arrays and flatten them, and can revive the array from the storage array. lori_m I was able to use the union (,) operator, but could only get it working with thunks, see the union tab. I've not messed with union/intersection much, so not fully following your breadcrumbs, and my google searches didn't help much. Any references you can point me to, or suggest a specific INDEX construction? I wasn't sure what to do with OFFSET either.Re: A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
PeterBartholomew1Re: Can a thunk refer to a previously calculated array, just waiting to be called, or should it be thought of as a recipe for calculating the array should it ever be required? I think I recall hearing Andy/Simon/someone along the way mention lazy evaluation?? And I think I recall that meaning that it stores the LAMBDA, but only evaluates it when needed. It's also possible that my brain made it up sometime during my research on lambda calculus implementations.Re: A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
lori_mPlease see attached, modified EXPAND slightly to handle 2d arrays of thunks. You'll note that for most of these, SCAN omits the init_value; this can be overcome if absolutely necessary, but is an added complication. I've also included a version of reshape that I tried basing off np.reshape, but it still needs some polishing. =LAMBDA(array,[aRows],[aCols], LET( aRows_,IF(OR(ISOMITTED(aRows),aRows=""),1,INT(aRows)), aCols_,IF(OR(ISOMITTED(aCols),aCols=""),COUNTA(array),INT(aCols)), rows_,ROWS(array), cols_,COLUMNS(array), seq,SEQUENCE(aRows,aCols,0,1), r_,1+QUOTIENT(seq,cols_), c_,1+MOD(seq,cols_), reshaped,IFERROR(INDEX(array,r_,c_),""), result,FILTER(reshaped,BYROW(reshaped,LAMBDA(row_,NOT(AND(row_=""))))), result ) ) I'm enjoying these side-by-side comparisons, I've got another one for you, earlier this year I wrote a LAMBDA to extract subarrays of size [r x c] from a given array of size [m x n] in row-major order. Originally it was returning all subarrays, but since we hadn't figured out how to handle arrays-of-arrays, I was just using ARRAYTOTEXT, or subbing in an appropriate function to get back a single value. Now that we are thunking the arrays, I can provide an index list, and operate on specific subarrays. What would this look like in Python?
