+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    05-26-2017
    Location
    London
    MS-Off Ver
    MS2016
    Posts
    6

    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,
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,873

    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.
    Attached Files Attached Files
    Glenn



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculate the portion of a planned number during a period, based on another time period
    By BryGuy81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2017, 01:29 PM
  2. how to calculate days worked for an average over a five week period
    By Nicky_B in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-24-2014, 11:28 AM
  3. [SOLVED] How to calculate the average for specific Time period
    By bssol in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-15-2013, 02:21 AM
  4. [SOLVED] Complicated:Calculate time period based on no. of units sold + fluctuations within period
    By omaha.crab in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 03:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1