How to Use Excel's Dmax Function

By eHow Computers Editor

Rate: (0 Ratings)

Excel's DMAX function is short for "database maximum." It selects a set of values from a column of a database that matches the specified criteria. DMAX then returns the largest number in this set just as MAX, the equivalent non-database function, does.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax of DMAX. It is DMAX (database, field, criteria). Database is an array that specifies the database. Field is the column name or number in the database that DMAX from which will take values. Criteria is a cell range that contains the criteria to be used in selecting the rows.

Step2

Study the Excel documentation for details regarding the arguments of database functions. Basically, they will be used to construct a query which will be used to obtain a set of values.

Step3

Enter the following in an Excel spreadsheet:
Tree, Height, Age and Yield in the first four columns of row 1;
="=Cherry" and ="<14" in the first two columns of row 2;
Tree, Height, Age and Yield in the first four columns of row 3;
Apple, 18, 20 and 14 in the first four columns of row 4;
Pear, 12, 12 and 10 in the first four columns of row 5;
Cherry, 13, 14 and 9 in the first four columns of row 6;
Cherry, 14, 15 and 10 in the first four columns of row 7;
Pear, 9, 8 and 8 in the first four columns of row 8;
Cherry, 8, 9 and 6 in the first four columns of row 9.

Step4

Interpret the formula =DMAX(A3:D9,"Yield",A1:B2) as follows: The database is defined by the array A3:D9. Note the column names in the first row of this array. "Yield" is the column name from which the values will be taken. The criteria are given by A1:B2 which indicates that the column named Tree must contain "Cherry" and the column named Height must contain a value less than 14.

Step5

Evaluate DMAX(A3:D9,"Yield",A1:B2) as MAX(9,6). DMAX will therefore return 9.