Once a cell reads 'X', Analyse a column and return a value

[DavidWT87]

Hello

I have some data - the temperature of a gas as time passes through various stages of a test. Each row is the data returns on a second by second basis. The outlet temperature needs to get below -33 within 30 seconds of the dispense stage reaching '60'.

So it should see me getting to stage 60 at Time=22, sees the temp get below -33 at Time= 46, and in a designated cell return the number of seconds taken (ie 46-22 = 24 seconds). I can then in another cell do a straightforward if function to return a pass/fail based on whether my answer is above/below 30 seconds.

Is there a way I can do this?

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	-4.513885
7	6	20	-4.181137
8	7	20	-7.754623
9	8	20	-10.46007
10	9	20	-14.40971
11	10	20	-15.76967
12	11	20	-17.69386
13	12	20	-18.33044
14	13	20	-19.18404
15	14	20	-19.63252
16	15	40	-19.8206
17	16	40	-19.8206
18	17	40	-19.8206
19	18	40	-20.47165
20	19	40	-22.61285
21	20	40	-23.78472
22	21	40	-24.33449
23	22	60	-24.52257
24	23	60	-24.52257
25	24	60	-24.52257
26	25	60	-24.52257
27	26	60	-24.37789
28	27	60	-24.20428
29	28	60	-24.03067
30	29	60	-23.85706
31	30	60	-23.61111
32	31	60	-23.37962
33	32	60	-22.989
34	33	60	-22.8588
35	34	60	-23.13368
36	35	60	-24.76852
37	36	60	-25.69444
38	37	60	-26.50462
39	38	60	-27.27142
40	39	60	-28.06712
41	40	60	-28.87732
42	41	60	-29.62962
43	42	60	-30.35301
44	43	60	-31.09086
45	44	60	-31.77084
46	45	60	-32.45081
47	46	60	-33.04398
48	47	60	-33.60822
49	48	60	-34.08564
50	49	60	-34.72222
51	50	60	-35.01158
Sheet4

 

[Fluff]

How about

=AGGREGATE(15,6,M2:M100/(O2:O100<=-33),1)-MATCH(60,N2:N100,0)

 

[DavidWT87]

How about

=AGGREGATE(15,6,M2:M100/(O2:O100<=-33),1)-MATCH(60,N2:N100,0)

Perfect! Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[DavidWT87]

No problem. One more question that is somewhat related?

Off the back of this, I also want to analyse the same temperature data and return the low value. So once Column N starts returning 60, I want to analyse Column O, see that it gets below -33, and once that happens check returns the lowest value it sees after that point whilst the criteria from column N & O are maintained.

So for the data I attached in the initial question, it sees N=60 & O<=-33 from row 47, then returns the low value from O47:O51, but hypothetically if N=60 & O<=-33 from row 37, but N then stopped being equal to 60 from row 47, it would check O37:O47 and return the low value from that range.

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks