dcunningham
Board Regular
- Joined
- Jul 14, 2015
- Messages
- 58
Hi Everyone,
First, 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:
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.
The end result based on the above data would look something like:
Where the highlighted values would be the number of instances of what I've defined as an 'event'.
Any help you can provide would be greatly appreciated.
First, 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:
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.
The end result based on the above data would look something like:
Where the highlighted values would be the number of instances of what I've defined as an 'event'.
Any help you can provide would be greatly appreciated.
