DMAX Function in Excel: Explained
In this article, you will learn how to use the DMAX formula in Excel.
What is the DMAX function in Excel?
The DMAX function in Excel is a database function that returns the maximum value selected from a database table-like array or range using a specified condition.
When is the DMAX function helpful in Excel?
The DMAX function in Excel is particularly helpful when you're dealing with large databases or tables and you need to identify the maximum value in a specific column based on certain criteria. It essentially helps in summarizing and extracting valuable insights from complex datasets. Here are a few scenarios where the DMAX function can be useful:
- Business Data Analysis: If you're running a business and have a large database of sales, the DMAX function could be used to identify the highest sales made by a specific salesperson or in a particular region.
- Financial Data Analysis: The DMAX function can be used to extract the maximum value from large financial datasets, for example, to find the highest closing stock price for a specific company over a defined period.
Understanding the syntax of the DMAX function in Excel
The syntax for the DMAX function is the following:
Database: This is a required argument that is the range of cells that is your database, including headers.
Field: This required argument is the column in your database that the function will be operated on. This column can be written in two ways. You can either write your column label in quotation marks or you can input the number of the column from your table (meaning the first column of your dataset table is 1, the second is 2, and so on).
Criteria: This required argument is a range of cells that is the conditions you specify. The criteria array should include at least one column label with at least one cell below the label.
How to use the DMAX function in Excel
Suppose you have a data set of sales information with columns for “Region”, "Product", "Salesperson", and "Sales" and you want to know the largest sale made by specified conditions, for this you could use the DMAX function. For instance, suppose you wanted to find out what the largest sale was in the East or the largest sale made by the salesperson Patrick for Product A. Below is an image of using the DMAX function for those examples.
As indicated by the annotations in the image above, the database argument encompasses the whole array, including headers. As previously stated, the field argument can be entered either as the column header enclosed in quotation marks or as the column number. In the example above, we are looking to calculate the largest sale, so we need the sales column as our field argument. This can be achieved by either typing "Sales" within the function or by recognizing that the Sales column is the 4th one in the database and entering "4". Both of these inputs are synonymous and yield the same result. The example above demonstrates the use of both kinds of inputs. Next, the criteria argument consists of an array that is distinct from the database and defines the conditions you wish to apply to the calculation. For instance, in the second formula, there are two conditions, so a small table is created with the desired headers (Product and Salesperson) and specific values under them (A under Product and Patrick under Salesperson).
Now that you found the largest sale in the East region, let’s say you want to know the salesperson who made that sale so you can give them a promotion. To do this you can use the DMAX function in conjunction with the LOOKUP function. The syntax for that would be the following:
- The result of this function would tell us that the largest sale was made in the East by the salesperson Rachel.
This is one example where the DMAX function in Excel could be used with another Excel function (in this case the LOOKUP function) to find out more about your dataset.
What is the difference between the DMAX and MAXIFS functions in Excel?
Both DMAX and MAXIFS are Excel functions used to calculate the maximum value in a given set of data based on specified criteria. However, they differ in how they're used and the structure of data they require.
- The DMAX function requires a structured database with a specific header row. It accepts three arguments: the database (the range of cells making up the list or database), the field (the column the function will operate on), and the criteria (a range of cells containing conditions to be met). This makes DMAX flexible when dealing with complex or multiple criteria fields.
- The MAXIFS function finds the maximum value among cells specified by a set of conditions or criteria. Unlike the DMAX function, the MAXIFS function does not need a structured database but works best with a simple range of cells.
The choice between the two usually depends on the nature of your data and the complexity of the conditions you need to apply.
Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.
