# MIN/MAX Function

#### maknapp77

##### New Member
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?

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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Why not just use a pivot table?

Book6
ABCDEFGH
1ProductSell PriceProductMINMAX
2A75A50150
3C75C2575
4B200B25200
5C50
6A50
7B25ProductDataTotal
8B100AMin of Sell Price50
9C25Max of Sell Price150
10A150BMin of Sell Price25
11Max of Sell Price200
12CMin of Sell Price25
13Max of Sell Price75
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.

Hi maknapp77:

Welcome to MrExcel Board!

Following is a DataTable solution ...
Book1
ABCDEF
1ProductSell PriceDataTable
2A75SellPrice
3C75ProductMinMax
4B200A50150
5C50B25200
6A50C2575
7B25
8B100
9C25
10A150
Sheet3

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

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!

Replies
2
Views
146
Replies
11
Views
325
Replies
1
Views
268
Replies
1
Views
541
Replies
4
Views
195

Threads
1,196,239
Messages
6,014,166
Members
441,807
Latest member
sjkenjalo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back