Join numbers with less than min amount-CodePudding

Here I am using minimum number 50. If it less than 50 in the range, then It should be fail,

ex: J3 =IF(MIN(C3:H3)<50,"Fail","Pass") and also I need to find less than the min amount with color.

ex: K3 =TRIM(SUBSTITUTE(TEXTJOIN("",,IF($C3:$H3<50,$C$2:$H$2,"#")&": "&IF($C3:$H3<50,(50-$C3:$H3)&",","")),"#:",""))

This is what I got the result. Is there any other way to do it? I would like to rid-off the last "," but the formula went lengthy.. Hope it clears. Thanking you..

CodePudding user response：

You can use the FILTER-function, which makes it easier:

=TEXTJOIN(", ",TRUE,FILTER($C$2:$H$2 & ": " & 50 - C3:H3,C3:H3<50,""))

or with spill down:

=LET(colors,C2:H2,
     data,C3:H9,
     BYROW(data,LAMBDA(d,
           TEXTJOIN(", ",TRUE,FILTER(colors & ": " & 50 - d,d<50,"")))
         )
)

The "trick" is to build the "color: delta-value" construct as input for the filter array.