Excel LAMBDA Spotlight: INFLECTIONPOINTS

In this article I'll look at a LAMBDA function called INFLECTIONPOINTS.?

This is a function I wrote last year to quickly get the local minima and local maxima of a continuous vector.

OVERVIEW

INFLECTIONPOINTS takes a single argument - a vector (or column, array, if you prefer) and returns an array of the same length where points in the input array are returned if they are inflection points and #N/A! is returned otherwise.

Given this simple dataset and chart:

The INFLECTIONPOINTS function will quickly identify the local minima and local maxima:

FUNCTION DEFINITION

This is the original definition. I've since realised another way of doing this, which I've shared at the end of this article.

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 takes one parameter:

1. y - an array of numerical values (presumably the y-values from a chart)

BREAKDOWN

Let _y be a copy of y. This is a habit I was using at the time to differentiate variables from arguments. It's not necessary, but I found it helped me stay organized.

Let _i be the index of _y - a sequence of integers from 1 to the count of rows in _y.

Then, scan _i as follows:

? ? _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)
? ? ? ? ? ? )
? ? ? ? ? )
? ? ? ? ),        

The goal of the scan will be to compare each array element in _y with the elements adjacent to it.

With that in mind, for each index element in _i:

• Let _s1 be the indexed element of _y.
• Let _s0 be the element before _s1. If _s1 is the first element, return #N/A. The assumption here is that the first point in a chart is not an inflection point.
• Let _s2 be the element after _s1. If _s1 is the last element, return #N/A. The assumption here is that the last point in a chart is not an inflection point.

Then, if the current point is larger than both the previous point and the next point, or the current point is smaller than the previous point and the next point, return the current point. Otherwise return #N/A.

_out is then the output of INFLECTIONPOINTS.

ALTERNATE DEFINITION

This is an alternate definition that occurred to me while I was reviewing the original. Which one do you prefer?

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
? )
);,        

That's it for now. I hope you enjoyed this article. You can grab both function definitions from this gist.

This is the sixth in the LAMBDA Spotlight series. If you haven't read the others, check them out:

A.XMATCH.ROWS

Text.DropSliceBetween

CROSSJOIN

UNPIVOT

SHOWCLOCK

You can see the other articles I've written?here.

If you have a suggestion for a function which you'd like put under the spotlight, please message me.

Thanks for reading!