There is a way where you can find the minimum value and a maximum value for a specific product from a large table of data.  For example,the table below is a list of different fruits and their prices.  We can  find the lowest and the highest prices for Apples. I set up an Excel sheet showing formulas using MINIF and MAXIF and an alternative way using DMIN and DMAX.

minif and maxif

 

Click on the picture to enlarge the view.

In the first scenario, place the name of the fruit in a cell D2. I created the MINIF formula in E4 to find the lowest price for apples. You do not include the field name of the table, just the data.  The same for MAXIF in E5.  The “IF” in the MIN and MAX formula tells Excel to look only if the fruit is equal to apples and find the minmum price among that group. The MINIF and MAXIF are array formulas and you need to NOT hit Enter but to click on CTRL+SHIFT+ENTER in order to enter it into the cell.

=MIN(IF((A2:A17=D2)*(B2:B17),(B2:B17)))
=MAX(IF((A2:A17=D2)*(B2:B17),(B2:B17)))

 

In the second scernario, which is mucher simplier, is to use the DMIN and DMAX.  The “D” represents the database which includes the fieldnames (fruit and price). The formula needs the database, the name of the field in double quotes, and  the criteria area where you enter any of the fruit category (located in D12 and D13).

=DMIN(A1:B17,”Price”,D12:D13)
=DMAX(A1:B17,”Price”,D12:D13)

Have fun!

Advertisements