Google Sheets Count if Consecutive numbers are Less than

Question

I have a set of data in column A looking like:

A       B
150    155
200
140
122
250
299

I would like to get a "Yes" statement if there are 2 or more consecutive numbers less than the value in B1 (155), so far my formula is:

=if(frequency(FILTER(A1:A,A1:A<B1),A1:A=2)>0,"Yes","No")

However this doesn't seem to be working, any clues?

Answer 1

try:

=ARRAYFORMULA(IF(MAX(SUBSTITUTE(SPLIT(QUERY(
 IF((A1:A<>"")*(A1:A<B1), 1, 0),, 9^9), 0), " ", )*1)>1, "yes", "no"))

---

for more than B1 use:

=ARRAYFORMULA(IF(MAX(SUBSTITUTE(SPLIT(QUERY(
 IF(A1:A>B1, 1, 0),, 9^9), 0), " ", )*1)>1, "yes", "no"))

Answer 2

In case you are wondering, you can do these using Frequency, but I don't think Frequency works in the way you are expecting. The first parameter is the data that you are grouping, and the second one is the bin range, or list of cut points, that you are using to group the data. So the trick is to use the values <155 to give a list of row numbers (the data), and the values >=155 to give another list of row numbers (the cut points) as follows:

=ArrayFormula(if(Max(
 frequency(
  if((A:A<B1)*(A:A<>""),row(A:A)),
  if((A:A>=B1)*(A:A<>""),row(A:A)))
 )>=2,
"Yes","No"))

There is no particular advantage of doing it this way except that it works in Excel as well.