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​

 

[jasonb75]

Any chance of a bit of consistency in your question?

You say that your data is in 6 columns (A, B, C, D, E, and F), yet your sample only shows 2 columns (presumably A and B from the description) and your own formula makes no reference to the data in any of these columns.

If we are to have any hope of resolving this, I think that you will need to post a cleaner example (using XL2BB so that the date formats, etc. are not corrupted) showing approximately 20 rows of data that include all relevant columns and the expected results (based on the posted example only, do not base results on data that we can not see).

Also, please update your Account details to show which version of excel you are using so that we can base the solution on the functions that you have available.

 

[dmheller]

sorry for the confusion but only 2 cells are referenced. N and O, the c,d,e,f are where i am wanting the equations to go in.
equation in 3c isnt working but I want 3c to return 1/5/21 11:15 and then I want 3d to return 1993 (no equation in there) then i want 3e to return 1/5/21 11:05 and3f to retun 24 (no equations in them) this will then go throughout the year so i am looking for the date and time of the max and min each time a cycle happens. Hope this helps

1q hmi all (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			date	max	date	min
2		equation in 3c isnt working but I want 3c to return 1/5/21 11:15 and then I want 3d to return 1993
3
4														date	Pounds
5														1/5/2021 11:03	25
6														1/5/2021 11:04	25
7														1/5/2021 11:04	25
8														1/5/2021 11:04	24
9														1/5/2021 11:05	24
10														1/5/2021 11:05	-1035
11														1/5/2021 11:05	25
12														1/5/2021 11:06	25
13														1/5/2021 11:06	31
14														1/5/2021 11:06	29
15														1/5/2021 11:07	32
16														1/5/2021 11:07	27
17														1/5/2021 11:07	-1389
18														1/5/2021 11:08	-1395
19														1/5/2021 11:08	-1395
20														1/5/2021 11:08	-1395
21														1/5/2021 11:09	-1395
22														1/5/2021 11:09	-1395
23														1/5/2021 11:09	-1395
24														1/5/2021 11:10	-1395
25														1/5/2021 11:10	-1395
26														1/5/2021 11:10	-1395
27														1/5/2021 11:11	-1395
28														1/5/2021 11:11	-1395
29														1/5/2021 11:11	-1395
30														1/5/2021 11:12	-1395
31														1/5/2021 11:12	-1395
32														1/5/2021 11:12	-1395
33														1/5/2021 11:13	-1395
34														1/5/2021 11:13	-1395
35														1/5/2021 11:13	-1395
36														1/5/2021 11:14	-1395
37														1/5/2021 11:14	-1395
38														1/5/2021 11:14	-1395
39														1/5/2021 11:15	-1395
40														1/5/2021 11:15	-1394
41														1/5/2021 11:15	1993
42														1/5/2021 11:16	1988
43														1/5/2021 11:16	1989
44														1/5/2021 11:16	1985
45														1/5/2021 11:17	1963
Sheet2

Cell Formulas
Range	Formula
C3	C3	=IFERROR(INDEX(N:N,AGGREGATE(4,6,ROW($N$5:$N$388595)/($O$5:$O$388595>0)/((O5:O388595="value")),ROWS($C$3:$C$3))),"")

 

[jasonb75]

this will then go throughout the year so i am looking for the date and time of the max and min each time a cycle happens.

So how do we identify date and cycle? Is it as simple as resetting the clock at midnight?

 

[dmheller]

Nothing is set, that is pounds in a tank and when it is used up, it gets refilled. That is why i am trying to write an equation to find when it is full them empty.

 

[jasonb75]

There needs to be something set in order to identify what you class as a cycle or there will be no boundaries for the formula to work with.

You have a lot of fluctuations in the data. Logically, these are the results that I would expect from your example (excluding any negative figures).

date	max	date	min
1/5/21 11:03	25	1/5/21 11:05	24
1/5/21 11:05	25	1/5/21 11:06	25
1/5/21 11:06	31	1/5/21 11:06	29
1/5/21 11:07	32	1/5/21 11:07	27
1/5/21 11:15	1993	1/5/21 11:16	1963

Is that what you would expect?

 

[dmheller]

i would like to get rid of the noise and just capture 1 set of data
I was hoping for something like this I ended with a min and thats what i would expect to end with for usage and it would be in the future or were the data ends like you have it here.
I would think if you knew how to get it said the max was greater than 1990 and capture 1 data point and the min was less than 30 and capture 1 data point that would be close enough.

date	max	date	min
1/5/21 11:15	1993	1/5/21 11:05	24
		1/5/21 11:16	1963

 

[jasonb75]

If that's all you need then this should do it. The way that you had used 'cycle' in your description implied that you wanted a summary list of each time the tank was filled and emptied.

Book1 (version 1).xlsx
	C	D	E	F
1	date	max	date	min
2	1/5/21 11:15	1993	1/5/21 11:04	24
3			1/6/21 11:17	1963
Sheet6

Cell Formulas
Range	Formula
C2	C2	=MINIFS(N:N,O:O,D2)
D2	D2	=MAX(O:O)
E2	E2	=MINIFS(N:N,O:O,F2)
F2	F2	=MINIFS(O:O,O:O,">0")
E3	E3	=MAXIFS(N:N,O:O,">0")
F3	F3	=MINIFS(O:O,O:O,">0",N:N,E3)

 

[dmheller]

It does cycle I just sent a small snapshot of the data. I am looking to capture it every time it happens

 

[jasonb75]

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.