multiple max and min and date using aggregate

[dmheller]

I have column A as date and time down to the min. and column B as a number from -2500 to 2500. I want to ignore any negative numbers but I want to capture a date and time on the Min and max as many times as it cycles. i tried =IFERROR(INDEX(N:N,AGGREGATE(4,6,ROW($N$5:$N$388624)/($O$5:$O$388624>0)/((O5:O388624="value")),ROWS($C$3:$C$3))),"")
I am putting this date into C and the max value i want in D and then another date in E and the Min in F.
I also need help finding that value if i can get that as well. I could be way off. snap shot of the numbers is like this. so what i am looking for is capturing 1/5/21 11:04 and 24 then you can see it goes positive again 11/5/21 11:15 and 1993. this will happen many times over the year so i am trying to capture them all. thanks for the help.

1/5/2021 11:01​	25​
1/5/2021 11:01​	25​
1/5/2021 11:01​	25​
1/5/2021 11:02​	25​
1/5/2021 11:02​	25​
1/5/2021 11:02​	25​
1/5/2021 11:03​	25​
1/5/2021 11:03​	25​
1/5/2021 11:03​	25​
1/5/2021 11:04​	25​
1/5/2021 11:04​	25​
1/5/2021 11:04​	24​
1/5/2021 11:05​	24​
1/5/2021 11:05​	-1035​
1/5/2021 11:05​	25​
1/5/2021 11:06​	25​
1/5/2021 11:06​	31​
1/5/2021 11:06​	29​
1/5/2021 11:07​	32​
1/5/2021 11:07​	27​
1/5/2021 11:07​	-1389​
1/5/2021 11:08​	-1395​
1/5/2021 11:08​	-1395​
1/5/2021 11:08​	-1395​
1/5/2021 11:09​	-1395​
1/5/2021 11:09​	-1395​
1/5/2021 11:09​	-1395​
1/5/2021 11:10​	-1395​
1/5/2021 11:10​	-1395​
1/5/2021 11:10​	-1395​
1/5/2021 11:11​	-1395​
1/5/2021 11:11​	-1395​
1/5/2021 11:11​	-1395​
1/5/2021 11:12​	-1395​
1/5/2021 11:12​	-1395​
1/5/2021 11:12​	-1395​
1/5/2021 11:13​	-1395​
1/5/2021 11:13​	-1395​
1/5/2021 11:13​	-1395​
1/5/2021 11:14​	-1395​
1/5/2021 11:14​	-1395​
1/5/2021 11:14​	-1395​
1/5/2021 11:15​	-1395​
1/5/2021 11:15​	-1394​
1/5/2021 11:15​	1993​
1/5/2021 11:16​	1988​
1/5/2021 11:16​	1989​
1/5/2021 11:16​	1985​
1/5/2021 11:17​	1963​
1/5/2021 11:17​	1977​
1/5/2021 11:17​	1944​
1/5/2021 11:18​	1931​

 

[dmheller]

You're not going to be able to clean up messy data with a single formula. Adding a helper column to the data source to categorise each row is the only realistic way of getting anywhere close to what you want. I will be very surprised if this works as needed with your full data set, but stranger things have happened.

This will grab the first value that is less then 30 (not necessarily the min), it will then ignore all values until it reaches the first value higher than 1990 (not necessarily the max), from this point it will ignore all values until it dips below 30 again, then repeating the cycle.

Book1 (version 1) (version 1).xlsb
	C	D	E	F								N	O	P
1	date	max	date	min
2	1/5/21 11:03	25	1/5/21 11:15	1993
3
4												date	Pounds	Bin
5												1/5/21 11:03	25	1
6												1/5/21 11:04	25
7												1/5/21 11:04	25
8												1/5/21 11:04	24
9												1/5/21 11:05	24
10												1/5/21 11:05	-1035
11												1/5/21 11:05	25
12												1/5/21 11:06	25
13												1/5/21 11:06	31
14												1/5/21 11:06	29
15												1/5/21 11:07	32
16												1/5/21 11:07	27
17												1/5/21 11:07	-1389
18												1/5/21 11:08	-1395
19												1/5/21 11:08	-1395
20												1/5/21 11:08	-1395
21												1/5/21 11:09	-1395
22												1/5/21 11:09	-1395
23												1/5/21 11:09	-1395
24												1/5/21 11:10	-1395
25												1/5/21 11:10	-1395
26												1/5/21 11:10	-1395
27												1/5/21 11:11	-1395
28												1/5/21 11:11	-1395
29												1/5/21 11:11	-1395
30												1/5/21 11:12	-1395
31												1/5/21 11:12	-1395
32												1/5/21 11:12	-1395
33												1/5/21 11:13	-1395
34												1/5/21 11:13	-1395
35												1/5/21 11:13	-1395
36												1/5/21 11:14	-1395
37												1/5/21 11:14	-1395
38												1/5/21 11:14	-1395
39												1/5/21 11:15	-1395
40												1/5/21 11:15	-1394
41												1/5/21 11:15	1993	3
42												1/5/21 11:16	1988
43												1/5/21 11:16	1989
44												1/5/21 11:16	1985
45												1/5/21 11:17	1963
Sheet6

Cell Formulas
Range	Formula
C2:D2	C2	=FILTER(N5:O45,P5:P45=1,"")
E2:F2	E2	=FILTER(N5:O45,P5:P45=3,"")
P5:P45	P5	=IF(O5>0,IF(MATCH(O5,{0,30,1990})<>IFERROR(LOOKUP(1E+100,P$4:P4),0),LOOKUP(O5,{0,30,1990},{1,"",3}),""),"")
Dynamic array formulas.

thanks, the helper column works well enought for what i need.