Turning Point for Data Series

I am looking at identifing short-term and long-term turning points for a data series. For example, in the attached file, I would like to identify day 27 as a short-term low and a short-term high at day 34. Also, I would like to identify a long-term high at day 107 and a long-term low at day 150.

I am looking for a formula I can put in column C that will identify these turning points.

I think this should be in the Excel Charting & Pivots sub-forum.

Check the attached file for a possible solution.

Well then please tell me where is the line between short and long term? At which day?

So, the problem feels more like a signal processing problem then, and it might be beyond Excel in that it feels more like a generic "what kind of algorithm does anyone use in any programming language to detect peaks and troughs". Unfortunately, I don't know if we have any experts in signal processing here that can generate a robust algorithm on demand. I doubt that it will reduce to a single formula, as these kinds of algorithms can get fairly complicated.

I am not an expert in signal processing, but I find that many signal processing problems -- especially peak/trough detection algorithms -- will start with some kind of "instantaneous rate of change" calculation. Peaks/Troughs are often detected in part by when the slope of the signal changes sign. Excel has a built in SLOPE() function (https://support.office.com/en-us/art...a-61d7e01276b9 ) that can be used for this purpose. A simple (probably too simple) algorithm for peak/trough detection might be to have a "moving" SLOPE() function (you would need to decide how wide the moving window should be) in column C, then analyze column C to look for changes in sign. You would then try different size windows on different signal data until you were confident that you had a good window size to correctly detect most of your real peaks and troughs.

We are usually pretty good at Excel programming questions. If you will help us understand the algorithm you want to use, we can usually help you program that algorithm into the spreadsheet. However, we are often less able to develop an algorithm from scratch (in part because we cannot know everything you know about the goals, specifications, and requirements of the project.) If we are at the very beginning of the algorithm development process, I might suggest that you start with a basic slope and detect when the slope changes sign algorithm and see how that informs the next stages of development.

Either that or I might begin searching for 3rd party preprogrammed signal processing apps and see if someone has already got a signal processing algorithm. Even if it costs a few dollars for a license, a preprogrammed solution that will work out of the box might be preferable to writing your own signal processing algorithm.

Do you feel like that combination is enough to catch the points you want to catch? It appears to me that it catches the 8 points you have highlighted in yellow (maybe a little earlier or later than the yellow cell), but it also catches a lot of other highs and lows. I tried a 5 point window (=SLOPE(B2:B6, A2:A6)) and it caught fewer spurious peaks/troughs, but still caught the 8 highlighted. When I tried a 9 window slope (=SLOPE(B2:B10,A2:A10)) It still caught a lot of spurious points, but failed to catch a couple of the yellow points.

Anyway, if the 2 point window you tried is adequate, then good. You might try other windows and see if you like them any better. As I noted, there may also be other things that need to be looked at to get an algorithm that will catch fewer spurious points while still catching the points you want.