Spreadsheet: 834174d1687517187-spilling-a-bycol-lambda-function-across-rows-example-file
Sheet: Sheet1

Obs. | Var1 | Var2 | Var3 | Var4 | Var5 | Var6 | Var7 | Var8 | Var9 | Var10 |  | Number of Runs, J |  | Numbers to Sum, M
1 | 1.120799977479432 | -0.5969347275131315 | 0.02228696931684582 | 0.328647380709218 | -2.442517596706816 | -1.167936619291443 | 0.2948303943291293 | -1.144511185379375 | -0.8236741874269126 | -1.94930087259543 |  | 5 |  | 10
2 | -0.6954715877197613 | -0.1370339492653311 | -0.5809230674712929 | 0.03934570189441858 | 0.6502208639575443 | -0.374074502086258 | -0.2952733419863861 | -1.006952972680952 | 0.5820885475124474 | 1.7829689717299
3 | -0.7787714710732577 | -3.049240984808333 | 0.3443401732690767 | -0.1185667775238558 | 0.2633803456124145 | -1.325341346804809 | 0.2416181406922668 | -0.9132843977884844 | -0.6901193477910892 | -0.2111754368791461
4 | 0.3811708633145998 | 0.4920764477891834 | -0.2685906002320982 | -2.331395366231194 | -0.5514305405325731 | -0.2500647040273169 | -1.690857578131617 | 0.9718361316429459 | -0.05995886762802748 | -0.3164453375661855
5 | -0.7087312212045634 | 0.4466436576264898 | 1.230021508970939 | -1.369919211006914 | -1.283127493612363 | 1.374727546587708 | 1.081912112494875 | 0.9437150233624547 | 0.009626249727044206 | 1.004824239069482
6 | 0.8036964497697934 | -0.01074203032515253 | 0.3091292072745736 | 0.02496310504927854 | -0.4115277455112598 | -0.5652403908656652 | 0.5654594919949499 | -0.6442438850396678 | -0.02744185623942651 | 0.9934972409046332
7 | 0.8261069316827205 | -1.101724368213394 | 0.07092131818846802 | 2.218411649608035 | 1.33371042603878 | 2.416573312784187 | 1.300411816830833 | 0.764417170274625 | -0.2912058614507037 | -1.612897519560957
8 | -1.154940040462936 | 0.6563577512410687 | -0.3880223139732757 | -0.6817725713307745 | -1.05625692202393 | -0.4027994965972599 | -2.05051189975561 | 0.3492590180395586 | -0.5364879175062 | 1.135281495638243
9 | -0.06041941595293236 | 0.92945958245049 | -0.8696470802981777 | 0.7951357437181303 | -0.2419052074534972 | 0.3747577653053785 | 0.2780832861446122 | 0.0938674570694947 | 1.19128616894855 | -0.3338353684597358
10 | 1.04122164857258 | 0.4731180713657155 | 0.06787076001791419 | -0.650057251640931 | 0.5892943875485821 | 0.9119642133801229 | -0.5767144008849678 | -1.788556775254355 | -0.3715650519636381 | -0.04401578735454489
11 | 0.1237186889510576 | 1.622758044257904 | 0.1946204643449932 | 0.2130827377072892 | 1.005158455208205 | 0.9056451441381748 | 0.1993342752016701 | -0.7313987691836288 | -0.1806923085072784 | 0.7329928242585256
12 | -0.502601184979536 | 0.1420949783888576 | -0.08995493202956781 | 0.1119916198473358 | -0.2143665740823832 | 0.2899102377434039 | -1.079834117118892 | -1.60389116594142 | -0.1399926489189728 | -0.1277288956636853
13 | 0.8807218348272755 | -1.631471886885062 | 0.608585446856659 | -0.1975747072398052 | -0.5472174242333137 | -1.830369251044991 | 0.2931040323364189 | 0.6164046220411802 | 0.03959879959118884 | 0.6243329171336058
14 | -0.1462173508511664 | 0.4190373638757966 | 0.6204465119100749 | 0.3808348833232402 | -2.327985708430298 | 0.4473687303134397 | -2.666266010211671 | 1.249014618450261 | -0.5746624564010799 | -1.526012295386814
15 | 0.3960773784036371 | 1.050281250785383 | 0.3819602275849289 | 1.900973321745147 | 0.9868075247828134 | 0.7299398523288191 | 1.325530382551781 | -1.256986203498209 | -1.234804019235251 | 0.2427802330638671
16 | -1.315011772600063 | 0.4282913906321 | -0.148111562857442 | 0.1796544273459678 | -0.1852880001303178 | 2.599707735042733 | -1.102379801753479 | -0.9141909751237844 | 0.437929891704898 | -0.8577366356704451
17 | -1.411815646295451 | -2.021387378461885 | -0.2809636850116185 | -0.8450336871546342 | 2.295222287464409 | -0.8550080402651659 | 0.7873957273328023 | -0.6271327611873989 | -0.9906067140588176 | -0.1174337544637185
18 | 1.979864825823816 | -0.1651365305337289 | 1.061514891922849 | -1.097135831737228 | -0.2442860453717892 | -0.5851950765104975 | 0.4898721564088282 | -0.7556080352462605 | 0.5672241438217815 | -0.02485079254759432
19 | 0.5034938382250349 | -0.5504200921646835 | 0.3800308897172568 | 0.05041044270415615 | -0.2968450498691618 | -0.350074850614342 | -1.143913567843714 | -0.7275342658340758 | -0.485230987487026 | 1.205380112610636
20 | 1.094016314079677 | 0.3656356273252548 | 0.5270008011996069 | -1.98064631247169 | -0.2093009591608457 | 0.2280565618252487 | -0.2195997734955111 | 1.290996190007751 | -0.7999052702845494 | 0.7393679369380425
Run | Var1 | Var2 | Var3 | Var4 | Var5 | Var6 | Var7 | Var8 | Var9 | Var10 |  | SOLUTION 1 - correct but manual
1 |  |  |  |  |  |  |  |  |  |  |  | t The function in B25 does not spill, and needs to be copied down each row manually to get correct Number of Runs output
2
3
4
5
6
7
8
9
10
Run | Var1 | Var2 | Var3 | Var4 | Var5 | Var6 | Var7 | Var8 | Var9 | Var10 |  | SOLUTION 2 - incorrect but automatic
1 |  |  |  |  |  |  |  |  |  |  |  | t The function in B25 DOES spill to the correct Number of Runs but the outputs are all the same because RANDARRAY does not refresh between rows
2
3
4
5
6
7
8
9
10
Run | Var1 | Var2 | Var3 | Var4 | Var5 | Var6 | Var7 | Var8 | Var9 | Var10 |  | SOLUTION 3 - correct and automatic
1 | ??? |  |  |  |  |  |  |  |  |  |  | t A single function in B51 that spills to the correct Number of Runs and differs per run?
2
3
4
5
6
7
8
9
10