Formula to find max and min in a range

[Ombir]

Hi Friends,

I need to find max and min from a given range based on criteria. Input and Output format is shown below.

Input:

	A	B
1	Code	Number
2	01	1234
3	01	3445
4	01	566
5	01	66634
6	02	45678
7	02	33
8	02	456
9	03	3456
10	03	23
11	03	456
12	03	678
13	03	111

<tbody>
</tbody>

Sheet2​

<tbody>
</tbody>

Output:

	G	H	I
1	Code	Min	Max
2	01	566	66634
3	02	33	45678
4	03	23	3456

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

<tbody>
</tbody>

Please help with some formulae. Thanks.

 

[Michael M]

Try

Excel 2007
	G	H	I
2	Code	Min	Max
3	1	566	66634
4	2	33	45678
5	3	23	3456
Sheet1

Cell Formulas
Range	Formula
H3	{=MIN(IF($A$2:$A$13=1,$B$2:$B$13))}
H4	{=MIN(IF($A$2:$A$13=2,$B$2:$B$13))}
H5	{=MIN(IF($A$2:$A$13=3,$B$2:$B$13))}
I3	{=MAX(IF($A$2:$A$13=1,$B$2:$B$13))}
I4	{=MAX(IF($A$2:$A$13=2,$B$2:$B$13))}
I5	{=MAX(IF($A$2:$A$13=3,$B$2:$B$13))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[XLearner]

@Michael M

Why hard coding 1, 2, 3... in your formula?
In H3: {=MIN(IF($A$2:$A$13=$G3,$B$2:$B$13))} and copy down
In I3: {=MAX(IF($A$2:$A$13=$G3,$B$2:$B$13))} and copy down

Makes sense?

Regards
XLearner

 

[Ombir]

Thanks Michael. It working fine.