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

 

[6StringJazzer]

You would use MAXIF and MINIF. Also it would help if you updated your profile to show what version of Excel you are using. These functions are not in older versions.

I can't make any sense out of your sample data. What are the numbers in the MIN and MAX columns?
Where/how do you want to show the MIN/MAX for an activity range? Do you want to show MIN/MAX for every activity?

 

[madkinson]

The MIN/MAX values are shown if over or under a specific threshold, else they are blank. I would like to focus on activities 11 and 12. I am using Excel 365

 

[6StringJazzer]

Let me ask the question a different way. In activity 11, your Qty numbers are all between 22 and 26. You don't show any maximum; I would expect you to show a maximum of 25.86. You show multiple minimums and they are all -500; I would expect to see a minimum of 22.88. I cannot make any sense out of your sample MAX MIN number.

 

[Peter_SSs]

As well as the clarification that @6StringJazzer has asked for ..

The MIN/MAX values are shown if over or under a specific threshold,

.. what specific threshold and how will we know what it is.

I am using Excel 365

Please add that to your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[madkinson]

The actual sheet has 20+ columns and averages about 200-300 rows depending on the day. I had to cut columns to make it fit. The only columns I care about for MIN/MAX are the Min and Max columns. I was trying to get results that look like this:

Activity	MIN	MAX
1	(5,400)	820
2	(2,400)	2,520
11	(500)
12		1,250

 

[6StringJazzer]

So you want to calculate the MIN of the Min, and the MAX for the Max? Where do you want the result to go for a given Activity? In a separate table?

 

[6StringJazzer]

I would do this with a pivot table.

 

[Marcelo Branco]

The only columns I care about for MIN/MAX are the Min and Max columns. I was trying to get results that look like this:

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.

 

[6StringJazzer]

Try
Using your data sample above

In this solution how is column H populated?