multiple max and min and date using aggregate

D

dmheller

Board Regular
Joined
May 26, 2017
Messages
159
Office Version
  1. 365
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​
 
Sort by date Sort by votes
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.
 
Upvote 0
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
ABCDEFGHIJKLMNO
1datemaxdatemin
2equation in 3c isnt working but I want 3c to return 1/5/21 11:15 and then I want 3d to return 1993
3 
4datePounds
51/5/2021 11:0325
61/5/2021 11:0425
71/5/2021 11:0425
81/5/2021 11:0424
91/5/2021 11:0524
101/5/2021 11:05-1035
111/5/2021 11:0525
121/5/2021 11:0625
131/5/2021 11:0631
141/5/2021 11:0629
151/5/2021 11:0732
161/5/2021 11:0727
171/5/2021 11:07-1389
181/5/2021 11:08-1395
191/5/2021 11:08-1395
201/5/2021 11:08-1395
211/5/2021 11:09-1395
221/5/2021 11:09-1395
231/5/2021 11:09-1395
241/5/2021 11:10-1395
251/5/2021 11:10-1395
261/5/2021 11:10-1395
271/5/2021 11:11-1395
281/5/2021 11:11-1395
291/5/2021 11:11-1395
301/5/2021 11:12-1395
311/5/2021 11:12-1395
321/5/2021 11:12-1395
331/5/2021 11:13-1395
341/5/2021 11:13-1395
351/5/2021 11:13-1395
361/5/2021 11:14-1395
371/5/2021 11:14-1395
381/5/2021 11:14-1395
391/5/2021 11:15-1395
401/5/2021 11:15-1394
411/5/2021 11:151993
421/5/2021 11:161988
431/5/2021 11:161989
441/5/2021 11:161985
451/5/2021 11:171963
Sheet2
Cell Formulas
RangeFormula
C3C3=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))),"")
 
Upvote 0
dmheller said:
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.
Click to expand...
So how do we identify date and cycle? Is it as simple as resetting the clock at midnight?
 
Upvote 0
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.
 
Upvote 0
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).
datemaxdatemin
1/5/21 11:03251/5/21 11:0524
1/5/21 11:05251/5/21 11:0625
1/5/21 11:06311/5/21 11:0629
1/5/21 11:07321/5/21 11:0727
1/5/21 11:1519931/5/21 11:161963


Is that what you would expect?
 
Upvote 0
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.
datemaxdatemin
1/5/21 11:1519931/5/21 11:0524
1/5/21 11:161963
 
Upvote 0
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
CDEF
1datemaxdatemin
21/5/21 11:1519931/5/21 11:0424
31/6/21 11:171963
Sheet6
Cell Formulas
RangeFormula
C2C2=MINIFS(N:N,O:O,D2)
D2D2=MAX(O:O)
E2E2=MINIFS(N:N,O:O,F2)
F2F2=MINIFS(O:O,O:O,">0")
E3E3=MAXIFS(N:N,O:O,">0")
F3F3=MINIFS(O:O,O:O,">0",N:N,E3)
 
Upvote 0
It does cycle I just sent a small snapshot of the data. I am looking to capture it every time it happens
 
Upvote 0
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
CDEFNOP
1datemaxdatemin
21/5/21 11:03251/5/21 11:151993
3
4datePoundsBin
51/5/21 11:03251
61/5/21 11:0425 
71/5/21 11:0425 
81/5/21 11:0424 
91/5/21 11:0524 
101/5/21 11:05-1035 
111/5/21 11:0525 
121/5/21 11:0625 
131/5/21 11:0631 
141/5/21 11:0629 
151/5/21 11:0732 
161/5/21 11:0727 
171/5/21 11:07-1389 
181/5/21 11:08-1395 
191/5/21 11:08-1395 
201/5/21 11:08-1395 
211/5/21 11:09-1395 
221/5/21 11:09-1395 
231/5/21 11:09-1395 
241/5/21 11:10-1395 
251/5/21 11:10-1395 
261/5/21 11:10-1395 
271/5/21 11:11-1395 
281/5/21 11:11-1395 
291/5/21 11:11-1395 
301/5/21 11:12-1395 
311/5/21 11:12-1395 
321/5/21 11:12-1395 
331/5/21 11:13-1395 
341/5/21 11:13-1395 
351/5/21 11:13-1395 
361/5/21 11:14-1395 
371/5/21 11:14-1395 
381/5/21 11:14-1395 
391/5/21 11:15-1395 
401/5/21 11:15-1394 
411/5/21 11:1519933
421/5/21 11:161988 
431/5/21 11:161989 
441/5/21 11:161985 
451/5/21 11:171963 
Sheet6
Cell Formulas
RangeFormula
C2:D2C2=FILTER(N5:O45,P5:P45=1,"")
E2:F2E2=FILTER(N5:O45,P5:P45=3,"")
P5:P45P5=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.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

BradleyS
  • Solved
Convert Age to Number format not working
Replies
7
Views
223
BradleyS
BradleyS
L
  • Solved
Help Please
2 3
Replies
21
Views
700
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
356
Fluff
Fluff
A
  • Locked
  • Question Question
Multiply an array based on date value entered.
Replies
1
Views
126
Fluff
Fluff
L
  • Solved
EXCEL FORMULA TO COUNT
Replies
6
Views
332
LAY2022
L

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top