0

I am attempting to create some graphs with dynamic cell references as indices. These graphs are based upon time stamps generated from a testing machine. I have a column of Boolean values (0 or 1) that mark "force logs", and a spreadsheet of various fields of data for machine. This machine also produces a spreadsheet on a separate workbook (this spreadsheet contains actual time stamps that mark start and end times) that I am manually copying and pasting into another sheet of the workbook with the data (it has approximately 5000 rows). I want to locate my beginning and end indices for my graph based on time stamps. Unfortunately, the separately produced Excel workbook with the correct time stamps to choose from lags around 1 second from the time stamp on the main data spreadsheet (this lagging is variable but between 0 and 2 seconds). Furthermore, my "force log" Boolean values are supposed to be 1 for time stamps, but often times I have extra force logs that do not correspond to the correct times for starting the graph. Below is a representative sample as per requested.

Worksheet containing data (Call this Sheet 2)
  A                     B           C          D
  Time Stamp            Force Log  Field 1    Field 2   
1 2/6/2015 3:21:22 PM   1          100        500      <- Extraneous boolean value
2 2/6/2015 3:21:23 PM   0          101.4      499.2
3 2/6/2015 3:21:24 PM   1          99.3       501.4    <- I want this index for graph start
4 2/6/2015 3:21:25 PM   0          100.3      498.9
5 2/6/2015 3:21:26 PM   0          102.4      500.7
6 2/6/2015 3:21:27 PM   1          101.8      499.3    <- I want this index for graph end

Worksheet containing machine generated data summary (call this Sheet 1)
Start Time    End Time
3:21:23 PM    3:21:26   <- The times here lead the other worksheet by less than 2 seconds
                        They are also off by 2/6/2015, but I can generate a helper column
After obtaining the indices 3 and 6, I would graph the values of Field 1 and Field 2.

Here is what, from my problem, I have gathered I need to do: Perform fuzzy searching for time stamps by either truncated or conditionally testing for equivalence via a range (plus minus 2 seconds), and make sure that the "force log" Boolean value column is also set to 1.

I am new to Excel reference and search functions, so I am not sure if I am missing something. So far I have tried LOOKUP, VLOOKUP (did not work because my data columns aren't formatted correctly for table_array), INDEX MATCH (did not work because of the fuzzy requirement of one of my searches), and can't seem to find any easy to understand solutions allowing a search with multiple criteria that returns multiple results (I have multiple times that each need a single result, not a single time that needs multiple results; I was struggling to grasp array formulas in Excel).

I greatly thank in advance anyone with a solution or advice.

EDIT: I understand that my question might have a very strange background. Please let me know if you need me to clarify or otherwise elaborate on what exactly I need to do. Thanks!

hedgepig
  • 103
  • 7
  • Possible duplicate of [Excel 2013 Fuzzy Lookup to find near-duplicate text](http://superuser.com/a/926385) – DavidPostill Jun 11 '15 at 20:18
  • David, I am unsure of how my question could be a duplicate of the question you posted. Would you mind elaborating on how I could use that add-on for searching row indices given that I have multiple criteria for searching and only one is fuzzy? Thanks! – hedgepig Jun 11 '15 at 20:21
  • I've never used the addon. That's why I didn't propose it as an answer. It is something you can research yourself. Searching for "Fuzzy Lookup Add-In for Excel examples" gives some links you could investigate. – DavidPostill Jun 11 '15 at 20:26
  • I understand that; however, I am not interested in fuzzy matching of textual data, I am interested in matching numbers based on a small difference in times. In any case, I do not think that my question is a *duplicate* of the one asked, even though the answer might be the same. I will look into the add-in when I get off work, however. Thanks! – hedgepig Jun 11 '15 at 20:28
  • 1
    I don’t care about the background of your real-world problem; I care that I have barely a clue as to what you want to do with your data.  Please [edit] your question to include sample data.  Not your real 5000 rows; and not 50 rows (unless you really need to, in order to illustrate your problem); five to ten rows of made-up data with just as many columns as you need.  Obviously, you need to include the timestamp column.  I guess you need to include the Boolean column, although I don’t understand what it has to do with anything.  Do you ***need*** to include any other columns  … (Cont’d) – Scott - Слава Україні Jun 12 '15 at 05:22
  • (Cont’d) …  to illustrate the problem?  Post inputs (do you have three sheets of input, or only two?) and outputs (*is* the third sheet your output, or is there something else?), and enough of a description of the desired processing to make it clear.  Maybe if I saw the inputs and outputs, it would instantly become clear, but it’s not clear (to me) now.  Don’t worry about posting snapshot images; just post text, as was done [here](http://superuser.com/q/889201/150988) and [here](http://superuser.com/q/892744/150988).  (That makes it easier for people to copy and paste into their own systems.) – Scott - Слава Україні Jun 12 '15 at 05:22
  • Thank you for the information Scott - I am new to asking these types of questions here so it did not immediately occur to me to do this. I added a short sample table; hopefully it is more clear to everyone why I need to filter Boolean values under the "Force Log" category as otherwise it is impossible to discern the appropriate time stamps. Please let me know if there is anything else I can/should do to make my question more clear. – hedgepig Jun 12 '15 at 11:29
  • 1
    Here's how I understand your question: You have a start time and end time (Are they reversed in your example?) and you have a table of data. You want to find the times in that table that are closest to your start and end times where `Force Log` is `1`. Is that correct? – Engineer Toast Jun 12 '15 at 12:50
  • Yes. That's exactly what I want. I need the row number of the time for usage in generating graphs with a specific data range – hedgepig Jun 12 '15 at 12:56
  • Two more questions: Are the start times and end times always 0-2 seconds *before* or *after* the times in the data table? Are the start and end times in your example accurate? (There's nothing within 2 seconds of 3:21:33.) – Engineer Toast Jun 12 '15 at 13:00
  • Sorry! My times for start and end were incorrect. I fixed those. The start and end times should always be 0-2 seconds before the times in the data table (not after). – hedgepig Jun 12 '15 at 13:15

1 Answers1

1

Here's a solution that will give you the index numbers for the start and end points. You can use those two numbers to create a dynamic named range for the values you want to graph.

First, here are the two things I did to your data to make the formulas easier. You don't have to do these, too, but you'll need to change a few pieces of the formula to match whatever your data setup is.

  1. I converted your data to a table with the default name Table1. When a formula is referencing all the values for "Time Stamp", you'll see it as Table1[Time Stamp].
  2. I named the two cells with the start and end times StartTime and EndTime to make the formula easier to follow. Otherwise, it would show up as A10 and B10 and it wouldn't be quite as easy to follow.

Here's the formula to give you the index number for the start time:

{=MATCH(1,IF(Table1[Force Log]=1,IF(HOUR(Table1[Time Stamp])=HOUR(StartTime),IF(MINUTE(Table1[Time Stamp])=MINUTE(StartTime),IF(SECOND(Table1[Time Stamp])-SECOND(StartTime)<=2,IF(SECOND(Table1[Time Stamp])-SECOND(StartTime)>=0,1))))),0)}

...and here it is for the end time:

{=MATCH(1,IF(Table1[Force Log]=1,IF(HOUR(Table1[Time Stamp])=HOUR(EndTime),IF(MINUTE(Table1[Time Stamp])=MINUTE(EndTime),IF(SECOND(Table1[Time Stamp])-SECOND(EndTime)<=2,IF(SECOND(Table1[Time Stamp])-SECOND(EndTime)>=0,1))))),0)}

The key point is that they're both array formulas so they must be entered with Ctrl+Shift+Enter. Don't try to paste in the curly brackets { } because it won't work. Paste in everything but those, use Ctrl+Shift+Enter, and the curly brackets { } will show up.

Here's how it works:

It's really just a bunch of nested IF formulas that result in an array of 1's and 0's with a MATCH formula that looks for the first 1. The IF statements setup a series of criteria and, if every criteria is met, then the value is 1. If any of them are not met, then the value is 0. (Well, it's actually FALSE but that evaluates to 0.) It checks that Force Log = 1, the hour is the same, the minute is the same, and that the seconds are inclusively within +0 / +2 seconds of the time you're seeking. Note that I did not check that the date was the same as your start and end times don't include date values.

Key Point: If there are multiple records that meet this criteria (i.e., if the time stamp 2/6/2015 3:21:23 PM in your example had a Force Log value of 1) then this formula will find the first record that matches the criteria.

Engineer Toast
  • 4,955
  • 1
  • 20
  • 33