Spilling a BYCOL/LAMBDA function across rows

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

Updated, thanks.

Thanks very much, windknife, much appreciated.

I've applied in my actual application, where input data dimension is 600x10, simulation numbers range between 1000 to 5000 and the outputs are sums of 30-120 random values. Performance of the auto-spilled version is considerably slower than the manually dragged down version.

No need to do further work (this is great already) but would you have any ideas to improve calculation speed?

Thanks again.

You are welcome, but I don't know how to improve calculation speed.
Maybe, Gurus of this forum can give you some clues.

@Hans, thanks for your teaching.