Excel question

[pjmac73]

Hello -

I am importing a temperature program into excel. Imported will be set point (SP), temp internal (TI), temp external (TE), and time. I want to set up a template that will recognize when then TE settles out (i.e. 40 ± 0.1) and then record the temp from another row. Looking at the attachment, recognize A, and then record B. Does anyone know the equations that I should be looking into? Thank you.

 

[zelgar]

Based on your attachment, everything that is in the TE column meets the condition 40 ± 0.1 (i.e., 39.9 - 40.1) and all of the data listed is from 39.98 - 40. So I'm uncertain why none of the other data except for time 188.1667 identified as B was selected (it appears it was the first time TE = 40)

 

[pjmac73]

zelgar - I want to set up a program that will recognize when a TE reaches 40 and then stabilizes (that's the 40 ± 0.1), then records only the time, 188.1667. I could of been more clear. TE reaches 40 and then stays within ± 0.1. Sorry for the confusion.

 

[MintJulep]

Well, firstly you need to establish a complete definition for "stabilizes".

For a second? A minute? An hour? Forever? For all subsequent recorded data?

 

[pjmac73]

In this case, once TE reaches 40 and stays within 40 ± 0.1 for 3 data points which is 15 seconds.

 

[zelgar]

the following macro may work:

Sub TE()
Dim iRow As Integer
Dim iCol As Integer
Dim target, range

iRow = 5        ' Starting Row of Data
iCol = 6        ' Column containing TE data

target = 40
range = 0.1

Do Until IsEmpty(Sheet1.Cells(iRow, iCol))
    If Sheet1.Cells(iRow, iCol) = target And _
        Sheet1.Cells(iRow + 1, iCol) >= target - range And _
        Sheet1.Cells(iRow + 1, iCol) <= target + range And _
        Sheet1.Cells(iRow + 2, iCol) >= target - range And _
        Sheet1.Cells(iRow + 2, iCol) <= target + range And _
        Sheet1.Cells(iRow + 3, iCol) >= target - range And _
        Sheet1.Cells(iRow + 3, iCol) <= target + range Then
        t = Sheet1.Cells(iRow, iCol - 3)
        With ActiveSheet
            iRow = .Cells(.Rows.Count, iCol).End(xlUp).Row        ' This skips to the end of the data
        End With
    End If
    iRow = iRow + 1
Loop
Cells(1, 1) = t     ' Put the found time in Cell A1  (note: Cells(Row, Col) for cell B5 change to Cells(5,2)
End Sub

 

[pjmac73]

Thanks zelgar. Ill give it a try

 

[Mutt]

You could also add in another column with AND function :
So for cells below, in column E you could have expression in cell E5 something like
=AND(D5=40,ABS(D6-40)<0.1,ABS(D7-40)<0.1,SUM($E$4:E4)=0)*C5

The answer is found by summing all column E or looking for max in column E.

C D E F G H
4
5 187.75 39.99 0.00
6 187.83 39.99 0.00
7 187.92 39.99 0.00 Answer 188.17
8 188.00 39.99 0.00
9 188.08 39.99 0.00
10 188.17 40.00 188.17
11 188.25 39.99 0.00
12 188.33 40.00 0.00
13 188.42 40.00 0.00
14 188.50 40.00 0.00
15 188.58 39.99 0.00
16 188.67 40.00 0.00
17 188.75 40.00 0.00

 

[IFRs]

Why the curly brackets { ... } in F13 ?