Counting runs and how to restart them with dynamic criteria

[TP1337]

Hi there,

I'm using k-RLE-compression to compress data, see wiki article for RLE: https://en.wikipedia.org/wiki/Run-length_encoding

I have sensor data that comes with a timestamp. I'd like to count the run length of the sensor data

1) RLE: if two consecutive values differ then the counter will restart at 1
2) k-RLE: if value(n+1) is within value(n)-k <= value(n+1) <= value (n)+k then counting continues; otherwise the counter restarts.

This sounds kind of complicated, but the demo file should explain it very well. I can do 1) with something like "=(B2=B1)*D1+1", but this is neither a good solution nor does it work with application 2).

Thank you very very much in advance for your help!

Best regards

[Greg M]

Hi there,

Take a look at the attached workbook and see if it gets you moving in the right direction.

Runs are counted on the basis of the difference in their values relative to the tolerances specified in Row 2.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[TP1337]

Wow, just amazing! Thank you so much Greg M. It took me four hours just to get the basic colum without tolerance working. You solved the whole "mistery" within seconds.

May I ask you one more thing - it would be great if you could show me how to find the maxima and copy these"max"-rows to the right. I attached a file. In principle it's sufficient to copy the max-rows right next to the regular rows because I know how to delete blank rows at least something.

For the maxima I used something like "=AND(G4<G3; G4<G5)". Please let me know if you have a better solution for the maxima. What's really important to me would be the automatic copy of the max-row to the right (and maybe also in another table without blank rows, see attached file). This way I could draw charts very easily...

Thank you so much. You saved me a lot of time!

[Greg M]

Hi again,

Many thanks for your feedback and also for your kind words - glad I'm able to help out.

Take a look at the attached workbook and see if it does what you need.

The columns highlighted in grey can be hidden if required.

Clicking on the "Create Data Table" button will produce a list of the values you require, with no blank rows included.

The VBA code involved is as follows:

Please Login or Register  to view this content.

The highlighted values can be altered to suit your own worksheet layout.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[TP1337]

Hi,

thanks alot. Now that I've got all data ready I can continue with the charts. I'm used to Excel 2003/2007 and 2013 is really driving me crazy... In the past I also worked with Origin which made charts very comfortably.

I attached a file with the final data. My application compresses the temperature data which gives me less information than the raw data. This means that I have less data points on the x-axis, e.g. daytime from 08 am to 10 am. Important: the time range! remains the same. There are just less datapoints within the given range.

My problem is that I can't find a way to join two or even three tables. I'd like to have a common timeline, e.g. from 8 am to 4 pm. Every tableset (Tolerance 0, 0.1 and 0.2) has temperature values which corresponds to a specific time ("t_max).
I want to map the specific time in the three tablesets to a common x-axis timeline. I'd like to define the range of the timeline and automatically add value pairs containing time and sensor data. So mapping specific time to a common timeline just won't work. I can't just have one big table because I won't know which temperature value corresponds to which Tolerance-factor.

I hope I made it clear. Thank you very much in advance!

[TP1337]

One more question related to your solution "286 - Counting Runs - 2.xlsm":
Tolerance 0,0 and 0,1 work perfectly, but I didn't properly explain the k-RLE-algorithm and how Tolerance 0,2 should work. In your solution the counter restarts if the difference between two consecutive values is greater than 0,2. I read my post again and realized that I didn't point that out at all. My fault!
In my situation (k-RLE with k=0,2) the algorithm will save the first temperature value, lets call it temp1 and set it to temp1=26,8. The next temperature is always measured against the current temp1. So the counter won't restart if the temperature values are within 26,6<=temp1<=27,00. I attached a file to explain what I mean.
The solution should be able to remember temp1 and check one temperature value after another against temp1 +- 0,2. With 27,1 we have a value which is not within 26,6<=temp1<=27,00 so the new "index" is 27,10. From that on all following temperature values will me compared against 27,10. 27,4 is the next value which is not within 26,9<=temp1<=27,30 and so on.

Would it be possible that you solve this problem? I'm very thankful for you help!

[Greg M]

Hi again,

The layout of your various sets of data certainly doesn't make life easy as far as creating graphs is concerned!

Take a look at the attached workbook "286 - Counting Runs - 3" - clicking on the "Collect Graph Data" button will retrieve the data from the "Raw Data" worksheet and assemble them in a table whose layout is suitable for populating the two graphs.

The attached workbook "286 - Counting Runs - 4" takes account of the requirements for the k-RLE algorithm. An additional helper column is needed - this is highlighted in grey and may be hidden if required.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M