Part 1: Introduction to the DMIN Function in Microsoft Excel
Definition
The DMIN function is a database function in Excel that calculates the smallest number in a column of records that matches given conditions.
Purpose
The primary purpose of the DMIN function retrieves the minimum value from a specified range of data based on defined criteria. It’s helpful in many fields, particularly business analysis, financial modeling, and large data sets.
Syntax & Arguments
The syntax of the DMIN the function is as follows:
=DMIN(database, field, criteria) Explanation of Arguments
Database: This is the range of cells that comprise the list or database. It must include column labels.
Field: This is the column in the database from which the minimum value is to be determined. It can be the column label enclosed in quotation marks or a number that represents the position of the column in the list.
Criteria: This is the range of cells that contains the conditions specified. It must include at least one column label and at least one cell below the column label for the state.
Return Value
The DMIN the function returns the smallest number in the selected database column that meets the specified criteria.
Remarks
- The “Database” argument must include the column headers.
- If no rows meet the given criteria,
DMINreturns the#NUM!error value. - If the “Field” argument contains non-numeric data,
DMINreturns the#VALUE!error value. - Text criteria must be enclosed in double-quotes.
Part 2: Examples of Using the DMIN Function in Microsoft Excel
Example 1:
Purpose
Find the smallest sales value for a specific product type.
Data table and formula
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Type | Sales Volume | Sales Value | Formula |
| 2 | Electronics | 200 | $2,000 | |
| 3 | Furniture | 150 | $1,500 | |
| 4 | Stationery | 300 | $1,200 | |
| 5 | =DMIN(A1:C4, "Sales Value", E1:F2) |
Criteria Table:
| E | F | |
|---|---|---|
| 1 | Product Type | |
| 2 | Electronics |
Result table
| D | |
|---|---|
| 1 | Minimum Sales Value for Electronics |
| 2 | $2,000 |
Explanation
This formula finds the smallest sales value for ‘Electronics.’ The $2,000 result represents the smallest sales value among the ‘Electronics’ product type.
Example 2:
Purpose
Find the smallest stock quantity for a specific supplier.
Data table and formula
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Supplier | Product | Stock Quantity | Formula |
| 2 | Alpha Corp | Laptop | 10 | |
| 3 | Alpha Corp | Mobile Phone | 15 | |
| 4 | Beta Corp | Laptop | 20 | |
| 5 | =DMIN(A1:C4, "Stock Quantity", E1:F2) |
Criteria Table:
| E | F | |
|---|---|---|
| 1 | Supplier | |
| 2 | Alpha Corp |
Result table
| D | |
|---|---|
| 1 | Minimum Stock Quantity for Alpha Corp |
| 2 | 10 |
Explanation
This formula finds the smallest stock quantity for ‘Alpha Corp.’ The result, 10, represents the smallest stock quantity among all products supplied by ‘Alpha Corp.’
Example 3:
Purpose
Find the minor price for a specific product category.
Data table and formula
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Product | Price | Formula |
| 2 | Electronics | Laptop | $900 | |
| 3 | Electronics | Mobile Phone | $700 | |
| 4 | Furniture | Chair | $100 | |
| 5 | =DMIN(A1:C4, "Price", E1:F2) |
Criteria Table:
| E | F | |
|---|---|---|
| 1 | Category | |
| 2 | Electronics |
Result table
| D | |
|---|---|
| 1 | Minimum Price for Electronics |
| 2 | $700 |
Explanation
This formula finds the most minor price for the ‘Electronics’ category. The $700 result represents the nominal price among all products within the ‘Electronics’ category.
Example 4:
Purpose
Find the minimum sales value of a specific product category and display a custom message based on the output.
Data table and formula
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Category | Sales Volume | Sales Value | Formula | Custom Message |
| 2 | Electronics | 200 | $2,000 | ||
| 3 | Furniture | 150 | $1,500 | ||
| 4 | Stationery | 300 | $1,200 | ||
| 5 | =DMIN(A1:C4, "Sales Value", F1:G2) | =IF(D5<=1500, "Low Sales", "High Sales") |
Criteria Table:
| F | G | |
|---|---|---|
| 1 | Product Category | |
| 2 | Electronics |
Result table
| E | |
|---|---|
| 1 | Minimum Sales Value for Electronics |
| 2 | $2,000 |
| 3 | Custom Message |
| 4 | High Sales |
Explanation
In this scenario, the DMIN function determines the smallest sales value for ‘Electronics.’ The IF function is then applied to generate a custom message based on the minimum sales value. If the value is less than or equal to $1,500, the news is “Low Sales.” Otherwise, it is “High Sales.”
Example 5:
Purpose
Find the total of the minimum stock quantity for a specific supplier and a set value.
Data table and formula
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Supplier | Product | Stock Quantity | Formula | Total Stock |
| 2 | Alpha Corp | Laptop | 10 | ||
| 3 | Alpha Corp | Mobile Phone | 15 | ||
| 4 | Beta Corp | Laptop | 20 | ||
| 5 | =DMIN(A1:C4, "Stock Quantity", F1:G2) | =D5 + 5 |
Criteria Table:
| F | G | |
|---|---|---|
| 1 | Supplier | |
| 2 | Alpha Corp |
Result table
| E | |
|---|---|
| 1 | Minimum Stock Quantity for Alpha Corp |
| 2 | 10 |
| 3 | Total Stock After Adding 5 |
| 4 | 15 |
Explanation
In this example, the DMIN the function finds the smallest stock quantity for ‘Alpha Corp.’ Then the SUM the function is used to add 5 to the minimum stock quantity.
Example 6:
Purpose
Find the minimum price for a specific product category and return the corresponding product name.
Data table and formula
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Product | Price | Formula | Product with Min Price |
| 2 | Electronics | Laptop | $900 | ||
| 3 | Electronics | Mobile Phone | $700 | ||
| 4 | Furniture | Chair | $100 | ||
| 5 | =DMIN(A1:C4, "Price", F1:G2) | =INDEX(B2:B4, MATCH(D5, C2:C4, 0)) |
Criteria Table:
| F | G | |
|---|---|---|
| 1 | Category | |
| 2 | Electronics |
Result table
| E | |
|---|---|
| 1 | Minimum Price for Electronics |
| 2 | $700 |
| 3 | Product with Minimum Price |
| 4 | Mobile Phone |
Explanation
This scenario uses the DMIN function to find the most minor price for the ‘Electronics’ category. The INDEX-MATCH the function is then employed to return the product name corresponding to the minimum cost.
Example 7:
Purpose
Find the minimum sales value of a specific product category and return a boolean value based on a comparison with a set value.
Data table and formula
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Category | Sales Volume | Sales Value | Formula | Is Min Value Less Than $1500 |
| 2 | Electronics | 200 | $2,000 | ||
| 3 | Furniture | 150 | $1,500 | ||
| 4 | Stationery | 300 | $1,200 | ||
| 5 | =DMIN(A1:C4, "Sales Value", F1:G2) | =D5<1500 |
Criteria Table:
| F | G | |
|---|---|---|
| 1 | Product Category | |
| 2 | Electronics |
Result table
| E | |
|---|---|
| 1 | Minimum Sales Value for Electronics |
| 2 | $2,000 |
| 3 | Is Minimum Sales Value Less Than $1500 |
| 4 | FALSE |
Explanation
Here, the DMIN the function determines the smallest sales value for ‘Electronics.’ The LESS THAN operator (<) is then used to return a boolean value based on whether the minimum sales value is less than $1500.
Example 8:
Purpose
Find the minimum sales value of a specific product category and return the corresponding sales volume.
Data table and formula
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Category | Sales Volume | Sales Value | Formula | Sales Volume for Min Sales Value |
| 2 | Electronics | 200 | $2,000 | ||
| 3 | Furniture | 150 | $1,500 | ||
| 4 | Stationery | 300 | $1,200 | ||
| 5 | =DMIN(A1:C4, "Sales Value", F1:G2) | =VLOOKUP(D5, A2:C4, 2, FALSE) |
Criteria Table:
| F | G | |
|---|---|---|
| 1 | Product Category | |
| 2 | Electronics |
Result table
| E | |
|---|---|
| 1 | Minimum Sales Value for Electronics |
| 2 | $2,000 |
| 3 | Sales Volume for Minimum Sales Value |
| 4 | 200 |
Explanation
In this scenario, the DMIN a function is used to identify the smallest sales value for ‘Electronics.’ The VLOOKUP the function is then utilized to return the sales volume corresponding to this minimum sales value.
Example 9:
Purpose
Calculate the minimum sales value of a specific product category, and return a custom message based on a set of nested IF conditions.
Data table and formula
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Category | Sales Volume | Sales Value | Formula | Custom Message |
| 2 | Electronics | 200 | $2,000 | ||
| 3 | Furniture | 150 | $1,500 | ||
| 4 | Stationery | 300 | $1,200 | ||
| 5 | =DMIN(A1:C4, "Sales Value", F1:G2) | =IF(D5<=1500, "Low Sales", IF(D5>1500 AND D5<=2000, "Moderate Sales", "High Sales")) |
Criteria Table:
| F | G | |
|---|---|---|
| 1 | Product Category | |
| 2 | Electronics |
Result table
| E | |
|---|---|
| 1 | Minimum Sales Value for Electronics |
| 2 | $2,000 |
| 3 | Custom Message |
| 4 | Moderate Sales |
Explanation
In this scenario, the DMIN the function is used to find the smallest sales value for ‘Electronics.’ Nested IF Conditions are then used to generate a custom message based on this minimum sales value. If the sales value is less than or equal to $1,500, it returns “Low Sales”; if it’s between $1,500 and $2,000, it replaces “Moderate Sales”; otherwise, it replaces “High Sales.”
Example 10:
Purpose
Find the minimum sales value of a specific product category and calculate the difference between the minimum and set values.
Data table and formula
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Category | Sales Volume | Sales Value | Formula | Difference From Set Value |
| 2 | Electronics | 200 | $2,000 | ||
| 3 | Furniture | 150 | $1,500 | ||
| 4 | Stationery | 300 | $1,200 | ||
| 5 | =DMIN(A1:C4, "Sales Value", F1:G2) | =D5 - 1800 |
Criteria Table:
| F | G | |
|---|---|---|
| 1 | Product Category | |
| 2 | Electronics |
Result table
| E | |
|---|---|
| 1 | Minimum Sales Value for Electronics |
| 2 | $2,000 |
| 3 | Difference from $1,800 |
| 4 | $200 |
Explanation
In this case, the DMIN the function is employed to find the smallest sales value for ‘Electronics.’ The SUBTRACTION operator (-) is then utilized to calculate the difference between this minimum sales value and a set value of $1,800.
Part 3: Tips and Tricks
- Always ensure that the “Database” argument includes the column labels.
- Consider using column numbers as a “Field” argument because rearranging the columns will alter the function results.
- Non-numeric data in the “Field” argument will lead to an
#VALUE!error. - If the specified criteria do not match any data row in the database,
DMINwill return a#NUM!error. - Text criteria can use wildcard characters:
?matches any single character, and*matches any sequence of characters. - When specifying multiple criteria, remember that a data row needs to meet all requirements to be considered. If you need to apply an ‘OR’ condition, you may need to adjust the criteria range or use helper columns.
- Review your data and results for accuracy. Even the most perfect formulas can yield incorrect results if the data isn’t accurate or clean.