How to find high and low temperature cycles in a column of temperature data

**landwanderer** · 12-11-2012, 06:34 PM

I have a month worth of temperature data at 5 minute intervals. I need to count the number of times the temperature cycled from <1400 degF to >1900 degF and back down to <1400 degF (this is one cycle). I can determine the first low instance (<1400 degF, I can determine the first high instance (>1900 degF) BUT I'm having difficulty coming up with a formula to determine the second instance of the low and high. Any help would be greatly appreciated.

**FDibbins** · 12-11-2012, 06:44 PM

welcome to the forum

perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).
To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**matt.nicolai** · 12-11-2012, 07:35 PM

Would something like this work?

Put a formula to identify if a entry is the start (over 1400), middle (over 1900) or end (under 1400) of a cycle and then count the starts and ends?

Starting in the second row of data:
=IF(AND(A2>1400, A1<1400),"Beginning",IF(AND(A2>1900,A1<1900),"Middle",IF(AND(A2<1400,A1>1400),"End","")))

Then at the bottom of the column with the formula:
=COUNTIFS(B1:Bx,"Beginning","End")/2

I suppose this doesn't take into account double dips, but it might be a start.