+ Reply to Thread
Results 1 to 5 of 5

Grouping time-stamped data into a different sampling interval

  1. #1
    Registered User
    Join Date
    12-06-2018
    Location
    Norway
    MS-Off Ver
    1811
    Posts
    31

    Grouping time-stamped data into a different sampling interval

    Hello,

    I've uploaded a sample of the data I'm working with. My question is rather simple although a bit complicated to implement due to the large number of columns in my dataset.

    In short, there are three categories of data to transform;

    1) Columns A and B are the time-stamps. As you can see, they are taken every 80-seconds.

    2) Columns C to E are activity values for 3 different axis

    3) Columns F to O contain the number of seconds for each type of Event as seen on the header of the columns for every 80-second period. The total time (in seconds) is summarized in column P

    Hence, I would like to transform my data to have time-stamps taken every 4 minutes (i.e. 240-seconds). The columns will have to be transformed as follows:

    1) Columns A and B will have a time-step of 240-seconds (starting 6/19/2018 at 10:40:00).

    2) Values in columns C to E will have to be grouped every three rows (because 240-seconds = 3 * 80-seconds) and then averaged to match the 240-second intervals

    3) Values on columns C to E will have to be grouped every three rows and summed. Column P being the sum of values for columns [F:O], a value of 240 for all rows will have to be displayed to make sure the transformation as been done right

    I hope somebody can help me or at least put me on the right track. I'm rather new to Excel so any input is appreciated.

    Cheers!
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: Grouping time-stamped data into a different sampling interval

    See attached.

    After entering the formulas shown in row 4, I selected R4:AF6 and double-clicked the fill handle.

    Then you can copy those coulumns, paste as values, delete columns A:Q, and sort by col R to close up.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-06-2018
    Location
    Norway
    MS-Off Ver
    1811
    Posts
    31

    Re: Grouping time-stamped data into a different sampling interval

    Quote Originally Posted by shg View Post
    See attached.

    After entering the formulas shown in row 4, I selected R4:AF6 and double-clicked the fill handle.

    Then you can copy those coulumns, paste as values, delete columns A:Q, and sort by col R to close up.
    I followed your steps by filling in the formulas for the 4th row. However, I'm not able to find any fill handle when I'm selecting R4:AF6. Indeed, I don't have any option to click on at the corner of the cell as all my selected areas will become grey.

    Also, I'm wondering why I should select R4:AF6 and not the full R4:AP6?

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: Grouping time-stamped data into a different sampling interval

    The fill handle is the small square that appears in the lower-right corner of the selection marquee. When you hover the mouse cursor over it, the cursor changes to a +

    But I gave you bad dope. I actually entered the formulas staring in col Q, filled them down as described, and then decided for cosmetic reasons to insert a blank column. Doing that keeps double-clicking the fill handle from working because of the lack of adjacency. So just grab the fill handle and drag down.

    Also, I'm wondering why I should select R4:AF6 and not the full R4:AP6?
    The formulas only extend to col AF:

    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    AA
    AB
    AC
    AD
    AE
    AF
    1
    GMT_DATE & Time ACTIVITY_X ACTIVITY_Y ACTIVITY_Z Vigilance Head-up Grazing Browsing Moving Grooming Resting Fleeing Unknown End Total
    2
    3
    4
    06/19/2018 10:40
    60.7
    64.3
    88.3
    0
    232
    0
    0
    0
    5
    0
    0
    3
    0
    240
    5
    6

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,577

    Re: Grouping time-stamped data into a different sampling interval

    You also have proposed solutions in your duplicate of this thread, which a moderator has now closed - check back in that one.

    Pete

+ 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. Grouping and counting series of time-stamped events
    By juansalix in forum Excel General
    Replies: 2
    Last Post: 01-25-2019, 06:56 AM
  2. [SOLVED] showing the 2 chart line with 2 difference time sampling interval in one graph
    By fakhteh in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-10-2016, 07:35 AM
  3. Grouping Time in Start-End Interval
    By kpravin827 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-31-2015, 12:33 PM
  4. Replies: 1
    Last Post: 04-29-2014, 04:42 AM
  5. [SOLVED] Filtering time stamped data
    By Mroy in forum Excel General
    Replies: 2
    Last Post: 01-29-2014, 01:16 PM
  6. Need help grouping/consolidating data by time interval
    By kushki in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2012, 04:21 PM
  7. Time stamped data
    By Cspotrun in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-03-2007, 01:54 PM

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