# COUNT the n number of consecutive values

#### TanNar

##### New Member
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.

.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Code:
``````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.

Last edited:
Code:
``````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.

Last edited:
Hi TanNar and welcome to the forum.

Try something like this.
Book1
ABC
1Operating range
2MaxMinCount Out Of Range
31701204
4
57/8/2014 12:01:11 AM169.0716858In Range
67/8/2014 12:01:11 AM169.1149902In Range
77/8/2014 12:01:12 AM178.788208Out Of Range
87/8/2014 12:01:13 AM175.0769958
97/8/2014 12:01:14 AM169.0716958In Range
107/8/2014 12:01:14 AM179.0716858Out Of Range
117/8/2014 12:01:15 AM129.0716858In Range
127/8/2014 12:01:16 AM119.0716858Out Of Range
137/8/2014 12:01:17 AM109.0716858
147/8/2014 12:01:17 AM107.0716858
157/8/2014 12:01:18 AM126.0716858In Range
167/8/2014 12:01:19 AM105.0716858Out 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

Last edited:
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 ?

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.
ABC
1Operating range
2MaxMinCount Out Of Range
31701204
4
57/8/2014 12:01:11 AM169.0716858In Range
67/8/2014 12:01:11 AM169.1149902In Range
77/8/2014 12:01:12 AM178.788208Out Of Range
87/8/2014 12:01:13 AM175.0769958
97/8/2014 12:01:14 AM169.0716958In Range
107/8/2014 12:01:14 AM179.0716858Out Of Range
117/8/2014 12:01:15 AM129.0716858In Range
127/8/2014 12:01:16 AM119.0716858Out Of Range
137/8/2014 12:01:17 AM109.0716858
147/8/2014 12:01:17 AM107.0716858
157/8/2014 12:01:18 AM126.0716858In Range
167/8/2014 12:01:19 AM105.0716858Out Of Range

</TBODY>

Worksheet Formulas
CellFormula
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",""))

</TBODY>

<TBODY>
</TBODY>

Copy the C5 formula down column C for each entry

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

Replies
20
Views
622
Replies
3
Views
468
Replies
0
Views
113
Replies
18
Views
458
Replies
9
Views
266

1,221,314
Messages
6,159,188
Members
451,544
Latest member
MrsGrayMarlin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back