MIN/MAX Function

[maknapp77]

I am trying to figure out the correct formula for returning, in the example below, minimum and maximum sell price. For instance, I want to return the MIN/MAX sell price for A (MIN 50, MAX 75), for B, for C, etc. I just cannot figure out the correct syntax. Can someone please help?

EXAMPLE:
Product.......Sell Price........MIN.........MAX
A................75
C................75
B................200
C................50
A................50
B................25
B................100
C................25
A................150

 

[Norie]

Why not just use a pivot table?

 

[Aladin Akyurek]

Book6
	A	B	C	D	E	F	G	H
1	Product	Sell Price			Product	MIN	MAX
2	A	75			A	50	150
3	C	75			C	25	75
4	B	200			B	25	200
5	C	50
6	A	50
7	B	25			Product	Data	Total
8	B	100			A	Min of Sell Price	50
9	C	25				Max of Sell Price	150
10	A	150			B	Min of Sell Price	25
11						Max of Sell Price	200
12					C	Min of Sell Price	25
13						Max of Sell Price	75
14
Sheet1

F2:

=MIN(IF(($A$2:$A$10=E2)*$B$2:$B$10,$B$2:$B$10))

which is confirmed with control+shift+enter (not just with enter) and coied down.

G2:

=MAX(IF(($A$2:$A$10=E2)*$B$2:$B$10,$B$2:$B$10))

which is confirmed with control+shift+enter (not just with enter) and coied down.

The same results can also be obtained by means of a pivot table as shown in E7:G13.

 

[Yogi Anand]

Hi maknapp77:

Welcome to MrExcel Board!

Following is a DataTable solution ...

Book1
	A	B	C	D	E	F
1	Product	Sell Price		DataTable
2	A	75			SellPrice
3	C	75		Product	Min	Max
4	B	200		A	50	150
5	C	50		B	25	200
6	A	50		C	25	75
7	B	25
8	B	100
9	C	25
10	A	150
Sheet3

Please post back if this works for you and you need to discuss this further!

 

[maknapp77]

Thanks alot for the help! I knew how to get the MIN/MAX using the pivot table but I could not figure out the formula.

The formulas for MIN/MAX that you provided worked great!

Thanks again!