# Need to modify my formula for organizing data with timestamps

1. ## 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

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

SORRY, really don't understand

3. ## 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. ## 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.

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

Thank you very much indeed Everything's cool

6. ## 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?

7. ## 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. ## Re: Need to modify my formula for organizing data with timestamps

Yes, thank you!

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

#### 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