Identify flat/plateau parts of line charts

[lukasvjohansson]

Hi,

i have a data base with x values (time/dates) and y values (price).

I have created a line chart, that show the price changes over e.g. a two year period, with each x being every 4 weeks.

So far so good.

However, i would now like to create some kind of formula or macro to identify the parts of the line chart which are "flat" or plateau like. I've managed to write dynamic formulas that can both identify maxi/minimi for the selected period and local maximi and minimi for defined periods as well. However, im struggling to come up with a way where i can select the data where there is a "plateau". The y values/price will change as i choose which product i want to look at. The price curve generally has some kind of incremental/stepwise pattern, and i would like to be able to select each step, and compare them with eachother. I want to be able to select and analyze each "jump" the price does against eachother.

In this dummy example the plateaus could be the 1's, 4's and 7's. And then i would like to compare the change in for example percentage between them. However, my data is much more irregular and it needs to be dynamic as the plateaus will be different in length and frequency.

1
1
1
1
2
3
4
4
4
4
5
6
7
7
7
7

Ive tried to play around with both st deviation, variance, moving average and so on and so forth, however, ive still not been able to actually create a formula which always select the whole period where the price has been flat/stable. The solution does not have to be graphical, but its always a plus to be able to visualize of course.


I know this is a very messy explanation, but Ill try my best to give further information if anyone has a first suggestion.

Best regards

 

[lukasvjohansson]

I should also add that the dummy data contains duplicates, which then would give the simple solution of a count if, however my data could be a "plateau of many ups and downs" consisting of unique values rather than duplicates.

Ive looked into the possibilty of using the second derivate and such as well, but i feel a bit lost.