MIN of a 3D Array to Spill

[jsmith-icodevelopment]

I'm creating a sensitivity table that uses the new dynamic arrays to spill in two dimensions. However, one of the calculations I am using needs to both create a 3D array, and then find the min for each cell (of each dimension, not of row or col) to spill back into a 2D array. How can I do this?

Currently, I'm using VSTACK to create an array of all of four possible options (what should be four tables deep of a 3D array). This was superficially working previously, but in a new scenario, the second array in the VSTACK is a 2D array because of dynamic inputs. This is causing this new VSTACKed array to spill the bounds of my sensitivity table, and to not iterate through how I want.  

I then used BYCOL to find the MIN, but in hindsight, that wasn't doing what I wanted, as it was VSTACKing three one-dimensional arrays, and a two dimensional array, while trying to fit that in the bounds of a defined table.   

For more context, here is the output of "CL" before I use BYCOL, LAMBDA, and MIN. Each of one-dimensional arrays that spill horizontally are highlighted in yellow. The 2D array is in white. I feel like I need to combine all four arrays into a 3D array to somehow use LAMBDA to iterate through that 3rd dimension? Also, to clarify for the 1D arrays in yellow, if I were to create a 3D array, the values would simply fill down for each row. 

EDIT: desensitized and annotated file is located in the link below. 

https://www.dropbox.com/scl/fi/cbmckrmx4r6780v32we8s/Array-Help-Workbook.xlsx?dl=0&rlkey=gp3nupfg9d5...

[#2]

@jsmith-icodevelopment 

Have you achieved a satisfactory solution to this question unaided? 

Without an annotated workbook it is difficult to see the intent of this calculation.  I am also left guessing what the quantities are that are referenced by name.

I think you are combining each row of a 2D array with another vector and two broadcast scalars.

It is not clear what is gained by combing the arrays with VSTACK rather than waiting to combe them within the calculation.

[#3]

@Peter Bartholomew Still haven't been able to find a solution to this. I have uploaded a desensitized and annotated version of the model to hopefully explain the situation better. The formula itself is quite long, but I have highlighted the problem area I am having. 

https://www.dropbox.com/scl/fi/cbmckrmx4r6780v32we8s/Array-Help-Workbook.xlsx?dl=0&rlkey=gp3nupfg9d5...

[#4]

@jsmith-icodevelopment 

I spent some time looking at your workbook, but some of the formulas are monsters!

It would help if you could replace nested IFs by SWITCH or, better still, arrange the data so that a simple XLOOKUP would return the appropriate values from the data table, both row and column.

A few more Lambda functions to encapsulate messy formulas might help (eg to expand in increments of 1% horizontally or 5% vertically.   I have yet to look at how the rows and columns are combined but a Lambda function that combines a single pair of values could then be fed array values either directly or using MAP.

Where there are problems caused by scalars that need to be vectors EXPAND will work

= EXPAND(value,1,11,value)

To expand a vector to return an array requires something a bit longer

= IF(boolean, vector)

I will try to get some more time to look at the problem but, meanwhile, I hope that the comments still contribute something of relevance.

[#5]

@jsmith-icodevelopment 

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 

[#6]

@Peter Bartholomew 

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. 

[#7 · Accepted solution]

@jsmith-icodevelopment 

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.