This Excel lambda function identifies the points in a series that are either higher or lower than both adjacent points

excel-lambda-INFLECTIONPOINTS.txt

/*
INFLECTIONPOINTS

Identifies the points y_x in an ordered numerical variable where the adjacent points y_x-1 and y_x+1 are either both higher or both lower than y_x

Inputs:
  y: an ordered numerical array
  
Returns:
  An array the same size as y containing -1 where y_x is lower than the adjacent points, 1 where y_x is higher than the adjacent points, otherwise 0.

*/

INFLECTIONPOINTS =LAMBDA(y,
  LET(
    _y,y,
    _i,SEQUENCE(ROWS(_y)),
    _out,SCAN(
          0,
          _i,
          LAMBDA(a,b,
            LET(
              _s1,INDEX(_y,b,1),
              _s0,IF(b=1,#N/A,INDEX(_y,b-1,1)),
              _s2,IF(b=ROWS(_y),#N/A,INDEX(_y,b+1,1)),
              IF(OR(AND(_s0<_s1,_s2<_s1),AND(_s0>_s1,_s2>_s1)),_s1,#N/A)
            )
          )
        ),
    _out
  )
);

INFLECTIONPOINTS_ALT = LAMBDA(y,
  LET(
      _y, y,
      _after, DROP(_y, 1),
      _before, VSTACK("", DROP(_y, -1)),
      _out, BYROW(
          HSTACK(_y, IFERROR(_y - _before,NA()), _y - _after),
          LAMBDA(r,
              LET(
                  y, INDEX(r, , 2),
                  z, INDEX(r, , 3),
                  IF(OR(AND(y < 0, z < 0), AND(y > 0, z > 0)), TAKE(r, , 1), #N/A)
              )
          )
      ),
      _out
  )
);