Closed Thread
Results 1 to 6 of 6

Count number of oscillations in a data series

  1. #1
    Registered User
    Join Date
    06-12-2005
    MS-Off Ver
    Office 2016
    Posts
    40

    Count number of oscillations in a data series

    I am trying to extract a few different types of observations from an example data series of Heat Index (HI).

    1st: The number of times the Index consecutively moves in one direction. I had some old code that referenced cell $C$110 in

    "=SUM(--(FREQUENCY(IF(OFFSET($C$110,0,0,$C$3,1)<=0,ROW(OFFSET($C$110,0,0,$C$3,1))),IF(OFFSET($C$110,0,0,$C$3,1)>0,ROW(OFFSET($C$110,0,0,$C$3,1))))=N47))"

    but I cannot seem to get it to work again.

    2nd: I would like to be able to count the number of times the Index moves up/down by 2%,5%,10%. This could be over any number of days. Essentially, I want to count how many times the Index moves around in a relative band of values. For example, there have been no 250% moves in the data series, but there have been numerous 2%, 5%, etc moves. I am trying to quantify this over specific observation periods.

    I cannot seem to find the solution to this on the forum or other websites, any help is appreciated. Many thanks!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    260

    Re: Count number of oscillations in a data series

    You want a lot of stats; there'll be lots of questions…
    Some ideas in the attached. 3 tables, one at cell G16, the two others to the right are only that first table filtered.
    Update them by clicking Refresh all on the Queries & Connections section of the Data tab of the ribbon, or right-click any table and choose Refresh.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-12-2005
    MS-Off Ver
    Office 2016
    Posts
    40

    Re: Count number of oscillations in a data series

    Wow, thank you for that. That largely appears to accomplish the bulk of what I have been trying to do.

    1.) Did you make the tables with power query? The Consecutive count column?
    2.) The tables look better than the long if/then, countif, etc formula I was trying to figure out. I will have about 3,000 rows of data.
    3.) Is there a simple way to (2nd question) show the number of moves above X% over specific start and end dates?

  4. #4
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    260

    Re: Count number of oscillations in a data series

    Quote Originally Posted by dstock View Post
    1.) Did you make the tables with power query? The Consecutive count column?
    Yes. Yes.


    Quote Originally Posted by dstock View Post
    3.) Is there a simple way to (2nd question) show the number of moves above X% over specific start and end dates?
    Simple? Not especially.
    In the attached, same as before with two additions:
    1. The cells E1:F2 have been converted to a table and serve as input into the previous result tables. Edit those two dates.
    2. A pivot table based on the table at cell G16.

    Once you've you've changed the dates, click Refresh all on the Queries & Connections section of the Data tab of the ribbon. You will probably have to click this twice; once to update the Power Query tables and a second time to refresh the pivot table.

    The pivot table uses grouping of its % change column to give you a count of moves. You can change the grouping to suit you.
    If you're experimenting with lots more data you may have to move the pivot table out of the way of the expanding results tables.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-14-2023
    Location
    Bogota, Colombia
    MS-Off Ver
    2016
    Posts
    1

    Re: Count number of oscillations in a data series

    Hello To all. Newbie here. Glad to be here.

    I don't understand. The samples don't have formulas.

    Thanks in advance.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Count number of oscillations in a data series

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do I count the number of cells with negative values in a series
    By Scorpvin in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-15-2019, 07:22 PM
  2. Obtain count of items within a series (within a series collection) VBA
    By jonfurn in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2019, 12:35 PM
  3. Sum and Count Consecutive High Values from Time Series Data
    By GeoffH1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2018, 04:32 PM
  4. How can I count data based on two conditions in a series of columns?
    By excelsearcher in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-30-2013, 09:48 AM
  5. [SOLVED] Macro to find last entry in a series and count number
    By jefflawrie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-19-2012, 12:33 PM
  6. Count a series of number with different types/description
    By bsengineer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-29-2009, 07:40 AM
  7. Using a threshold to detect frequency of oscillations
    By abcd3fg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-14-2008, 01:31 PM

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