Hi all, new to the forum. Still getting my feet wet with Excel too I guess!
I have a table of data with two columns, one is a time/date stamp and the other is a rate (See the second table below). I only want to log reduced rate or downtime events and their duration, and record the time stamps when they change in another table. The goal is to set it up so that when the data is refreshed (downloaded from another program), the table automatically updates with new time stamps and rate changes.
Lets say maximum is X, reduced is Y, and shutdown is 0. In my table I have a mix of those numbers, usually stays constant at one state or the other for long stretches of time. So I want to look through the table and find the first point where rate is Y or 0 and record the starting time stamp and then find the ending time stamp (when rate changes again). Then I want to start at the ending time stamp from that last rate record and find the next change or place where it is not maximum. This is what I want the table to look like:
Rate| Start| End| Hours
Y| 01-Jun-12 02:00:00| 01-Jun-12 04:30:00| 2.5
Y| 01-Jun-12 11:30:00 | 01-Jun-12 17:00:00| 5.5
0| 03-Jun-12 19:30:00 | 04-Jun-12 05:30:00| 10.0
Y| 05-Jun-12 21:00:00 | 06-Jun-12 08:30:00| 11.5
I am having trouble using the previous time stamp to update the range for VLOOKUP. It keeps giving me a #VALUE error when it evaluates the VLOOKUP, even though it will calculate the range correctly. Here is the formula I am using:
VLOOKUP(Y, ADDRESS(MATCH($G$4,A1:A17521,0),1)&":"&ADDRESS(MATCH("No Data",B1:B17524,0),2),1,FALSE)
For our purposes, G4 is the first value on the "End" column in the table above, and this formula would be entered into F5 ("01-Jun-12 11:30:00") to obtain the next value. "No Data" just means the end of the useful range, or up to the present (this is process data). I want this formula to return the time stamp at the Y value in the time stamp range from G4 to present. Then G5 will do the same thing, but just using F5 as the reference cell, and looking for either 0 or X as the rate. Then F6 looks at G5, G6 looks at F6, etc.
FYI, data is imported in this format (two columns):
Time | Rate
01-Jun-12 03:00:00| Y
01-Jun-12 03:30:00| Y
01-Jun-12 04:00:00| Y
01-Jun-12 04:30:00| Y
01-Jun-12 05:00:00 | Y
01-Jun-12 05:30:00 | Y
01-Jun-12 06:00:00 | Y
01-Jun-12 06:30:00 | Y
01-Jun-12 07:00:00 | X
01-Jun-12 07:30:00| X
01-Jun-12 08:00:00 | X
01-Jun-12 08:30:00 | X
01-Jun-12 09:00:00 | X
01-Jun-12 09:30:00 | 0
01-Jun-12 10:00:00 | 0
01-Jun-12 10:30:00 | 0
Sorry, that was a lot. Any clue why it won't return the time stamp I want? Thanks in advance!
Bookmarks