I have some data in cells A1:A18.
In cells C1:C14, I calculate the standard devation of subarrays in A1:A18 of length 5. This is the intermediate step.
Finally, in cell C20, I take the median of the values of the previous step.
Is there a way to skip the intermediate step and have cell C20 do all the work by itself, without copying the fourteen formulas above into it? (I want it to remain flexible, so it would also work for larger data sets.)
−-
Data:
0.014887428323466000
0.000249196159985297
0.004870262955141020
0.020241881949868000
0.007727580571946330
0.003520634519614010
0.023101923158969000
0.002751646641415980
0.008626006212130160
0.015728065005392400
0.008482040243214300
0.036656588715963100
0.028932076832144000
0.013672713688400400
-0.015999612376752700
0.004871177652082880
-0.030935686919494200
0.020029104415489500
>Solution :
Maybe something along these lines:
=MEDIAN(MAP(ROW(1:14),LAMBDA(x,STDEV.P(INDEX(A1:A18,x):INDEX(A1:A18,x+4)))))
Or, maybe a bit more intuitive:
=MEDIAN(MAP(ROW(1:14),LAMBDA(x,STDEV.P(CHOOSEROWS(A1:A18,SEQUENCE(5,,x))))))