Forum Discussion
NV
Jul 17, 2022Copper Contributor
Find Minimum Value from a dynamic range
Hi Friends, please help with my requirement. Each block is having some Data. As and when there is a value in the column M, then it should pick the Min/Max value from the next block. For e...
PeterBartholomew1
Jul 18, 2022Silver Contributor
I use 365 formula solutions for Excel solutions when possible. The starting point is to identify any given block by index (thick block lines communicate structure to the viewer but are difficult to maintain and not easily handled by formula).
Blockλ
= LAMBDA(b,
LET(
n, ROWS(dataSet),
seqNo, SEQUENCE(n),
blockNo, SCAN(0,seqNo,
LAMBDA(acc, s, IF(ISTEXT(INDEX(BlockID, s)), acc + 1, acc))
),
DROP(FILTER(dataSet, blockNo = b), , -1)
)
);
BlockMinλ
= LAMBDA(b, MIN(Blockλ(b)));The first function scans the block labels incrementing the index column whenever it finds a label. That allows each block of values to be identified and the second function returns the minimum over the block. A similar function is used to locate the value that shows whether the next block is to be displayed.