Part 1: Introduction to DMAX Function in Microsoft Excel
Definition: DMAX is a database function in Excel that calculates the maximum value in a selected database column based on multiple conditions. This function is an advanced version of the MAX function, which includes criteria.
Purpose: DMAX aims to find the maximum value in a database column where the corresponding values in other rows meet certain conditions.
Syntax & Arguments:
The syntax of the DMAX function is as follows:
=DMAX(database, field, criteria) Let’s dive into what these arguments mean:
Database: This is the range of cells that make up the database. The database should include column headers.
Field: This is the column in the database where the function will be used. The area can be given as text with the column header or as a number that represents the position of the column in the database.
Criteria: This is the cell range containing the conditions you specify. It should include at least one column header and one cell below the column header for defining requirements.
Return Value: DMAX returns the most significant number in a set of values based on given criteria.
Remarks: If the database or field argument is not valid or the field does not exist in the database, the DMAX function will return a #VALUE! Error. Also, if no record in the database meets the criteria, DMAX will return a #NUM! Error.
Part 2: Examples of DMAX Function in Microsoft Excel
Example 1
Purpose: Find the highest sale in a particular region.
Data Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | ||
| 2 | West | 500 | ||
| 3 | East | 600 | ||
| 4 | West | 700 | ||
| 5 | =DMAX(A1:B4, “Sales”, A1:A2) |
Result Table:
| D | |
|---|---|
| 5 | 700 |
Explanation: In this example, we want to determine the highest sale in the West region. We have our data in columns A and B, with column A having parts and column B having sales. We have specified the criteria as “West” in cell A2. The DMAX formula is set up in cell D5, referencing the database range A1:B4, the field “Sales”, and the criteria range A1:A2. The result is 700, the highest sale in the ‘West’ region.
Example 2
Purpose: Find the highest sale in January.
Data Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | ||
| 2 | January | 1000 | ||
| 3 | February | 1200 | ||
| 4 | January | 1500 | ||
| 5 | =DMAX(A1:B4, “Sales”, A1:A2) |
Result Table:
| D | |
|---|---|
| 5 | 1500 |
Explanation: In this example, we want to determine the highest sale in January. We have our data in columns A and B, with column A having months and column B having sales. We have specified the criteria as “January” in cell A2. The DMAX formula is set up in cell D5, referencing the database range A1:B4, the field “Sales”, and the criteria range A1:A2. The result is 1500, which is the highest sale in January.
Example 3
Purpose: Find the highest sale for product ‘A’.
Data Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sales | ||
| 2 | A | 2000 | ||
| 3 | B | 2200 | ||
| 4 | A | 2500 | ||
| 5 | =DMAX(A1:B4, “Sales”, A1:A2) |
Result Table:
| D | |
|---|---|
| 5 | 2500 |
Explanation: In this example, we want to find out the highest sale for product ‘A’. We have our data in columns A and B, with column A having products and column B having sales. We have specified the criteria as ‘A’ in cell A2. The DMAX formula is set up in cell D5, referencing the database range A1:B4, the field “Sales”, and the criteria range A1:A2. The result is 2500, the highest sale for product ‘A’.
Example 4
Purpose: Find the maximum sales value in the East region and display a message based on the result.
Data Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | ||
| 2 | East | 100 | ||
| 3 | West | 200 | ||
| 4 | East | 300 | ||
| 5 | =IF(DMAX(A1:B4, “Sales”, A1:A2) > 200, “High Sales”, “Low Sales”) |
Result Table:
| D | |
|---|---|
| 5 | High Sales |
Explanation: We use the DMAX function nested within an IF function in this example. The DMAX function is finding the maximum sales value in the East region. The IF function then checks if this value is greater than 200. If it is, it returns the text “High Sales”. Otherwise, it replaces “Low Sales”.
Example 5
Purpose: Sum the maximum sales value from East and West regions.
Data Table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | East | 100 | West | 200 | |
| 3 | West | 150 | East | 250 | |
| 4 | East | 200 | West | 300 | |
| 5 | =SUM(DMAX(A1:B4, “Sales”, A1:A2), DMAX(C1:D4, “Sales”, C1:C2)) |
Result Table:
| E | |
|---|---|
| 5 | 500 |
Explanation: In this example, we use the DMAX function within a SUM function. The DMAX functions to find the maximum sales values in the East and West regions. The SUM function then adds these two maximum values together.
Example 6
Purpose: Retrieve the salesperson’s name with the maximum sales value in the East region.
Data Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | Salesperson | |
| 2 | East | 100 | John | |
| 3 | West | 200 | Sarah | |
| 4 | East | 300 | Mike | |
| 5 | =VLOOKUP(DMAX(A1:B4, “Sales”, A1:A2), B1:C4, 2, FALSE) |
Result Table:
| D | |
|---|---|
| 5 | Mike |
Explanation: In this example, we use the DMAX function within a VLOOKUP function. The DMAX function is finding the maximum sales value in the East region. The VLOOKUP function then looks for this value in column B and returns the corresponding salesperson name from column C.
Example 7
Purpose: Check if the maximum sales value in the East region is more significant than a specific target.
Data Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | ||
| 2 | East | 100 | ||
| 3 | West | 200 | ||
| 4 | East | 300 | ||
| 5 | =DMAX(A1:B4, “Sales”, A1:A2) > 250 |
Result Table:
| D | |
|---|---|
| 5 | TRUE |
Explanation: In this example, we are using the DMAX function to find the maximum sales value in the East region. The formula then checks if this value is greater than 250. If it is, it returns TRUE. Otherwise, it returns FALSE.
Example 8
Purpose: Retrieve the product associated with the maximum sales value in the East region.
Data Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | Product | |
| 2 | East | 100 | A | |
| 3 | West | 200 | B | |
| 4 | East | 300 | C | |
| 5 | =INDEX(C2:C4, MATCH(DMAX(A1:B4, “Sales”, A1:A2), B2:B4, 0)) |
Result Table:
| D | |
|---|---|
| 5 | C |
Explanation: In this example, we use the DMAX function within an INDEX-MATCH function. The DMAX function is finding the maximum sales value in the East region. The INDEX-MATCH function then looks for this value in column B and returns the corresponding product from column C.
Example 9
Purpose: Calculate the average maximum sales values in the East and West regions.
Data Table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | East | 100 | West | 200 | |
| 3 | West | 150 | East | 250 | |
| 4 | East | 200 | West | 300 | |
| 5 | =AVERAGE(DMAX(A1:B4, “Sales”, A1:A2), DMAX(C1:D4, “Sales”, C1:C2)) |
Result Table:
| E | |
|---|---|
| 5 | 250 |
Explanation: We use the DMAX function within an AVERAGE role in this example. The DMAX functions to find the maximum sales values in the East and West regions. The AVERAGE function then calculates the average of these two maximum values.
Example 10
Purpose: Determine if the maximum sales value in the East region equals the maximum sales value in the West region.
Data Table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | East | 100 | West | 200 | |
| 3 | West | 150 | East | 250 | |
| 4 | East | 200 | West | 300 | |
| 5 | =DMAX(A1:B4, “Sales”, A1:A2) = DMAX(C1:D4, “Sales”, C1:C2) |
Result Table:
| E | |
|---|---|
| 5 | FALSE |
Explanation: In this example, we use two DMAX functions to find the maximum sales values in the East and West regions. The formula then checks if these two maximum values are equal. If they are, it returns TRUE. Otherwise, it returns FALSE.
Part 3: Tips and Tricks
Always include column headers in your database and criteria ranges. This helps Excel to understand which column the criteria should apply to.
Make sure the criteria range does not include empty cells. If it does, DMAX might return incorrect results.
DMAX can handle multiple criteria. Add various measures to the requirements range under the appropriate column header if you want to apply them.
DMAX is not case-sensitive. The function does not differentiate between uppercase and lowercase characters when applying criteria.