Excel Question

[driryos]

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

 

[Coding4Fun]

If you can provide more sample data and call out what you need a little clearer I could help.
I get when you say it has to be 2 consecutive hours under / over zero to be considered freezing or thawing but not fully understanding the comment "Two consecutive freeze/that cycles must be separated by, at least, 2 hours to occur."

For example
(A) (B)
1 -10
2 -15 - Freezing
3 10
4 12 - Thawing

Count as 1 freeze cycle and 1 thaw cycle or

For example
(A) (B)
1 -10 - Freeze Hour 1
2 -15 - Freezing
3 10 - 1 Hour pass
4 12 - 2 Hour pass
5 8 - Thaw Hour 1
6 12 - Thawing

 

[driryos]

Hi there
Thank you for the response

Your first example is correct.
Here is a more detailed example:

(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 =)

 

[Coding4Fun]

I am not sure how you wanted to handle consecutive occurrences, for example if it is freezing for 4 hours in a row would this be counted as 1 or 2 freezing cycles, I am counting this as 1 freezing cycle. If that is not right then this would need to be adjusted.

This was the sample table i used as a base and what I was considering to be freezing/thawing cycles
You would show 8 freezing and 7 thawing cycles with the data below.

(A) (B)
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]-10[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]-10[/TD]
[TD]Freeze[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD]Thaw[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]-10[/TD]
[TD]Freeze[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD]Thaw[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]-10[/TD]
[TD]Freeze[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]20[/TD]
[TD]Thaw[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]-10[/TD]
[TD]Freeze[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]20[/TD]
[TD]Thaw[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]-10[/TD]
[TD]Freeze[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD="align: right"]-10[/TD]
[TD]Freeze[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD="align: right"]20[/TD]
[TD]Thaw[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]-10[/TD]
[TD]Freeze[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD="align: right"]20[/TD]
[TD]Thaw[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]48[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]-10[/TD]
[TD]Freeze[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]52[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]53[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]56[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]57[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]58[/TD]
[TD="align: right"]20[/TD]
[TD]Thaw[/TD]
[/TR]
[TR]
[TD="align: right"]59[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub Test()

LR = Cells(Rows.Count, "A").End(xlUp).Row
Freeze = 0
Thaw = 0


For i = 2 To LR


    If Range("B" & i) < 0 And Range("B" & i - 1) < 0 Then
    
        If i <= 2 Then
            Freeze = Freeze + 1
        ElseIf i > 2 Then
        
            If Range("B" & i - 2) > 0 Then
            Freeze = Freeze + 1
            End If
        
        End If
        
    ElseIf Range("B" & i) > 0 And Range("B" & i - 1) > 0 Then
    
        If i <= 2 Then
            Thaw = Thaw + 1
        ElseIf i > 2 Then
        
            If Range("B" & i - 2) < 0 Then
            Thaw = Thaw + 1
            End If
    
        End If
        
    End If


Next i


MsgBox ("Freeze: " & Freeze & " " & "Thaw: " & Thaw)




End Sub

 

[driryos]

Thank you so much! Cheers

 

[Coding4Fun]

Glad to help.