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

Attached Files

RSI Frequency.xlsx (430.7 KB, 8 views)

Here Is a screenshot of column R and S where you can see in Green the 0s indicating above 90, below 10 and above 90. However the red 0 indicates the issue I face as I want it to ignore the red red 0 and tell me how long it takes to get from the first green box to third green box

Attached Images

RSI FREquyency Explain SNIP.PNG (76.5 KB, 12 views)

Hi and welcome
please keep in mind when posting, that not everyone understands the technical terms/jargon you are using.
Perhaps it is not relevant, but I e.g. ( and probably others) do not know the meaning of "RSI", and you can not expect members to look up the meaning of this abbreviation. Always try to keep descriptions as simple as possible
Thanks

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post