IF function with 3 consecutive condition

[Researchstudent]

Hello,

I need to write a formula to determine whether an event took place or not. Definition of event is at least 3 consecutive column cells with a value lower than 65. I would like to determine this for a certain period of time, for example in the coming 5 minutes. Every cell represents 20 seconds, so for 5 minutes I will be looking at 15 cells. The question is: are there 3 consecutive values lower than 65 within the 15 cells? If yes, give 1 (event), if no give 0 (no event) . It can also be more than 3 consecutive cells, but it should be at least 3 consecutive values

If I write IF(A1:A15<65;"1";"0") then I am not including the at least 3 consecutive condition. How can I do this?

Here is an example of what my data look like, and I manually added 1 to indicate that the formula should give a 1 in this case

 

[C Moore]

Offset to define your range.
=IF(COUNTIF(OFFSET(A1,0,0,15),"<65")>=3,1,0)
also might replace "<65" with "<"&cellreference to make it updatable.

 

[C Moore]

Oops! Doesn't test for consecutive in the last one.

Offset to define your range in a helper column. Next to the values from A1 down:
=IF(COUNTIF(OFFSET(A1,-2,0,3),"<65")>=3,1,0)
also might replace "<65" with "<"&cellreference to make it updatable.
Then next to that column (C1?):
=IF(COUNTIF(OFFSET(B1,0,0,15),">0")>0,1,0)

 

[Toadstool]

Hi Researchstudent,

Here's my long-winded way:

Researchstudent.xlsx
	A	B	C	D
1	Time	Value		Result
2	15:54:04	77		1
3	15:54:24	74
4	15:54:44	71
5	15:55:04	68
6	15:55:24	65
7	15:55:44	77
8	15:56:04	74
9	15:56:24	71
10	15:56:44	68
11	15:57:04	65
12	15:57:24	62
13	15:57:44	59
14	15:58:04	56
15	15:58:24	53
16	15:58:44	50
17
Sheet1

Cell Formulas
Range	Formula
D2	D2	=IFERROR(ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$9999)/(($B$2:$B$9999<65)*($B$3:$B$10000<65)*($B$4:$B$10001<65)*($B$2:$B$9999<>"")),1)),0)+0

 

[Researchstudent]

Hi Researchstudent,

Here's my long-winded way:

Researchstudent.xlsx
	A	B	C	D
1	Time	Value		Result
2	15:54:04	77		1
3	15:54:24	74
4	15:54:44	71
5	15:55:04	68
6	15:55:24	65
7	15:55:44	77
8	15:56:04	74
9	15:56:24	71
10	15:56:44	68
11	15:57:04	65
12	15:57:24	62
13	15:57:44	59
14	15:58:04	56
15	15:58:24	53
16	15:58:44	50
17
Sheet1

Cell Formulas
Range	Formula
D2	D2	=IFERROR(ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$9999)/(($B$2:$B$9999<65)*($B$3:$B$10000<65)*($B$4:$B$10001<65)*($B$2:$B$9999<>"")),1)),0)+0

Hi Toadstool,

Thank you for your reply! Unfortunately this formula gives me a 1 all the time in my dataset, even if there is no value below 65 within the 15 cells. Does this formula look at the entire A column? I can't think another reason why it would always give 1 as result. How can I set it to check only within 15 cells (so within 5 minutes) in A column?

 

[Researchstudent]

Oops! Doesn't test for consecutive in the last one.

Offset to define your range in a helper column. Next to the values from A1 down:
=IF(COUNTIF(OFFSET(A1,-2,0,3),"<65")>=3,1,0)
also might replace "<65" with "<"&cellreference to make it updatable.
Then next to that column (C1?):
=IF(COUNTIF(OFFSET(B1,0,0,15),">0")>0,1,0)

Hello C moore,

Thank you for your reply. It almosts works but not exactly. This first formula tests only for the 3 consecutives from upper rows, but I also need to test it for the 3 consecutives from lower rows. Here is a screenshot from another part of my dataset. I put the first formula in D and the second in E. According to this formula, I get 0 in column D on row 77 and 78 even though the correct answer is 1 (because both rows are followed by 2 values that are lower than 65). It does work well for row 79 and 80 since it checks for the 3 consecutives from upper rows. If I can set it to check for both upper and lower 3 consecutive rows (for every value in column B) then I would have the right answer. Is there a way to do this?

 

[Toadstool]

Hi Toadstool,

Thank you for your reply! Unfortunately this formula gives me a 1 all the time in my dataset, even if there is no value below 65 within the 15 cells. Does this formula look at the entire A column? I can't think another reason why it would always give 1 as result. How can I set it to check only within 15 cells (so within 5 minutes) in A column?

I'd forgotten to check the last row isn't followed by empty cells (required for this to work as per your example).

This just checks rows 2 to 16 and requires rows 17 and 18 to be empty.

Researchstudent.xlsx
	A	B	C	D	E	F
1	Time	Value		Result		Starting at Row
2	15:54:04	77		0
3	15:54:24	74
4	15:54:44	71
5	15:55:04	68
6	15:55:24	66
7	15:55:44	65
8	15:56:04	44
9	15:56:24	71
10	15:56:44	66
11	15:57:04	55
12	15:57:24	71
13	15:57:44	66
14	15:58:04	44
15	15:58:24	66
16	15:58:44	44
Sheet2

Cell Formulas
Range	Formula
D2	D2	=IFERROR(ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$16)/(($B$2:$B$16<65)*($B$3:$B$17<65)*($B$4:$B$18<65)*($B$2:$B$16<>"")*($B$3:$B$117<>"")*($B$4:$B$18<>"")),1)),0)+0
F2	F2	=IFERROR(AGGREGATE(15,6,ROW($A$2:$A$16)/(($B$2:$B$16<65)*($B$3:$B$17<65)*($B$4:$B$18<65)*($B$2:$B$16<>"")*($B$3:$B$117<>"")*($B$4:$B$18<>"")),1),"")

 

[Researchstudent]

I'd forgotten to check the last row isn't followed by empty cells (required for this to work as per your example).

This just checks rows 2 to 16 and requires rows 17 and 18 to be empty.

Researchstudent.xlsx
	A	B	C	D	E	F
1	Time	Value		Result		Starting at Row
2	15:54:04	77		0
3	15:54:24	74
4	15:54:44	71
5	15:55:04	68
6	15:55:24	66
7	15:55:44	65
8	15:56:04	44
9	15:56:24	71
10	15:56:44	66
11	15:57:04	55
12	15:57:24	71
13	15:57:44	66
14	15:58:04	44
15	15:58:24	66
16	15:58:44	44
Sheet2

Cell Formulas
Range	Formula
D2	D2	=IFERROR(ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$16)/(($B$2:$B$16<65)*($B$3:$B$17<65)*($B$4:$B$18<65)*($B$2:$B$16<>"")*($B$3:$B$117<>"")*($B$4:$B$18<>"")),1)),0)+0
F2	F2	=IFERROR(AGGREGATE(15,6,ROW($A$2:$A$16)/(($B$2:$B$16<65)*($B$3:$B$17<65)*($B$4:$B$18<65)*($B$2:$B$16<>"")*($B$3:$B$117<>"")*($B$4:$B$18<>"")),1),"")

But my dataset consists of 10000 rows. With this formula I would have it to add 2 empty rows after every 15 cells which would take a long time. Do you think this is the only way?

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you give us a set of sample data and the expected results with XL2BB ?
Then explain again how you get those results manually in relation to that specific sample data.

 

[Toadstool]

But my dataset consists of 10000 rows. With this formula I would have it to add 2 empty rows after every 15 cells which would take a long time. Do you think this is the only way?

OK, this checks up to row 20003, requiring the two cells after the last entry in column B be empty. The single "There are at least 3 consecutive rows less than 65" indicator remains in D2 with column F giving the first row of such a set and column G giving the Time. I generated random numbers to get the test data down to row 10,000.

Researchstudent.xlsx
	A	B	C	D	E	F	G
1	Time	Value		Result		Starting at Row	Start Time
2	15:54:04	77		1		938	21:06:04
3	15:54:24	74				959	21:13:04
4	15:54:44	71				1154	22:18:04
5	15:55:04	68				1320	23:13:24
6	15:55:24	66				1613	0:51:04
7	15:55:44	65				1614	0:51:24
8	15:56:04	44				1801	1:53:44
9	15:56:24	71				3181	9:33:44
10	15:56:44	66				3425	10:55:04
11	15:57:04	55				3575	11:45:04
12	15:57:24	71				3576	11:45:24
13	15:57:44	66				4446	16:35:24
14	15:58:04	44				4811	18:37:04
15	15:58:24	66				7356	8:45:24
16	15:58:44	44				7457	9:19:04
17	15:59:04	111				7501	9:33:44
18	15:59:24	77				7676	10:32:04
19	15:59:44	66				9309	19:36:24
20	16:00:04	74
21	16:00:24	99
Sheet2

Cell Formulas
Range	Formula
D2	D2	=IFERROR(ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$20000)/(($B$2:$B$20000<65)*($B$3:$B$20001<65)*($B$4:$B$20002<65)*($B$2:$B$20000<>"")*($B$3:$B$20001<>"")*($B$4:$B$20002<>"")),1)),0)+0
F2:F21	F2	=IFERROR(AGGREGATE(15,6,ROW($A$2:$A$20000)/(($B$2:$B$20000<65)*($B$3:$B$20001<65)*($B$4:$B$20002<65)*($B$2:$B$20000<>"")*($B$3:$B$20001<>"")*($B$4:$B$20002<>"")),ROW()-ROW($F$1)),"")
G2:G21	G2	=IFERROR(INDEX(A:A,F2),"")