Forum Discussion
MIN of a 3D Array to Spill
I think the approaches you are exploring are important.
I regard the grid as providing the means for manipulating multidimensional arrays and displaying the results. To most users it provides a default referencing framework for formulas but I haven't used an A1-type reference for 8 years. I use defined names to declare a region of the sheet to be part of my model and to indicate what data should be placed within the region.
Back to your problem. I didn't bother about the individual parts of the 3D range (strictly speaking a 2D representation of the implied 3D array) but named the overall data structure 'Array3D'.
= LET( MINλ, LAMBDA(x, MIN(x)), reshaped, WRAPCOLS(TOCOL(Array3D), 30), minima, BYROW(reshaped, MINλ), DROP(WRAPROWS(minima, 5), -1) )The differences are that I used LET to split a nested formula into steps, tended to run the longer array dimensions down the sheet rather than across, and named the Lambda function rather than using it as an anonymous function. All of these are minor aesthetic points and others are free to disagree.
I have remarkably little idea what this is about, but I have run through the code to achieve the stated result for the specific case
I appreciate all the time and effort spent helping me out here. I'll have to take some time to step through what you accomplished in your workbook and learn from it. I'm still relatively new to the dynamic arrays, so I'm looking forward to understanding different approaches.
I spent some time yesterday simplifying my problem with sample random arrays, and was able to solve it by separating out each of my four arrays to be equally sized arrays, and then worked with shaping and reshaping to get what I was after. The goal here was to output the minimum value by cell location of four different arrays, in an equally sized output array.
I think the approaches you are exploring are important.
I regard the grid as providing the means for manipulating multidimensional arrays and displaying the results. To most users it provides a default referencing framework for formulas but I haven't used an A1-type reference for 8 years. I use defined names to declare a region of the sheet to be part of my model and to indicate what data should be placed within the region.
Back to your problem. I didn't bother about the individual parts of the 3D range (strictly speaking a 2D representation of the implied 3D array) but named the overall data structure 'Array3D'.
= LET( MINλ, LAMBDA(x, MIN(x)), reshaped, WRAPCOLS(TOCOL(Array3D), 30), minima, BYROW(reshaped, MINλ), DROP(WRAPROWS(minima, 5), -1) )The differences are that I used LET to split a nested formula into steps, tended to run the longer array dimensions down the sheet rather than across, and named the Lambda function rather than using it as an anonymous function. All of these are minor aesthetic points and others are free to disagree.