Counting Number Of Occurrences One Threshold Met Over Multiple Lines

[PS_Richard]

Good morning all,

As always in advance, thanks to this wonderful site and all the extremely helpful users. Now on to the issue that I have today. I'm looking at the voltages on a device where I need to count the occurrence of times it enters idle. This is done when a state is recorded as the same voltage a minimum of 3 rows the same. Below is a hefty example of 74 lines of data but I wanted to give a clear picture of what I'm after.

What you can see here is data within columns A to G with headers on the top row. I've highlighted the sections when it needs to flag that an occurrence has happened and we need to view this on column A as being the voltage column's trigger.

BA - Idle State.xlsx
	A	B	C	D	E	F	G
1	voltage	localTemp	remoteTemp	electrolyteStatus	dischargeCurrent	chargeCurrent	dateTime
2	86.3	39.1	39.5	2	0	0	12/09/2022 15:43
3	86.3	39.1	39.4	2	0	0	12/09/2022 15:44
4	85.2	38.8	39.3	2	34.6	0	12/09/2022 15:45
5	86.1	38.8	39.3	2	8.6	0	12/09/2022 15:46
6	86.2	38.8	39.3	2	9	0	12/09/2022 15:47
7	85.1	38.7	39.3	2	35.1	0	12/09/2022 15:48
8	87.5	38.5	39.3	2	7.3	139.2	12/09/2022 15:49
9	85.8	38.2	38.7	2	1.2	96.1	12/09/2022 15:54
10	85.6	38.1	38.7	2	0	0	12/09/2022 15:55
11	85.8	38.1	38.7	2	0	0	12/09/2022 15:56
12	85.8	38	38.7	2	0	0	12/09/2022 15:57
13	85.8	37.8	38.7	2	0	0	12/09/2022 16:01
14	85.8	37.8	38.7	2	0	0	12/09/2022 16:02
15	85.8	37.8	38.7	2	0	0	12/09/2022 16:03
16	85.8	37.8	38.7	2	0	0	12/09/2022 16:04
17	85.9	37.8	38.7	2	0	0	12/09/2022 16:05
18	85.8	37.8	38.7	2	0	0	12/09/2022 16:06
19	85.8	37.8	38.7	2	0	0	12/09/2022 16:07
20	85.8	37.6	38.7	2	0	0	12/09/2022 16:08
21	85.9	37.6	38.7	2	0	0	12/09/2022 16:09
22	85.8	37.5	38.7	2	0	0	12/09/2022 16:10
23	85.8	37.5	38.7	2	0	0	12/09/2022 16:11
24	85.8	37.5	38.7	2	0	0	12/09/2022 16:12
25	85.8	37.5	38.7	2	0	0	12/09/2022 16:13
26	81.7	37.5	38.7	2	152.5	15.5	12/09/2022 16:14
27	90	37.5	38.7	2	0	318.5	12/09/2022 16:15
28	85.3	37.5	38.7	2	9.3	0	12/09/2022 16:16
29	85.5	37.5	38.7	2	8.8	0	12/09/2022 16:17
30	85.5	37.8	38.7	2	8.6	0	12/09/2022 16:18
31	85.5	37.7	38.7	2	8.6	0	12/09/2022 16:19
32	85.5	37.8	38.7	2	8.8	0	12/09/2022 16:20
33	85.5	37.8	38.7	2	9.2	0	12/09/2022 16:21
34	85.6	37.8	38.7	2	8.2	0	12/09/2022 16:22
35	85.5	37.8	38.6	2	8.6	0	14/09/2022 16:23
36	85.5	37.8	38.5	2	8.4	0	14/09/2022 16:24
37	85.5	37.8	38.4	2	8.6	0	14/09/2022 16:25
38	85.5	37.8	38.4	2	8.2	0	14/09/2022 16:26
39	85.5	37.8	38.4	2	8.6	0	14/09/2022 16:27
40	85.5	37.8	38.4	2	8.4	0	14/09/2022 16:28
41	85.5	37.8	38.3	2	8.2	0	14/09/2022 16:29
42	85.5	37.8	38.3	2	8.8	0	14/09/2022 16:30
43	85.5	37.8	38.1	2	8.2	0	14/09/2022 16:31
44	80.7	37.8	38	2	218	0	14/09/2022 16:32
45	85.5	37.2	38.2	2	0	0	14/09/2022 16:39
46	85.5	37.2	38.4	2	0	0	14/09/2022 16:40
47	85.5	37.2	38.4	2	0	0	14/09/2022 16:41
48	84.5	37.3	38.4	2	32	0	14/09/2022 16:44
49	84.4	37.2	38.4	2	28.6	0	14/09/2022 16:45
50	84	37.2	38.4	2	41.1	0	14/09/2022 16:46
51	85.4	37.2	38	2	0	0	14/09/2022 17:04
52	85.3	37.2	38	2	0	0	14/09/2022 17:05
53	85.3	37.2	37.8	2	0	0	14/09/2022 17:06
54	85.4	37.2	37.7	2	0	0	14/09/2022 17:07
55	85.3	37.2	37.7	2	0	0	18/09/2022 17:08
56	85.3	37.1	37.6	2	0	0	18/09/2022 17:09
57	85.3	37	37.5	2	0	0	18/09/2022 17:10
58	85.3	36.9	37.4	2	0	0	18/09/2022 17:11
59	85.3	36.9	37.4	2	0	0	18/09/2022 17:12
60	85.3	36.9	37.4	2	0	0	18/09/2022 17:13
61	85.3	36.9	37.4	2	0	0	18/09/2022 17:14
62	85.3	36.9	37.4	2	0	0	18/09/2022 17:15
63	85.3	36.9	37.2	2	0	0	18/09/2022 17:16
64	85.3	36.9	37.1	2	0	0	18/09/2022 17:17
65	85.3	36.9	37.1	2	0	0	18/09/2022 17:18
66	85.3	36.9	37.1	2	0	0	18/09/2022 17:19
67	85.3	36.9	37.1	2	0	0	18/09/2022 17:20
68	85.3	36.9	37.1	2	0	0	18/09/2022 17:21
69	85.3	36.8	37	2	0	0	18/09/2022 17:22
70	85.3	36.7	37	2	0	0	18/09/2022 17:23
71	85.3	36.9	37	2	0	0	18/09/2022 17:24
72	85.3	36.8	36.9	2	0	0	18/09/2022 17:25
73	85.3	36.6	36.9	2	0	0	18/09/2022 17:26
74	85.3	36.5	36.8	2	0	0	18/09/2022 17:27
Sheet1

On rows 13 - 16, the voltage is 85.8 for 4 stages so this is occurrence #1. Row #17 is just one entry so can be ignored. Then back to being checked on any additional rows.
Next are rows 18 to 20, 22 to 25, 29 to 33, 35 to 45 to 47 and then 55 to 74.

So even though the values can appear across the range it needs to be only noted once itstheyssed 3 entries. So looking at the above rows it would be a total occurrence 7. I'd then be then looking to show the results into something as follows (this is a manual edit with no formulas as that is the help I'm looking for).

BA - Idle State.xlsx
	J	K	L	M
1	Occurance	Number Of Entries	Date/Time of First Entry	Date/Time of Last Entry
2	1	4	12/09/2022 16:01	12/09/2022 16:04
3	2	3	12/09/2022 16:06	12/09/2022 16:08
4	3	4	12/09/2022 16:10	12/09/2022 16:13
5	4	5	12/09/2022 16:17	12/09/2022 16:21
6	5	9	14/09/2022 16:23	14/09/2022 16:31
7	6	3	14/09/2022 16:39	14/09/2022 16:41
8	7	20	18/09/2022 17:08	18/09/2022 17:27
Sheet1

Hopefully this all makes sense on what I'm looking to achieve but if you need any further clarification then please let me know.

Thanks, PS_Richard

 

[PS_Richard]

If it helps, I've made a helper column in the interim to see when the occurrence happens. So in cell H1, I have this: =1
Then in cell H2 copied down I have this: =IF(A2=A1,H1+1,1)

This is then doing a count and then resetting to 1 if the previous figure is not the same. So I'm getting to see where these instances are happening but it's then pulling that information across into the chart example I've given in the second part of my original post.

 

[PS_Richard]

I've updated my sheet and hopefully, by showing the below it'll help someone to help me.

2023-01-27 - BA eGO! Idle.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Occurance ID	Sequence Start	Consecutive Checker	Number of Entries Checker		voltage	localTemp	remoteTemp	electrolyteStatus	dischargeCurrent	chargeCurrent	dateTime			Occurance	Number of Entries	Voltage	Day / Time of First Entry	Day / Time of Last Entry	Duration
2			1			86.8	41.3	40.3	2	9.7	0	12/09/2022 15:26			1		85.8	12/09/2022 16:01
3			1			86.9	41.1	40.3	2	10.1	0	12/09/2022 15:27			2		85.8	12/09/2022 16:06
4			2			86.9	40.7	40.1	2	9.3	0	12/09/2022 15:28			3		85.5	12/09/2022 23:59
5	1	1	1			85.8	37.8	38.7	2	0	0	12/09/2022 16:01			4
6			2			85.8	37.8	38.7	2	0	0	12/09/2022 16:02			5
7			3			85.8	37.8	38.7	2	0	0	12/09/2022 16:03			6
8			4	4		85.8	37.8	38.7	2	0	0	12/09/2022 16:04			7
9			1			85.9	37.8	38.7	2	0	0	12/09/2022 16:05			8
10	2	1	1			85.8	37.8	38.7	2	0	0	12/09/2022 16:06			9
11			2			85.8	37.8	38.7	2	0	0	12/09/2022 16:07			10
12			3	3		85.8	37.6	38.7	2	0	0	12/09/2022 16:08
13			1			81.7	37.5	38.7	2	152.5	15.5	12/09/2022 16:14
14			1			90	37.5	38.7	2	0	318.5	12/09/2022 16:15			Occurance	Number of Entries	Voltage	Day / Time of First Entry	Day / Time of Last Entry	Duration
15			1			85.3	37.5	38.7	2	9.3	0	12/09/2022 16:16			1		85.8	12/09/2022 16:01	12/09/2022 16:04	00:03:00
16	3	1	1			85.5	37.5	38.7	2	8.8	0	12/09/2022 23:59			2		85.8	12/09/2022 16:06	12/09/2022 16:08	00:02:00
17			2			85.5	37.8	38.7	2	8.6	0	13/09/2022 00:00			3		85.5	12/09/2022 23:59	13/09/2022 00:03	00:04:00
18			3			85.5	37.7	38.7	2	8.6	0	13/09/2022 00:01
19			4			85.5	37.8	38.7	2	8.8	0	13/09/2022 00:02
20			5	5		85.5	37.8	38.7	2	9.2	0	13/09/2022 00:03
21			1			85.4	37.2	38	2	0	0	13/09/2022 00:04
Example Data

Cell Formulas
Range	Formula
A2:A21	A2	=IF(B2=1,COUNTIF(B$2:B2,1),"")
B2:B21	B2	=IF(C4=3,1,"")
C2	C2	=1
D2:D21	D2	=IF(AND(C2>=3,C3=1),C2,"")
C3:C21	C3	=IF(F3=F2,C2+1,1)
Q2:Q11	Q2	=IFERROR(VLOOKUP(O2,$A:$L,6,0),"")
R2:R11,R17	R2	=IFERROR(VLOOKUP(O2,$A:$L,12,0),"")
T15:T17	T15	=S15-R15

So from F1 to L21 is my raw data. I've added 'helper' columns in A1 to D21 and you can see the formulas I've used there to see when an occurrence happens and when the sequence starts and when it ends.

In O1 to T11 is what I currently have in my lookups to get the data from the raw. What I'm struggling with is filling in the following columns, 'Number of Entries' and 'Day / Time of Last Entry' as when I get that field in I can work out the duration.

Hope this better shows / explains what I'm after help with? Th.nks...