how to count freeze/thaw cycles from time and temperature data

[ad.dias]

Greetings,
I’m currently writing a dissertation about how building envelopes behave in cold climates, specifically, I’m having trouble interpreting a wall’s resistance to freeze/thaw cycles. I have the following data:

I have two columns with 40 000 rows of data.
Colum A is the Time (Number of hours after the experiment starts, 1, 2, 3, 4, 5…)
Colum B is the Temperature (In Celsius)

Example:
(A) (B)
1 -5
2 13
3 16
… …

I need to create a function, or something similar, that can return the number of freeze/taw cycles that occur. Freezing takes place when the temperature is below 0ºC, and thaw takes place when the temperature is above 0ºC. However, I need to take into consideration the following two aspects:

1)The freezing of the wall must take at least 2 hours to occur (There needs to exist 2 consecutive hours, where the temperature is below 0ºC to occur freezing)
2)Two consecutive freeze/thaw cycles must be separated by, at least, 2 hours to occur.

Could someone with a little more expertise help me out on this? I would be very grateful
Kind regards

[FDibbins]

Hi, welcome to the forum

Our forum rules require that your thread title describes what your problem is, but Im not sure what else you could title this that would explain what you need - any other suggestions?

[MrShorty]

I could see a title like "how to count freeze/thaw cycles from time and temperature data" being a good title.

I haven't thought it all the way through, but I would expect the solution will involve the COUNTIFS() function in some way or another. Are you familiar with this function? https://support.office.com/en-us/art...c-aa8c2a866842

I think a larger sample data set, with a few worked examples so we can see how the logic works might also help.

[FDibbins]

Thanks MrShorty - done

[ad.dias]

Hey guys
Here is a more detailed example on how a freeze/thaw cycle occurs:

(A) (B)
1 -10 (Freezing hour 1)
2 -15 (Freezing hour 2, hence, freeze cycle occurs)
3 10 (Thawing hour 1)
4 -6 (Freezing hour 1, remember, thawing has to occur for two consecutive hours in order for a thawing cycle to occur, so this resets the thawing count for the cycle to occur)
5 6 (Thawing hour 2)
6 7 (Thawing hour 3, given that 2 consecutive hours of thawing occur, a thawing cycle occurs)
7 -10 (Freezing hour 1)
8 -11 (Freezing hour 2) (freeze cycle occurs)
9 5 (Thawing hour 1)
10 -10 (Freezing hour 1)
11 7 (Thawing hour 2)
12 9 (Thawing hour 3) (thawing cycle occurs)

I hope this helps =)

[MrShorty]

As I suggested, the COUNTIF() or COUNTIFS() functions are probably at the heart of this. Are you familiar with them.

My first attempt looks like this:
time -- temp -- moving count of subzero -- moving count above zero -- freeze? -- thaw?
1 -- -10 -- =COUNTIFS($B1:$B2,"<0") -- =COUNTIFS($B1:$B2,">0") -- =C2>=2 -- copy of E2 note the relative row references here
2 -- -15 -- copy of C2 -- copy of D2 -- copy of E2 -- copy of E2
and so on.

The TRUE's in columns E and F match up with the bolded entries in your example data. It is not clear to me where 0 fits in this decision -- should temperatures of 0 count towards a freeze or a thaw cycle or not count towards either (I have excluded 0 from both counts in my example so 0 is effectively ignored). The example seems to only be concerned with finding two consecutive hours below or above 0, so I limited the analysis to 2 hours. How would you have counted the freeze thaw cycles if hour 5 had been -1 instead of +6? My formulas would have counted 3 freeze cycles and 1 thaw cycle -- but is that correct?

That should illustrate how to use the COUNTIFS() function. From there it is about figuring out the internal logic that needs to go into the conditions to get the correct counts.

[shg]

1)The freezing of the wall must take at least 2 hours to occur (There needs to exist 2 consecutive hours, where the temperature is below 0ºC to occur freezing)
2)Two consecutive freeze/thaw cycles must be separated by, at least, 2 hours to occur.

It is surely more complicated than that, no?

I'd have thought it would take many hours to thaw at 1 degree from a hard freeze, and many hours to freeze at 1 below from a warm liquid.

[shg]

Ignoring all that,

	A	B	C	D
1	Temp	State	Change
2	-16	Frozen		B2: =IF(A2<=0, "Frozen", "Thawed")
3	-5	Frozen		B3 and down: =IF((B2 = "Thawed")*(A2 < 0)*(A3 < 0), "Frozen", IF((B2 = "Frozen")*(A2 > 0)*(A3 > 0), "Thawed", B2))
4	20	Frozen		C4 and down: =IF(B4=B3, "", "Change")
5	2	Thawed	Change
6	-4	Thawed
7	-4	Frozen	Change
8	11	Frozen
9	-16	Frozen
10	-8	Frozen
11	-2	Frozen
12	8	Frozen
13	4	Thawed	Change
14	-9	Thawed
15	-5	Frozen	Change
16	17	Frozen
17	-1	Frozen
18	-1	Frozen
19	17	Frozen
20	-9	Frozen
21	14	Frozen
22	6	Thawed	Change
23	-20	Thawed
24	17	Thawed

[ad.dias]

Thank you so much guys!

[shg]

You're welcome.