Analysing Data based on Input Conditions being met

[DavidWT87]

Hello. If someone can help, I would like to analyse some temperature data and return the low value, once certain conditions are met. The scenario is once my test is at a certain stage (60), the outlet temperature needs to maintain a certain range once it hits a threshold. So I would like to be checking for Column N becoming 60, and then start analysing Column O once it gets below -33, and once that happens, return the lowest value it sees whilst N=60, in one cell, and the highest value it sees in another cell.

So for the attached data, it sees N=60 & O<=-33 from row 29, then checks O29:O51 (stage stops being 60 in row 52), and returns the low value that it finds in a cell.

Once O<=-33, it doesn't matter whether it becomes higher again.

Thanks

Copy of Test 36A 30.03.23 PASS.xlsm
	M	N	O
1	Time	Dispense Stage	Outlet Temperature
2	1	0	0
3	2	20	-4.600693
4	3	20	-4.528351
5	4	20	-4.528351
6	5	20	-15.76967
7	6	20	-17.69386
8	7	20	-18.33044
9	8	20	-19.18404
10	9	20	-19.63252
11	10	40	-19.8206
12	11	40	-19.8206
13	12	40	-19.8206
14	13	40	-20.47165
15	14	40	-22.61285
16	15	40	-23.78472
17	16	40	-24.33449
18	17	60	-24.52257
19	18	60	-24.52257
20	19	60	-24.52257
21	20	60	-24.03067
22	21	60	-23.85706
23	22	60	-23.61111
24	23	60	-23.37962
25	24	60	-26.50462
26	25	60	-27.27142
27	26	60	-28.06712
28	27	60	-29.62962
29	28	60	-33.01
30	29	60	-33.04398
31	30	60	-33.60822
32	31	60	-34.08564
33	32	60	-34.72222
34	33	60	-35.01158
35	34	60	-35.30092
36	35	60	-35.47454
37	36	60	-35.63368
38	37	60	-34.8235
39	38	60	-36.03876
40	39	60	-36.06771
41	40	60	-36.12558
42	41	60	-36.14005
43	42	60	-36.16898
44	43	60	-36.22686
45	44	60	-36.24132
46	45	60	-36.29919
47	46	60	-36.31365
48	47	60	-36.31365
49	48	60	-36.386
50	49	60	-36.386
51	50	60	-36.40046
52	51	70	-36.386
53	52	70	-36.22686
54	53	70	-36.08217
55	54	70	-35.9809
Sheet4

 

[DavidWT87]

Ok, how about

=AGGREGATE(14,6,INDEX(O:O,AGGREGATE(15,6,M2:M100/(O2:O100<=-33),1)+1):O100/(INDEX(N:N,AGGREGATE(15,6,M2:M100/(O2:O100<=-33),1)+1):N100=60),1)

And we're there.

Thank you so much. You've been extraordinarily patient in helping me. It is hugely appreciated

 

[Fluff]

You're welcome & thanks for the feedback.