+ Reply to Thread
Results 1 to 4 of 4

Select range based on timestamp values in first column (computationally light)

  1. #1
    Registered User
    Join Date
    06-09-2012
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    7

    Select range based on timestamp values in first column (computationally light)

    I have a huge table with several measurement values for every second of a day. The first column is therefore a UTC timestamp to identify each measurement record in the format shown below. What I want to be able to do is making calculations on all values that fall within a certain timeframe of the day, as computationally light as possible.

    For example I want to be able to take the average value of the 22:00:00-22:15:00 timeframe. I therefore need a way to select a range of values from different columns that fall within this timeframe.

    My problem: how do I make that selection using formula’s, not macro’s, given the rather tricky way Excel handles time formats?
    Or in other words: =average( _FILL IN YOUR FORMULA_ ) => what would you fill in here?

    Example dataset:

    utcTimeStamp I_L1

    24/03/2020 22:59:59 17,2
    24/03/2020 22:59:58 16,9
    24/03/2020 22:59:57 16,8
    24/03/2020 22:59:56 16,7
    24/03/2020 22:59:55 16,7

    I added this data set in attachment en in the picture below you can see the format my data is in

    Looking forward to your suggestions!

    formatdate.jpg
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Select range based on timestamp values in first column (computationally light)

    For lightweight -- add below to your table (should isolate time, and round time up to nearest 15 min interval):

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    then run your calcs relative to this field - e.g. AVERAGEIF (if you're using XL2007)

    you can achieve the same without helpers but, with large data sets, these approaches won't be as quick as the above should prove to be.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Select range based on timestamp values in first column (computationally light)

    For light weight I would propose a column of Start interval index numbers and a column of End interval index numbers as in columns C:D here. I used the Fill Series feature.


    C
    D
    E
    F
    G
    1
    Start Index
    End Index
    15 min time stamps
    sum 15 min intervals
    average 15 min intervals
    2
    1
    900
    24/03/2020 23:00:00
    13324.7
    14.80522222
    3
    901
    1800
    24/03/2020 22:45:00
    13416.6
    14.90733333
    4
    1801
    2700
    24/03/2020 22:30:00
    13401.3
    14.89033333
    5
    2701
    3600
    24/03/2020 22:15:00
    13375.7
    14.86188889
    6
    3601
    4500
    24/03/2020 22:00:00
    13352.2
    14.83577778
    7
    4501
    5400
    24/03/2020 21:45:00
    13487.3
    14.98588889
    8
    5401
    6300
    24/03/2020 21:30:00
    13444.8
    14.93866667
    9
    6301
    7200
    24/03/2020 21:15:00
    13426.8
    14.91866667
    10
    7201
    8100
    24/03/2020 21:00:00
    13439.8
    14.93311111
    11
    8101
    9000
    24/03/2020 20:45:00
    13404.6
    14.894
    12
    9001
    9900
    24/03/2020 20:30:00
    13490
    14.98888889
    13
    9901
    10800
    24/03/2020 20:15:00
    13338
    14.82
    14
    10801
    11700
    24/03/2020 20:00:00
    13348.5
    14.83166667
    15
    11701
    12600
    24/03/2020 19:45:00
    13361
    14.84555556
    16
    12601
    13500
    24/03/2020 19:30:00
    13360.7
    14.84522222
    17
    13501
    14400
    24/03/2020 19:15:00
    13354.8
    14.83866667
    18
    14401
    15300
    24/03/2020 19:00:00
    13437.2
    14.93022222
    19
    15301
    16200
    24/03/2020 18:45:00
    13432
    14.92444444


    Then define the target 15 minute sum and average (?) ranges like this: In F2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In G2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit I forgot the 15 minute interval date/time stamps. In E2 and filled down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 03-27-2020 at 01:35 PM.
    Dave

  4. #4
    Registered User
    Join Date
    06-09-2012
    Location
    Belgium
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Select range based on timestamp values in first column (computationally light)

    Hi guys, thanks for the advice!

    I notice that after doing the XLent solution I am actually not yet there: I now have a nice identifier column that allows to identify in which quarter hour a record falls...but then if I want to perform an averageif() or something on that I again encounter the problem that I need to be able to express the range of subsequent records that fall within the same quarter hour value.


    (as for the indexing idea proposed by FlameRetired: For the indexing to work, I would have to make sure that any 'number 1' starts at the beginning of a 15 min timeframe. Maybe it could work if I had an extra step in the formula that finds a record that is on exactly the beginning of a quarter hour timeslot.)
    Last edited by homecore; 05-07-2020 at 03:46 AM.

+ 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] select unique values range to column
    By rhvanloenen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-11-2016, 09:38 AM
  2. [SOLVED] Vlookup (but select range based on values)
    By learnall18 in forum Excel Formulas & Functions
    Replies: 31
    Last Post: 05-30-2016, 10:41 AM
  3. Select range of data based on values in colum A
    By eddiej90 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 01-07-2016, 06:12 PM
  4. [SOLVED] select column range based on a matched value
    By onroute in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2015, 06:28 PM
  5. Select range based off column name
    By randomonium in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2015, 07:35 AM
  6. [SOLVED] Select to the end of a row based on used column range
    By BrownBoy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-20-2014, 02:14 PM
  7. [SOLVED] How to select a range based on values in 1 column
    By ForestRamsey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-30-2005, 12:05 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