My Problem
A bit of background, I have access to a database that provides me with engine status codes for a vehicle with a time stamp. This engine status code is in decimal, but needs to be converted to binary to be read because each engine status (No Engine Speed, Engine Derate, and so on) are assigned to one of the sixteen bits. I have figured out how to convert these decimal values to binary, and then how to split up the binary string into individual cells. My data now looks like this: https://i.gyazo.com/359785dfe236b81d5545105dfc59a958.png.
That's just a sample, the values go on to row 8656. So, Column E has the time stamps, Column F has the engine status (in decimal, these change even if they do only look to have a value of 1 or 65 in the sample I provided), Column G is Column F converted into 16-bit binary, and Columns H to W are each individual bit of the string in Column G. The black box contains values that I needed to hide, they're used as part of the query to the aforementioned database.
Now, what I'd like to do is count the number of times both of the two following conditions are met for each column in Columns H through W: (1) the value in the column is 1 consecutively 5 times or more, and (2) the time stamps (in column E) for each of those consecutive events are within two minutes of each other. In this instance, an event that meets those conditions is considered an event where one of those engine statuses was actually active and not just a brief blip. I need to know this for each bit column. Ideally these functions would go in H3:W3, above the bit columns. For a point of reference, Number of Values is located in Cell E6.
What I Tried
I put together the following function for criteria (1), but it doesn't work. I get a #Value error.
=SUM(IF(FREQUENCY(IF(W7:W8656=1,COLUMN(W7:W8656)),IF(W7:W8656<>1,COLUMN(W7:W8656)))>=5,1))
For the time stamps I have this:
=IF(E8 - TIMEVALUE("00:02:00")<E7,1,0)
It seems to work, but I don't quite know how I'd implement it. It would be paired with the first function in a single function (perhaps an AND()?)
To Experiment
I uploaded a sanitized version of my worksheet to Google Drive if you'd like to take a closer look at what I'm dealing with. Here's a link to that: https://drive.google.com/open?id=0B_fZnVjXU9YAdS1JRS1VSnFnbmM.
Edit 1:
Here is an example of the function that I'm using in column G.
=DEC2BIN(INT(F7/256),8)&DEC2BIN(MOD(F7,256),8)
And here's what I'm using to split the binary string:
For the first:
=VALUE(MID(G7,1,1))
For the second:
=VALUE(MID(G7,2,1))
And so on.
nickelcap, I had another go at it. This assumes that (a) time-stamp data are in a column starting at $C$7; (b) engine status codes are in a column starting at $D$7, and (c) data can extend down as far as row 8656.
A. Define the minimum acceptable time interval (2 minutes). In cell X1 enter:
=TIME(0,2,0)
B. Create a column of delta-time data in column X (setting the value to zero when there is no data). In cell X8 enter:
=IF(LEN(TRIM($C8))=0,0,$C8-$C7)
and fill down this formula to row 8656.
C. Create a column of binary-formatted engine status codes. This is the same formula you came up with, wrapped in a filter to set the status code to all zeros if there is no data. In cell G7 enter:
=IF(LEN(TRIM(D7))>0,DEC2BIN(INT(D7/256),8)&DEC2BIN(MOD(D7,256),8),"0000000000000000")
and fill down this formula to row 8658.
D. The range H7:W8658 has the same formula you came up with to extract a single bit. E.g. in H7:
=VALUE(MID($G7,1,1))
E. Finally, the formula for counting repeating failure codes. To count failures of the column H events I have this in H4:
=SUM(IF(IF($X$8:$X$8653<=$X$1,1,0)+IF($X$9:$X$8654<=$X$1,1,0)+IF($X$10:$X$8655<=$X$1,1,0)+IF($X$11:$X$8656<=$X$1,1,0)>=4,IF(H$7:H$8652+H$8:H$8653+H$9:H$8654+H$10:H$8655+H$11:H$8656>=5,1,0),0))
(It doesn't have to be in row 4 --- put it where you want.) Fill that formula across to column W.
One the spreadsheet is set up, you can clear out the old data and copy/paste new data in columns C and D to analyze the new data.
Hope that gets you going.