• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help finding the MAX and MIN for selected groups.

M

mm3904

New Member
I need to find the MAX and MIN of the "last" column for the "groups" I created (see table below). The groups are rounded down to 10 minute intervals and then concatenated with the hour and minute. As such, the first group is 12:00am (00) and the third group is 12:14 (rounded down to 12:10am). The goal is to determine the high(max) and low(min) of the last column for each group (00, 010 and so on). That means, I need to create a dynamic function that determines the MAX and MIN for each group. My sheet has 37,627 cells so I would like to automate this. Any thoughts?

[pre]
Code: 
#RIC	Date[L]	        Time[L]	          Type	         Last	hour	Minute	Group
EUR=D2	2/1/2013	12:00:00 AM	Intraday 1Min	1.3610	0	0	00
EUR=D2	2/1/2013	12:04:00 AM	Intraday 1Min	1.3606	0	0	00
EUR=D2	2/1/2013	12:14:00 AM	Intraday 1Min	1.3602	0	10	010
EUR=D2	2/1/2013	1:02:00 AM	Intraday 1Min	1.3606	1	0	10
EUR=D2	2/1/2013	1:06:00 AM	Intraday 1Min	1.3605	1	0	10
EUR=D2	2/1/2013	1:15:00 AM	Intraday 1Min	1.3611	1	10	110
EUR=D2	2/1/2013	1:18:00 AM	Intraday 1Min	1.3614	1	10	110
EUR=D2	2/1/2013	1:19:00 AM	Intraday 1Min	1.3615	1	10	110
EUR=D2	2/1/2013	1:23:00 AM	Intraday 1Min	1.3622	1	20	120
EUR=D2	2/1/2013	1:36:00 AM	Intraday 1Min	1.3615	1	30	130
EUR=D2	2/1/2013	2:00:00 AM	Intraday 1Min	1.3611	2	0	20
EUR=D2	2/1/2013	3:03:00 AM	Intraday 1Min	1.3610	3	0	30
EUR=D2	2/1/2013	3:33:00 AM	Intraday 1Min	1.3607	3	30	330
EUR=D2	2/1/2013	3:37:00 AM	Intraday 1Min	1.3607	3	30	330
EUR=D2	2/1/2013	3:44:00 AM	Intraday 1Min	1.3608	3	40	340
[/pre]
 
While you've done an excellent job describing what you have, I'm not quite sure what the goal is. I can see that the Group column concatenates the hour and minute column (side note, the minute column uses a single 0..should it be 00? What are the different groups you want? We can certainly create psuedo MINIF and MAXIF functions, but we'll need to better understand what the criteria/group is.
 
Are you looking for something like:
Code: 
=MAX(INDEX(C$2:C$16,MATCH(J2,H$2:H$16),0))


Where the data from your example has been pasted from cell A1 and column J contains group names (i.e. 00, 10, 110 etc)
 
hello - thanks for the feedback. I have other data that I am going to match with the "group" criteria. The plan is to round down each value in this data set and the other data set to 10 minute intervals and match the high and low of each group (as you can see there are multiple last values for each group). For instance, rows two through four are all within 10 minutes of each other. As a result, I was to locate the MAX and MIN of the last column for that group. The next group is 10 (1:00 to 1:10am). I need to locate the max and min for that group as well. The next group is 110 (1:15 to 1:20) which I need to get the max and min of the three values in that group. The hard part is the groups are random so some samples may have one, three, five or any number of cells.


Thanks
 
The results would look like this: Then, I could match the high/low for each group with my other dataset.

[pre]
Code: 
Last	hour	Minute	Group	high	low
1.3610	0	0	00
1.3606	0	0	00
1.3602	0	10	010	1.3610	1.3602
1.3606	1	0	10
1.3605	1	0	10	1.3605	1.3606
1.3611	1	10	110
1.3614	1	10	110
1.3615	1	10	110
1.3622	1	20	120
1.3615	1	30	130
1.3611	2	0	20
1.3610	3	0	30
1.3607	3	30	330
1.3607	3	30	330
1.3608	3	40	340
[/pre]
 
I cleaned up the group column. Here is a clearer example: The * indicates a cell where I need to find the MAX and MIX from the "last" column that includes the range for each group. Again, the range sequence is random, which complicates the formula-writing process. I will then reference these max and min values against my other data set.

[pre]
Code: 
Date[L]	        Time[L]	          Type	         Last	hour	Minute	Group
2/1/2013	12:00:00 AM	Intraday 1Min	1.3610	00	0	00 00
2/1/2013	12:04:00 AM	Intraday 1Min	1.3606	00	0	00 00*
2/1/2013	12:14:00 AM	Intraday 1Min	1.3602	00	10	00 10*
2/1/2013	1:02:00 AM	Intraday 1Min	1.3606	01	0	01 00
2/1/2013	1:06:00 AM	Intraday 1Min	1.3605	01	0	01 00*
2/1/2013	1:15:00 AM	Intraday 1Min	1.3611	01	10	01 10
2/1/2013	1:18:00 AM	Intraday 1Min	1.3614	01	10	01 10
2/1/2013	1:19:00 AM	Intraday 1Min	1.3615	01	10	01 10*
2/1/2013	1:23:00 AM	Intraday 1Min	1.3622	01	20	01 20*
2/1/2013	1:36:00 AM	Intraday 1Min	1.3615	01	30	01 30*
2/1/2013	2:00:00 AM	Intraday 1Min	1.3611	02	0	02 00
2/1/2013	3:03:00 AM	Intraday 1Min	1.3610	03	0	03 00*
2/1/2013	3:33:00 AM	Intraday 1Min	1.3607	03	30	03 30
2/1/2013	3:37:00 AM	Intraday 1Min	1.3607	03	30	03 30*
2/1/2013	3:44:00 AM	Intraday 1Min	1.3608	03	40	03 40*
[/pre]
 
Well, a fast way would be to throw the data into a PivotTable, with Group in the Row Field and Last in the Data area twice (one for Min and one for Max). But, to do it via formula, and to have the MIN/MAX appear at last instance of that group:


MIN:

=IF(COUNTIF($H$2:$H2,$H2)<>COUNTIF($H:$H,$H2),"",MIN(IF($H$2:$H$20=$H2,$E$2:$E$20)))


MAX:

=IF(COUNTIF($H$2:$H2,$H2)<>COUNTIF($H:$H,$H2),"",MAX(IF($H$2:$H$20=$H2,$E$2:$E$20)))


Both of these are array formulas and should be confirmed by Ctrl+Shift+Enter, not just Enter.
 
Great, thanks! Much appreciated. Is there a way to adjust the formula to include all the rows? The sheet has 37,627 rows so how would this impact the cells referenced in the second part of each formula: MIN(IF($H$2:$H$20=$H2,$E$2:$E$20))and MAX(IF($H$2:$H$20=$H2,$E$2:$E$20)))? I also tried these formulas to no avail.
 
Change all the reference to 20 to 32627. If you're using XL2007+, you can just call out the entire column instead of something like H2:H32627.
 
Unfortunately, the formulas are not working properly. Are you suggesting to plug the first formula is cell I2, change the formulas to include all the rows and then drag the formula down the rest of the columns?
 
I second part of the formula appears to work but unfortunately the countif logic does not. Here is an example when I just use the countif logic with a sample data set: I need to generate a false to capture the MAX and MIN for each formula.


Time[L] Type Last hour Minute Group High

12:00:00 AM Intraday 1Min 1.3610 00 0 00 00 TRUE

12:04:00 AM Intraday 1Min 1.3606 00 0 00 00 TRUE

12:14:00 AM Intraday 1Min 1.3602 00 10 00 10 TRUE

1:02:00 AM Intraday 1Min 1.3606 01 0 01 00 TRUE

1:06:00 AM Intraday 1Min 1.3605 01 0 01 00 TRUE

1:15:00 AM Intraday 1Min 1.3611 01 10 01 10 TRUE

1:18:00 AM Intraday 1Min 1.3614 01 10 01 10 TRUE

1:19:00 AM Intraday 1Min 1.3615 01 10 01 10 TRUE

1:23:00 AM Intraday 1Min 1.3622 01 20 01 20 TRUE

1:36:00 AM Intraday 1Min 1.3615 01 30 01 30 TRUE

2:00:00 AM Intraday 1Min 1.3611 02 0 02 00 TRUE

3:03:00 AM Intraday 1Min 1.3610 03 0 03 00 TRUE

3:33:00 AM Intraday 1Min 1.3607 03 30 03 30 TRUE

3:37:00 AM Intraday 1Min 1.3607 03 30 03 30 TRUE

3:44:00 AM Intraday 1Min 1.3608 03 40 03 40 TRUE

3:48:00 AM Intraday 1Min 1.3609 03 40 03 40 TRUE

3:51:00 AM Intraday 1Min 1.3609 03 50 03 50 TRUE

3:52:00 AM Intraday 1Min 1.3609 03 50 03 50 TRUE

3:54:00 AM Intraday 1Min 1.3609 03 50 03 50 TRUE

3:58:00 AM Intraday 1Min 1.3610 03 50 03 50 TRUE

4:08:00 AM Intraday 1Min 1.3611 04 0 04 00 TRUE
 
Hi ,


I am not sure I have understood you correctly , but won't the following two formulae do the job ?


=IF(G2<>G3,MAX(OFFSET($D$2,MATCH(G2,$G$2:$G$16,0)-1,0,COUNTIF($G$2:$G$16,G2))),"")


=IF(G2<>G3,MIN(OFFSET($D$2,MATCH(G2,$G$2:$G$16,0)-1,0,COUNTIF($G$2:$G$16,G2))),"")


Both are array formulae , entered using CTRL SHIFT ENTER.


Narayan
 
This seems to work but it does not match the correct column. Instead of matching the values from the "group" column I would like to match the "last" column. That is column E. Is there anyway to update this formula to use the value from column E instead of the value in column H? I think the offset and match formulas need to be updated. Please advise and thanks again for the help!
 
mm3904,


Don't be afraid to modify the formulas we post. Since we can't see the exact workbooks/setup the posters are talking about, it's quite common to post a generic solution structure, and then the asker can adapt the formula to meet exact needs by changing row/column references.

Also, when you post a "formula didn't work after I put it in" type of response, it's helpful for you to post the formula you used *exactly* as you put it in. This lets us check to make sure it got copied/transferred correctly.


The formula I posted had the COUNTIF function using a absolute/relative range reference, so that the range would grow as you copied it down. However, if this got changed to a pure absolute or pure relative, it won't work.
 
I see, thanks. This is my first post so my apologies. Here are the formulas I mentioned:


=IF(G2<>G3,MAX(OFFSET($D$2,MATCH(G2,$G$2:$G$16,0)-1,0,COUNTIF($G$2:$G$16,G2))),"")

=IF(G2<>G3,MIN(OFFSET($D$2,MATCH(G2,$G$2:$G$16,0)-1,0,COUNTIF($G$2:$G$16,G2))),"")


I tried to use these two formulas and alter them to include column E (last) instead of column G (minute). These formulas are very close to the solution but each should reference the MAX and MIN of column E. I think the IF G3<>G4 is the right logic for the formula but I am unsure how to reference the right column.


I tried to alter the formula to reflect this but unfortunately it does not reference the correct cell. Here is the update I made:


=IF(G3<>G4,MAX(OFFSET($D$2,MATCH(E3,$E$2:$E$16,0)-1,0,COUNTIF($E$2:$E$16,E3))),"")

Here is what the cells (columns D-J) look like with the first two formulas:

[pre]
Code: 
Type	        Last	Hour	Minute 	Group	High 	Low
Intraday 1Min	1.361	00	0	00
Intraday 1Min	1.3606	00	0	00	0	0
Intraday 1Min	1.3602	00	10	010	0	0
Intraday 1Min	1.3606	01	0	10
Intraday 1Min	1.3605	01	0	10	0	0
Intraday 1Min	1.3611	01	10	110
Intraday 1Min	1.3614	01	10	110
Intraday 1Min	1.3615	01	10	110	0	0
Intraday 1Min	1.3622	01	20	120	0	0
Intraday 1Min	1.3615	01	30	130	0	0
[/pre]
 
Assumptions:

Group is in col H

Last is in col E

Data starts in row 2.

Data ends somewhere before row 40,000.

Data in col G is not necessarily sorted.


Array formulas:

MIN:

=IF(COUNTIF($H$2:$H2,$H2)<>COUNTIF($H:$H,$H2),"",MIN(IF($H$2:$H$40000=$H2,$E$2:$E$40000)))


MAX:

=IF(COUNTIF($H$2:$H2,$H2)<>COUNTIF($H:$H,$H2),"",MAX(IF($H$2:$H$40000=$H2,$E$2:$E$40000)))


If one of the above assumptions is incorrect, please correct.
 
You must log in or register to reply here.
Back
Top