test the continuity of the cells

Copper Contributor

截图20210512005531.png

3 Replies

@tttcx 

Here is a solution with some helper columns.

In G1: =COUNTIF(A1:C1,">15")=3

Fill to the right to J1, then down to G3:J3.

In K3: =IF(OR(G1:J1),"√","")

Fill down to K3.

You can hide columns G to J if you wish.

 

S0397.png

@tttcx 

As variant

image.png

with custom number format instead of IF()

Formula is

=--ISNUMBER(SEARCH("111",TEXTJOIN("",0,--(A1:F1>15))))

@tttcx

This is a 365 solution.  I think it will port to traditional methods but I only use 365.

= LET(
   k, SEQUENCE(1,6),
   selected, FILTER(k, values>16),
   bins, FILTER(k, values<=16),
   seq, FREQUENCY(selected, bins),
   IFERROR(IF(MAX(seq)>2,"√",""),"√"))

 This formula counts the selected values that lie within each bin defined by the non-selected values.

I also have the advantage of a copy of FastExcel so I can use an ACCUMULATE function that evaluates the recursion equation

Uᵣ₊₁ = aᵣ+(1+bᵣ)Uᵣ

so

= LET(
   selected, SIGN(values>16),
   seq, ACCUMULATE(selected, 0, selected-1),
   IF(MAX(seq)>2,"√",""))

also works for me.