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

 

[Fluff]

How about

=AGGREGATE(15,6,O2:O100/(N2:N100=60),1)

 

[DavidWT87]

How about

=AGGREGATE(15,6,O2:O100/(N2:N100=60),1)

Thank you so much. That is way more simple than I anticipated it would be!

Do you have a solution for the other half of my question? (I think I did a bad job of editing my post to include it so may not have been obvious!)

In another cell, I want to return the highest temperature Column O sees after it first sees O<-33 and whilst N=60.

So in the presented data, row 29 is where O first meets the criteria, and this is also the highest temperature. But if in a subsequent row, the temp value was -31.2, that would be the value returned instead.

Thanks

 

[Fluff]

How about

=AGGREGATE(14,6,O2:O100/(O2:O100<=-33),1)

 

[DavidWT87]

How about

=AGGREGATE(14,6,O2:O100/(O2:O100<=-33),1)

Unfortunately it doesn't seem like this (or the first answer as it turns out) are quite doing what I was hoping for. On the embedded sheet, I have overwritten a couple of values highlighted in grey.

The formula in Q5 is picking up the -38 and returning it as the lowest temperature which it shouldn't, because Column O hasn't yet reached -33. It is only once -33 has been reached that I would like to search for the lowest value that also has N=60 as a condition.

Similarly, I have overwritten -23 in O34, but the formula in Q9 is not picking up this as the highest temperature, which I would like it to, because it satisfies my initial condition that N=60, and it is after the first point Column O drops below -33.

Auotmated Testing Analysis.xlsm
	M	N	O	P	Q
1	Time	Dispense Stage	Outlet Temperature
2	1	0	0
3	2	20	-4.600693
4	3	20	-4.528351		LOW VALUE
5	4	20	-4.528351		-38.00
6	5	20	-15.76967
7	6	20	-17.69386
8	7	20	-18.33044		HIGH VALUE
9	8	20	-19.18404		-33.01
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	-38
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	-23
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

Cell Formulas
Range	Formula
Q5	Q5	=AGGREGATE(15,6,O2:O100/(N2:N100=60),1)
Q9	Q9	=AGGREGATE(14,6,O2:O100/(O2:O100<=-33),1)

 

[Fluff]

Can you have odd values like that in your data?

 

[DavidWT87]

Can you have odd values like that in your data?

Not quite as odd as I have presented here, (and upon further consideration, ignore the random -38. The formula in Q5 will actually do what I need), but it is possible for the principal to happen around the Q9 formula.

It is equipment that is monitoring temperature over time through various stages of a test. Once stage 60 of the test starts, the temperature has got 30 seconds to get within a target range (this is the other thread for you replied on), and once it gets within that target range, it has to stay in that range for the duration of the stage 60 part of the test.

But if for some reason there is an error, and after it gets into the target range (ie lower than -33), the temperature could theoretically rise again above -33 and I'd like to log what that highest temp is so the test can be tagged as a fail. And that's why I threw the random -23 in there. In reality it would be a smooth progression, but I just wanted to test the formula hence the random value.

 

[Fluff]

Ok, how about

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

 

[DavidWT87]

Ok, how about

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

I think it's so nearly there. With the random -23 in there, it is now picking that up, which is good.

But if I use the formula on the original data I posted on this thread, it returns the cell above as the answer.

A little play about testing it, it looks as if it is including the cell above the first one that is <=-33 within the dataset it is searching through for the highest number.

ie: with the dataset unedited, it returns the highest value as being -29.62962

if I edit that cell (O28) to be -33, then my answer jumps to contents of O27

if I then edit cell O22 to be -33, it returns -23.37962 as the answer (presumably because the cell above (O21) is now not the highest value in the range.

 

[Fluff]

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)