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!

11
  • Possible duplicate of Excel 2013 Fuzzy Lookup to find near-duplicate text
    – DavidPostill
    Jun 11, 2015 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, 2015 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, 2015 at 20:26
  • 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) Jun 12, 2015 at 5:22
  • 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? Jun 12, 2015 at 12:50

1 Answer 1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .