COUNT the n number of consecutive values

[TanNar]

Hi,

I want to calculate the count of n consecutive records as 1 .
I have a data set which have 2 columns,Timestamp & value. Data is coming every second.Sometime there are 2 values per second.
If i get different values which are not in range continuously for next t hours or may be for full day then don't count every instance instead of that count it as 1.

E.g. Operating range : max=170 & min=120

7/8/2014 12:01:01 AM 169.071685791015 - In Range
7/8/2014 12:01:01 AM 169.114990234375 - In Range
7/8/2014 12:01:02 AM 178.788208007812 - Out Of Range
7/8/2014 12:01:03 AM 175.076995849609 - Out Of Range
7/8/2014 12:01:04 AM 169.071695791015 - In Range
7/8/2014 12:01:04 AM 179.071685791015 - Out Of Range
7/8/2014 12:01:05 AM 129.071685791015 - In Range
7/8/2014 12:01:06 AM 119.071685791015 - Out Of Range
7/8/2014 12:01:07 AM 109.071685791015 - Out Of Range
7/8/2014 12:01:07 AM 107.071685791015 - Out Of Range
7/8/2014 12:01:08 AM 126.071685791015 - In range
7/8/2014 12:01:09 AM 105.071685791015 - Out Of Range


Total out of range = 7 but i want to consider consecutive out of range values count as 1 till it comes in range. I want to execute this in excel using some formula which will give me count as 4.

Please reply ASAP.
.

 

[WarPigl3t]

Function myFunction()
lastRow = Range("B" & Rows.Count).End(xlup).Row
i = 2  'If you don't have headers, i = 1
c = 0
output = True
Do Until i > lastRow
     If (Range("B" & i).value < 120 Or _
          Range("B" & i).value > 170) And _
          output = True Then
          output = False
          c = c + 1
     Else
          output = True
     End If
     i = i + 1
Loop
myFunction = c
End Function

Type =myFunction() into whatever cell you want.

 

[WarPigl3t]

Function myFunction()
lastRow = Range("B" & Rows.Count).End(xlup).Row
i = 2  'If you don't have headers, i = 1
c = 0
output = True
Do Until i > lastRow
     If Range("B" & i).value < 120 Or _
          Range("B" & i).value > 170 Then
          If output = True Then
               output = False
               c = c + 1
          Else
               output = True
          End If
     Else
          output = True
     End If
     i = i + 1
Loop
myFunction = c
End Function

If the last code I gave you doesn't work, try this one. But this one isn't as neat or as easy to understand so only use it as a backup.

 

[AlphaFrog]

Hi TanNar and welcome to the forum.

Try something like this.

Book1
	A	B	C
1	Operating range
2	Max	Min	Count Out Of Range
3	170	120	4
4
5	7/8/2014 12:01:11 AM	169.0716858	In Range
6	7/8/2014 12:01:11 AM	169.1149902	In Range
7	7/8/2014 12:01:12 AM	178.788208	Out Of Range
8	7/8/2014 12:01:13 AM	175.0769958
9	7/8/2014 12:01:14 AM	169.0716958	In Range
10	7/8/2014 12:01:14 AM	179.0716858	Out Of Range
11	7/8/2014 12:01:15 AM	129.0716858	In Range
12	7/8/2014 12:01:16 AM	119.0716858	Out Of Range
13	7/8/2014 12:01:17 AM	109.0716858
14	7/8/2014 12:01:17 AM	107.0716858
15	7/8/2014 12:01:18 AM	126.0716858	In Range
16	7/8/2014 12:01:19 AM	105.0716858	Out Of Range
Sheet1

<br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=COUNTIF(<font color="Blue">C5:C16,"Out Of Range"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">B5<=$A$3,B5>=$B$3</font>),"In Range",IF(<font color="Red">C4="In Range","Out Of Range",""</font>)</font>)</td></tr></tbody></table></td></tr></table>

Copy the C5 formula down column C for each entry

 

[TanNar]

Thanks for quick reply.
As i am not so good in excel din't understood what exactly i should do. I have typed myFunction() in one of the cell of same sheet.
But how should i execute that function, so that i will get the desired result. Will you please elaborate it stepwise ?

 

[TanNar]

Thanks for your quick reply. And solution given by you is working perfectly fine.
Can you tell me one more thing, how to add timestamps of all consecutive & non-consecutive Out Of Range values ? i want to calcualte Sum of timestamps of all 7 Out Of Range.. It should show the total time in a day for which the value was deviated.

Hi TanNar and welcome to the forum.

Try something like this.

	A	B	C
1	Operating range
2	Max	Min	Count Out Of Range
3	170	120	4
4
5	7/8/2014 12:01:11 AM	169.0716858	In Range
6	7/8/2014 12:01:11 AM	169.1149902	In Range
7	7/8/2014 12:01:12 AM	178.788208	Out Of Range
8	7/8/2014 12:01:13 AM	175.0769958
9	7/8/2014 12:01:14 AM	169.0716958	In Range
10	7/8/2014 12:01:14 AM	179.0716858	Out Of Range
11	7/8/2014 12:01:15 AM	129.0716858	In Range
12	7/8/2014 12:01:16 AM	119.0716858	Out Of Range
13	7/8/2014 12:01:17 AM	109.0716858
14	7/8/2014 12:01:17 AM	107.0716858
15	7/8/2014 12:01:18 AM	126.0716858	In Range
16	7/8/2014 12:01:19 AM	105.0716858	Out Of Range

<COLGROUP><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Worksheet Formulas Cell Formula C3 =COUNTIF(C5:C16,"Out Of Range") C5 =IF(AND(B5<=$A$3,B5>=$B$3),"In Range",IF(C4="In Range","Out Of Range","")) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Copy the C5 formula down column C for each entry

 

[AlphaFrog]

Sum of timestamps of all 7 Out Of Range..
It should show the total time in a day for which the value was deviated.

The Sum of timestamps of all 7 Out Of Range doesn't give you the total time in a day for which the value was deviated.

I don't know how to calculate the total time in a day for which the value was deviated for your data