A few weeks ago Karine asked me if there was a way she could find the minimum value for a specific product from a large table of data.
For example, if we take this table of data, let’s say we wanted to find the lowest price for Apples.
Excel MIN IF
One way is to use an array formula like this:
=MIN(IF((A4:A19=F3)*(B4:B19),(B4:B19)))
Entered with CTRL+SHIFT+ENTER as this is an array formula.
And where F3 contained our criteria; ‘Apples’
Result = $4.00
Excel MAX IF
Alternatively Karine could find the highest sale price by replacing MIN with MAX:
=MAX(IF((A4:A19=F3)*(B4:B19),(B4:B19)))
Result = $6.00
DMIN - Another Way to Find MIN IF
Of course many people are wary of array formulas and for that reason I prefer to use simpler alternatives if they’re available.
Introducing Database Function DMIN.
DMIN Syntax
=DMIN(database,field,criteria)
Returns the smallest number in the field (column of records in the database that match the conditions you specify.
Database
– Our whole table including the row headers – A3:B19
Field
– The column heading containing the value – “Price”
Criteria
– Cells H3 – our column name, and H4 – our criteria
DMAX Alternative
The DMAX function works the same way but instead…wait for it….it finds the maximum:
=DMAX(A3:B19,"Price",H3:H4)
=$6.00
The only downside to Database functions is the spreadsheet real estate the criteria occupies, but I don’t think this should be an issue since each worksheet has over 1 billion cells.
Thanks for your question Karine.
Janakiram
Suppose I have three columns, one represents the area,other two represent fruit and prices respectively. I would like to apply D MIN and D MAX for area which should give least/highest price and name of the fruit. How it can be done.
Mynda Treacy
Hi Janakiram,
Please post this question in our Excel forum and if you can provide a sample file that would help you get a speedy reply.
Thanks,
Mynda