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
Code	Number

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01[/TD]
[TD="align: right"]1234[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01[/TD]
[TD="align: right"]3445[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01[/TD]
[TD="align: right"]566[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]01[/TD]
[TD="align: right"]66634[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]02[/TD]
[TD="align: right"]45678[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]02[/TD]
[TD="align: right"]33[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]02[/TD]
[TD="align: right"]456[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]03[/TD]
[TD="align: right"]3456[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]03[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]03[/TD]
[TD="align: right"]456[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]03[/TD]
[TD="align: right"]678[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]03[/TD]
[TD="align: right"]111[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Output:

	G	H	I
Code	Min	Max

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01[/TD]
[TD="align: right"]566[/TD]
[TD="align: right"]66634[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]02[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]45678[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]03[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]3456[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.