Applying a rule to the range within a function

[alexvistas]

Hello, I hope someone can help with a formula that can apply a dynamic range rule to a function argument?

I am using the min and max functions applied to sets of ranges of data. The ranges are all different lengths:
Example:
Cols A:D have number values in them.
Col E classifies the numbers as either "Green" or "Red"
Col F has the min function
Col G has the max function

Each set of data is broken down into chunks of green & red.
eg
A2: D4 Green
A5: D5 Red
A6:D10 Green and so on
Each separate colour chunk has a min and max value
eg Row 2 Col F =min(a2:d4)
Row 5 Col f =min((a5:d5)
Row 6 Col f =min(a6:d10)
At present I am having to go into each min and max formula and adjust the range depending on the amount of cells.
Question
Is it possible to add a rule to the min() function to dynamically apply a range rule that is saying and taking into account col E (Greens & Reds)?

Thanking you in advance. Hope the above is not too confusing.
I suspect you could use an indirect function somehow within the min function???

 

[GlennUK]

You can do if you have some helper columns:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	13	32	53	99	Green	1	Green1	12	115
2	71	35	65	68	Green	1	Green1
3	98	115	94	115	Green	1	Green1
4	112	96	12	52	Green	1	Green1
5	34	91	86	50	Red	2	Red2	34	91
6	93	39	74	31	Green	3	Green3	31	116
7	67	84	91	105	Green	3	Green3
8	109	108	70	75	Green	3	Green3
9	90	116	42	79	Green	3	Green3
10	31	69	40	83	Green	3	Green3
11	79	31	56	12	Red	4	Red4	12	118
12	85	116	79	118	Red	4	Red4
13	29	40	99	36	Red	4	Red4
14	43	74	35	91	Green	5	Green5	35	91
15	116	76	99	77	Red	6	Red6	14	118
16	28	14	95	118	Red	6	Red6
17	22	89	20	16	Red	6	Red6
18	103	112	91	85	Green	7	Green7	20	112
19	100	56	106	20	Green	7	Green7
Sheet

 

[alexvistas]

wow Thank you Glenn for your help. much appreciated