Measure cycles in a log file

Hello, nice to see that there was a forum out there for help with Excel!

Im currently workning on an assignment where im trying to measure the amount of temperaturcycles that a log file contains.

The log file has been used to measure the water temperature in a pipeline every 5th minute. It contains about 18 000 rows of information. I'd like to use a function to measure how many times the temperature changes by atleast 5 degrees. Meaning if i have the following values 91,91,91,87,86 it will count that as one cycle. It would be of interest if it was possible to make excel count that as 1 cycle and then continue to count from the last value( =86 +/- 5). Thereby giving me the total amount of changes by atleast 5 degrees in the llog file.

Is it possible to make such a function?

Thanks in advance for ur help

This kind of does what you want, I think. You have to play around with the parameters and test it.

Originally Posted by Gsten

I've been trying to understand the functions fully, and well change some values. Correct me if im wrong, but i've tried to change the "Amount of degree change" to 1 which makes it count the "Number of changes" to 13, shouldn't it be alot higher as it should count every change larger then 1?

The way I'm thinking is that a change is only one change as long as it is continously rising or falling, no matter how many degrees. As soon as the temperature levels out it is ready to register a new change.
It's like the fluctuating values (your data) are moving within a corridor that is 5 degrees wide. It can fluctuate within that corridor without register a change but as soon as it fluctuates enough to hit any of the walls it will move the whole corridor sideways and any movement of the corridor will register as a change. The data can then fluctuate within the new position of the corridor without register a change. Eventually it will hit a wall again and cause another movement of the corridor and register another change.

Another way of looking at it is as a very crude filter (hysteresis filter). It just filters out changes smaller than a certain amount and then counts the number of changes that are bigger.

Originally Posted by Gsten

For Example if you start of with the value of R2 as 63.

=COUNTIFS(R3;">63") , =COUNTIFS(R3;"<55") and then =OR(AC3;AD3) --> This will make it return FALSE if R3 is not lower then 55 or higher then 63 and TRUE in other cases.

i've been trying to make a function which then adds 5 to the upper and lower limit of that current value if it returns as TRUE and continues to count FALSE/TRUE. In the end you could then just summarize the amount of "TRUE."

This does not sound that much different from my corridor analogy, except you make it jump 5 instead of following a continous movement.
I implemented that too for comparison but that is not the method I would have used.