+ Reply to Thread
Results 1 to 8 of 8

Need to modify my formula for organizing data with timestamps

  1. #1
    Registered User
    Join Date
    04-28-2013
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    93

    Need to modify my formula for organizing data with timestamps

    Dear All,

    I attach the file with my data. I have the following formula which selects the very first observation in each minute:

    =IF((A2=MIN(IF(HOUR($A$2:$A$150)&MINUTE($A$2:$A$150)=HOUR($A2)&MINUTE($A2),$A$2:$A$150))*(COUNTIF($A$2:$A2,$A2)=(COUNTIF($A$2:$A150,MIN(IF(HOUR($A$2:$A$150)&MINUTE($A$2:$A$150)=HOUR($A2)&MINUTE($A2),$A$2:$A$150)))))),B2,"")

    So for 09:04 it displays the first value (the one that occured first) and so on. What I need is a modification of this formula to display the first value over a 5-minute interval. For example, it starts from 09:04:48 (when the first price occured) and displays the first value over the interval 09:04:48 - 09:09:48, then it continues with the next 5-minute interval. Any help will be really appreciated! Thanks in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Need to modify my formula for organizing data with timestamps

    SORRY, really don't understand

  3. #3
    Registered User
    Join Date
    04-28-2013
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Need to modify my formula for organizing data with timestamps

    If you look at the data, the column with timestamps: it starts from 09:04:48, you have 41 prices that occur at 09:04:48, then you have one at 09:04:53. I only need the earliest observation in each minute - so the first observation. If you continue, the next observation is at 09:05:00, the one after that is at 09:05:02, then at 09:05:11 and so on. For this minute, I need only the first observation that occurrred, i.e. the one at 09:05:00. If you go to the next minute, we have observations at 09:06:04, then 09:06:08 and so on, I need the first observation for that minute, so the one at 09:06:04. This is what my formula does at the moment. What I want to achieve is to edit it in such a way that it will start with the first observation at 09:04:48 and it will display the earliest value from 09:04:48 until 09:09:48 (five-minute interval). Then, for the next five minutes (from 09:09:48 to 09:14:48) it will once again display the earliest observation that occurs and so on. Hope that this clarifies things a little bit

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,499

    Re: Need to modify my formula for organizing data with timestamps

    Not sure why your formula is so complex since where it appears in your spreadsheet isnt clear but here's what I did.
    In E2, = MIN(A:A)
    In E3 copied down
    =IF(OR(E2=MAX(A:A),E2=""),"",IF(E2+"00:05">MAX(A:A),"",INDEX($A$2:$A$150,MATCH(E2+"00:05", A2:A150)+1)))
    This gives you the first value that's equal to or greater than E2 + 5 minutes. Note, each value is based 5 minutes from the last value, not the first value (i.e. E4 is based on 5 minutes from E3, E5 is based on E4 and so on as opposed to all being in 5 minute increments from E2). The formula can be modified if you want everything based on the minimum value.

    Then in F2 copied down
    =IFERROR(INDEX($B$2:$B$1000, MATCH(E2, $A$2:$A$1000,0)),"")
    Questions?
    See attachment
    Does that work for you.
    Attached Files Attached Files
    Last edited by ChemistB; 05-17-2013 at 10:26 AM.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    04-28-2013
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Need to modify my formula for organizing data with timestamps

    Thank you very much indeed Everything's cool

  6. #6
    Registered User
    Join Date
    04-28-2013
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Need to modify my formula for organizing data with timestamps

    Well, in fact it's not that cool. The data I provide is only a subsample of all my observations. When I try the methodology on the full sample, something gets wrong. Any ideas why that might be the case?
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,499

    Re: Need to modify my formula for organizing data with timestamps

    You didn't anchor your cells A2:A16085 so they changed as you dragged down.

    =IF(OR(E2=MAX(A:A),E2=""),"",IF(E2+"00:05">MAX(A:A),"",INDEX($A$2:$A$16085,MATCH(E2+"00:05", $A$2:$A$16085)+1)))Does that help?

  8. #8
    Registered User
    Join Date
    04-28-2013
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Need to modify my formula for organizing data with timestamps

    Yes, thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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