Syntax. DMAX(database,field,criteria)
Definition. This function returns the largest number from a column in a list or database that matches the specified conditions.
Arguments
database (required) The cell range that makes up the list or database
field (optional) Indicates which column is used in the function
criteria (required) The cell range containing the field names and the filter criteria
Background. Use the DMIN() and DMAX() functions to find the smallest or largest value in a database based on certain criteria. DMAX() returns a value from a database, such as the highest production volume for a product within the past five years.
Example. As a wholesaler, you have many customers to whom you sell a range of the products you offer. You want to analyze your customers, sales, and products. First you want to know the product and customer with the highest sales based on orders within the United States. For this you can use the DMAX() function.
The DMAX function looks for the largest sales value for orders from the United States (see Figure 13-15). Because you want to know only the highest sales within the United States, you specify USA as a search criterion (B5). You don’t specify the product because you want to include all products in the search.
DMAX() returns 2000; that is, the highest order placed in the United States was for $2,000.
If you also want to know which company placed this order and what product the company bought, you can perform additional calculations with the DGET() function. Figure 13-16 shows a possible solution.
With the DMAX() function, you can quickly analyze your sales and customers.