Formula to Count Data in a Range That is Consecutively Outside of a Specification

[templetonjp]

Hello all,

I am trying to create a single formula in a single cell that lists the number of times an out of specification condition occurs for greater than two hours. As an example:

Time Temp
0:00 -15
0:15 -15
0:30 -16
0:45 -16
1:00 -17
1:15 -18
1:30 -20
1:45 -20
2:00 -22
2:15 -23
2:30 -25
2:45 -26
3:00 -27
3:15 -28
3:30 -28
3:45 -28
4:00 -27
4:15 -27
4:30 -26
4:45 -26
5:00 -26
5:15 -25
5:30 -25
5:45 -24
6:00 -23
6:15 -22
6:30 -22
6:45 -23
7:00 -24
7:15 -25
7:30 -26
7:45 -27
8:00 -26
8:15 -22
8:30 -20
8:45 -18
9:00 -17
9:15 -16
9:30 -15
9:45 -15

I'm looking for the number of times the temperature is less than -25 for more than two hours. This occurs one time (from 2:45 - 5:00) although it is out of specification at 13 points total I only need to count that it was consistently out of specification once, in this case.

Any help at all would be much appreciated.

Joe

 

[kieran]

Assuming the data is in A1:B41 (including the headings)
Paste =SUMPRODUCT(--(B2:B9<-25))=8 into C9 and copy down.

The column will read TRUE for each 2 hour period that is outside specification. (C20 to C22)

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​
1​	Time	Temp		Temp Value
2​	0:00​	-15​		-25​
3​	0:15​	-15​		Duration longer than
4​	0:30​	-16​		120​
5​	0:45​	-16​		Sequence count
6​	1:00​	-17​		1​
7​	1:15​	-18​
8​	1:30​	-20​
9​	1:45​	-20​
10​	2:00​	-22​
11​	2:15​	-23​
12​	2:30​	-25​
13​	2:45​	-26​
14​	3:00​	-27​
15​	3:15​	-28​
16​	3:30​	-28​
17​	3:45​	-28​
18​	4:00​	-27​
19​	4:15​	-27​
20​	4:30​	-26​
21​	4:45​	-26​
22​	5:00​	-8​
23​	5:15​	-25​
24​	5:30​	-25​
25​	5:45​	-24​
26​	6:00​	-23​
27​	6:15​	-22​
28​	6:30​	-22​
29​	6:45​	-23​
30​	7:00​	-24​
31​	7:15​	-25​
32​	7:30​	-26​
33​	7:45​	-27​
34​	8:00​	-26​
35​	8:15​	-22​
36​	8:30​	-20​
37​	8:45​	-18​
38​	9:00​	-17​
39​	9:15​	-16​
40​	9:30​	-15​
41​	9:45​	-15​

<tbody>
</tbody>

D6, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$41 < D$2,ROW($B$2:$B$41)),
    IF($B$2:$B$41 >= D$2,ROW($B$2:$B$41)))*15 > D$4,1))

 

[templetonjp]

Aladin,

This works really well, exactly what I was looking to accomplish. Could you possibly appease my curiosity and explain how this accomplishes the goal. I know a little about the FREQUENCY command and know IF statements but get lost in the nesting and how it all works together.

Thank you again,

Joe

 

[templetonjp]

Aladin,

One other thing, if I were also trying to get a count of times it was >-15 in a much larger data set that contained a lot of blanks, is there a way to get this formula to ignore blanks?

Thanks,

Joe

 

[Aladin Akyurek]

Aladin,

This works really well, exactly what I was looking to accomplish. Could you possibly appease my curiosity and explain how this accomplishes the goal. I know a little about the FREQUENCY command and know IF statements but get lost in the nesting and how it all works together.

Thank you again,

Joe

The first IF determines which rows meet the condition (stated as < -25). The second IF delivers the bins the FREQUENCY function needs...

FREQUENCY(IF($B$2:$B$41<D$2,ROW($B$2:$B$41)),IF($B$2:$B$41>=D$2,ROW($B$2:$B$41)))

>>

FREQUENCY({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;13;14;15;16;17;18;19;20;21;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;32;33;34;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},IF($B$2:$B$41>=D$2,ROW($B$2:$B$41)))

*This evaluation shows that a sequence of < -25 starts at row 13 continues up till 21.

>>

FREQUENCY({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;13;14;15;16;17;18;19;20;21;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;32;33;34;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{2;3;4;5;6;7;8;9;10;11;12;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;22;23;24;25;26;27;28;29;30;31;FALSE;FALSE;FALSE;35;36;37;38;39;40;41})

* This creates bins of rows where the condition does not hold.

>>

{0;0;0;0;0;0;0;0;0;0;0;9;0;0;0;0;0;0;0;0;0;3;0;0;0;0;0;0;0}

which says that there are row values from the first IF that fall in the first set of eleven bins. On the other hand, 13, 14, etc. are all bigger than the bin of 12 and less then the bin of 23, therefore all fall (nine of them) in the bin of 22. That's the first count. The second
count of 3 I leave to you to evaluate.

Hope this helps.

 

[Aladin Akyurek]

Aladin,

One other thing, if I were also trying to get a count of times it was >-15 in a much larger data set that contained a lot of blanks, is there a way to get this formula to ignore blanks?

Thanks,

Joe

How do you want to treat blanks in 'temp'?

 

[templetonjp]

I prefer to just ignore the blanks. I was mainly just thinking I could change the range to be really long and easily copy and paste it to other data sets without having to change the range, so all the blanks would be at the end, but I don't want to count the tail of blanks.

Sometimes I have blanks that are introduced because the time step changes to 1min intervals but in those cases I may have to do something different.

 

[Aladin Akyurek]

I prefer to just ignore the blanks. I was mainly just thinking I could change the range to be really long and easily copy and paste it to other data sets without having to change the range, so all the blanks would be at the end, but I don't want to count the tail of blanks.

Sometimes I have blanks that are introduced because the time step changes to 1min intervals but in those cases I may have to do something different.

~

Row\Col	A​	B​
1​	Time	Temp
2​	0:00​	-15​
3​	0:15​	-15​
4​	0:30​	-16​
5​	0:45​	-16​
6​	1:00​	-17​
7​	1:15​	-18​
8​	1:30​	-20​
9​	1:45​	-20​
10​	2:00​	-22​
11​	2:15​	-23​
12​	2:30​	-25​
13​	2:45​	-26​
14​	3:00​	-27​
15​	3:15​
16​	3:30​
17​	3:45​
18​	4:00​	-27​
19​	4:15​	-27​
20​	4:30​	-26​
21​	4:45​	-26​

What result(s) do we have when the data looks like above?

 

[templetonjp]

The result for above would be fine. It is less than 2 hours. If the blanks lasted longer then 2 hours and temperatures greater than -15 were being counted then there would be a count of 1.