Spilling a BYCOL/LAMBDA function across rows

[Emlyn Flint]

Hi all,

See attached a sample workbook of my problem.

I have a given set of NxK input data. The example sheet has 20 data rows across 10 column variables.

I am trying to achieve the following: using a single dynamic spilled formula (i.e. no VBA), calculate the sum per column of M randomly chosen rows and output this a total of J times. The final output is thus a J x K matrix of random M-observation sums per column.

My solution so far is to use a BYCOL/LAMBDA function with RANDARRAY to calculate the random M-sum values but this only spills across columns. I still have to drag this formula down to get a total of J output rows.

I have also tried to use an IF-statement which checks if an input array is less than or equal to the desired number of output rows. This does spill across the target number of rows but the outputs are all the same because the RANDARRAY function doesn't update between rows and hence we are summing the same M numbers per column each time.

Any assistance in creating a dynamic formula that updates and spills across rows would be greatly appreciated.

Regards,
Emlyn