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

1. How to calculate the Average time period of a full oscillation on a set of data

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.

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.

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,

2. Re: How to calculate the Average time period of a full oscillation on a set of data

You know... Sometimes when you start down a road you get fixated on the route.... There may be easier ways to do this... bound to be. But I got fixated on "my" route. It needs a bit of tweaking still - so don't regard it as the finished product. I just need a shower and a beer now!!.

Column AA identifies each and every maximum.
Column AB returns the value of the first occurrence of each maximum (NEED TO check if another maximum of the same numerical value, further down the table, is correctly returned).
Column AC removes "clusters" of maxima (this may be removable)

Columns AD, AE and AF do the same for the minima.

Column AG combines them into a single column as MAX or MIN.

Column AH & AI remove 2nd, 3rd, etc, MAXs between MINs and vice versa.

Column AK returns the counter for the MAXs and column AL the counter for the next MIN.
Column AM is the counter interval between each MAX/MIN pair.

All formula, except AK and AL are normal formulae. AK and AL are array formulae.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Check it over. Is this doing what you want? LATER I/we/you can tidy of the loose ends. This is a "verification of the principle" stage.

There are currently 1 users browsing this thread. (0 members and 1 guests)