Need MIN/MAX from Range That Varies Daily

[madkinson]

I generate a sheet every day that has 4 activities and associated data. I need to capture the MIN and MAX as part of the requirement. that is easy manually but how can you automate this every day to account for a different Activity range? In my example sheet, Activity 12 is rows 22 thru 25. Tomorrow, it may be rows 25 thru 40. I tried to do an INDEX/MATCH but it too wasn't flexible enough to deal with different ranges. Am I making this too hard?

Book3
	A	B	C	D	E	F
1	Doc #	Date	Act #	Qty	MIN	MAX
2	1092043890	5/23/2022	1	14.37
3	1092043633	5/23/2022	1	25.67	(3,000)
4	1092043641	5/23/2022	1	26.56		820
5	1092043643	5/23/2022	1	24.60	(1,500)
6	1092043646	5/23/2022	1	23.70	(5,400)
7	1092043662	5/23/2022	2	21.16	(2,400)
8	1092043676	5/23/2022	2	22.54
9	1092043753	5/23/2022	2	25.14	(520)
10	1092043762	5/23/2022	2	22.30	(1,160)
11	1092043765	5/23/2022	2	18.75
12	1092043767	5/23/2022	2	16.92		2,520
13	1092043828	5/23/2022	11	24.29	(500)
14	1092043843	5/23/2022	11	25.86
15	1092043847	5/23/2022	11	24.20
16	1092043854	5/23/2022	11	23.85
17	1092043864	5/23/2022	11	22.88	(500)
18	1092043881	5/23/2022	11	24.44
19	1092043886	5/23/2022	11	23.60
20	1092043887	5/23/2022	11	24.65
21	1092043898	5/23/2022	11	23.50	(500)
22	1092043609	5/23/2022	12	22.49		1,250
23	1092043612	5/23/2022	12	27.71
24	1092043617	5/23/2022	12	25.25		460
25	1092043621	5/23/2022	12	24.33
Sheet1

 

[Marcelo Branco]

In this solution how is column H populated?

One way
You can copy all the values in column C and paste in column H, then use Data > Remove duplicates to get unique values

M.

 

[Peter_SSs]

Try these formulas in row 2 (other results will automatically 'spill' to the required rows).

madkinson.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Doc #	Date	Act #	Qty	MIN	MAX		Activity	MIN	MAX
2	1092043890	23/05/2022	1	14.37				1	(5,400.00)	820.00
3	1092043633	23/05/2022	1	25.67	(3000.00)			2	(2,400.00)	2,520.00
4	1092043641	23/05/2022	1	26.56		820		11	(500.00)
5	1092043643	23/05/2022	1	24.6	(1500.00)			12		1,250.00
6	1092043646	23/05/2022	1	23.7	(5400.00)
7	1092043662	23/05/2022	2	21.16	(2400.00)
8	1092043676	23/05/2022	2	22.54
9	1092043753	23/05/2022	2	25.14	(520.00)
10	1092043762	23/05/2022	2	22.3	(1160.00)
11	1092043765	23/05/2022	2	18.75
12	1092043767	23/05/2022	2	16.92		2520
13	1092043828	23/05/2022	11	24.29	(500.00)
14	1092043843	23/05/2022	11	25.86
15	1092043847	23/05/2022	11	24.2
16	1092043854	23/05/2022	11	23.85
17	1092043864	23/05/2022	11	22.88	(500.00)
18	1092043881	23/05/2022	11	24.44
19	1092043886	23/05/2022	11	23.6
20	1092043887	23/05/2022	11	24.65
21	1092043898	23/05/2022	11	23.5	(500.00)
22	1092043609	23/05/2022	12	22.49		1250
23	1092043612	23/05/2022	12	27.71
24	1092043617	23/05/2022	12	25.25		460
25	1092043621	23/05/2022	12	24.33
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=UNIQUE(C2:C25)
I2:I5	I2	=IF(COUNTIFS(E2:E25,"<0",C2:C25,H2#),MINIFS(E2:E25,C2:C25,H2#),"")
J2:J5	J2	=IF(COUNTIFS(F2:F25,">0",C2:C25,H2#),MAXIFS(F2:F25,C2:C25,H2#),"")
Dynamic array formulas.

 

[madkinson]

Try
Using your data sample above

Pasta1
	H	I	J
1	Activity	MIN	MAX
2	1	(5.400)	820
3	2	(2.400)	2520
4	11	(500)
5	12		1250
Plan6

Cell Formulas
Range	Formula
I2:I5	I2	=IFERROR(AGGREGATE(15,6,E$2:E$300/((C$2:C$300=H2)*ISNUMBER(E$2:E$300)),1),"")
J2:J5	J2	=IFERROR(AGGREGATE(14,6,F$2:F$300/((C$2:C$300=H2)*ISNUMBER(F$2:F$300)),1),"")

M.

Sorry to be so tardy in responding (other work demands). Marcelo, I do not follow what you are doing here. IN teh second line, the AGGREGATE is looing for the MAX (LARGE (14)). The second value (6) is saying ignore errors. Then you define the range (F$2:F$300). After that I get lost. You are dividing the first expression by a value (where the value in the C$ range equals the Activity and taking the largest value?) but what does the rest of the string mean?

 

[Marcelo Branco]

Marcelo, I do not follow what you are doing here.

To consider only the values according to the criteria, the formula divides the values in F2:F300 by the conditions, that is:
Values in C2:C300 must be equal to the value in H2 (criterion1)
AND
Must be numbers (criterion2). I added this second criterion so that the formula does not return 0 in cases where there are only blank cells in column F in the rows that meet criterion 1 - in these cases the AGGREGATE function will result in Error and the formula will return an empty string as desired.

M.

 

[Marcelo Branco]

By the way...
Peter has provided a better solution using the new functions available in Excel 365. Stick with them

M.

 

[madkinson]

By the way...
Peter has provided a better solution using the new functions available in Excel 365. Stick with them

M.

I will try those out today. Thanks.

 

[madkinson]

Try these formulas in row 2 (other results will automatically 'spill' to the required rows).

madkinson.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Doc #	Date	Act #	Qty	MIN	MAX		Activity	MIN	MAX
2	1092043890	23/05/2022	1	14.37				1	(5,400.00)	820.00
3	1092043633	23/05/2022	1	25.67	(3000.00)			2	(2,400.00)	2,520.00
4	1092043641	23/05/2022	1	26.56		820		11	(500.00)
5	1092043643	23/05/2022	1	24.6	(1500.00)			12		1,250.00
6	1092043646	23/05/2022	1	23.7	(5400.00)
7	1092043662	23/05/2022	2	21.16	(2400.00)
8	1092043676	23/05/2022	2	22.54
9	1092043753	23/05/2022	2	25.14	(520.00)
10	1092043762	23/05/2022	2	22.3	(1160.00)
11	1092043765	23/05/2022	2	18.75
12	1092043767	23/05/2022	2	16.92		2520
13	1092043828	23/05/2022	11	24.29	(500.00)
14	1092043843	23/05/2022	11	25.86
15	1092043847	23/05/2022	11	24.2
16	1092043854	23/05/2022	11	23.85
17	1092043864	23/05/2022	11	22.88	(500.00)
18	1092043881	23/05/2022	11	24.44
19	1092043886	23/05/2022	11	23.6
20	1092043887	23/05/2022	11	24.65
21	1092043898	23/05/2022	11	23.5	(500.00)
22	1092043609	23/05/2022	12	22.49		1250
23	1092043612	23/05/2022	12	27.71
24	1092043617	23/05/2022	12	25.25		460
25	1092043621	23/05/2022	12	24.33
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=UNIQUE(C2:C25)
I2:I5	I2	=IF(COUNTIFS(E2:E25,"<0",C2:C25,H2#),MINIFS(E2:E25,C2:C25,H2#),"")
J2:J5	J2	=IF(COUNTIFS(F2:F25,">0",C2:C25,H2#),MAXIFS(F2:F25,C2:C25,H2#),"")
Dynamic array formulas.

Pete, your solution works just like I needed. Now I have to bone up on the new stuff in Excel 365. Thanks for your help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.