How to calculate the Average time period of a full oscilation on a set of data

[DDK2110]

Hey Everybody!



I hope you guys are having a great day!

I am currently working on something and I am really stuck and was wondering whether anybody had an idea of how to solve this problem.

So If you look in the spreadsheet which I have attached in column H you can see a long list of data points. These represent the Relative Strength Index (RSI) value for that day. The RSI is technical indicator used in trading to show whether a stock is overbought (RSI>90) or oversold (RSI<10). I would like to find out how long it takes on average for the RSI to make one full oscillation from overbought to oversold back to overbought. Meaning how long it takes on average for the RSI to go above 90 below 10 and back up to 90. So that I can then find the frequency of these oscillations.

The issue here arises because the RSI can move above 90 below 90 and back above 90. However, this is not a full oscillation as it did not go below the value of 10.

In column R and S I have a binary series of numbers, where 0 in R indicates the RSI being above 90 and 0 in S indicates it being below 10.

How can I make it count or simply enter values when there is a 0 in R and keep counting until it reaches a 0 in S and neglecting and further 0 in R between 0a in R and 0b in S. and Then to reset the counter and look for the next 0 in R and do it again. As it would get me half an oscillation down and this could be reversed to get the the other half of the oscillation back up to 90.

I hope this makes sense and I hope somebody has an idea of how to get the frequency of oscillations either through the binary series approach in columns R and S or through and entire different approach.

Thanks you a lot in advance!

All the Best,

DDK2110