1. Computing

Excel DMAX Function Tutorial

By

Find the Largest Number in an Excel Database
Excel Database Functions - DMAX

Excel Database Functions - DMAX

© Ted French

DMAX Function Overview

The DMAX function is one of Excel's database functions.

This group of functions is designed to make it easy to summarize information from large tables of data.

They do this by returning specific information based on one or more criteria chosen by the user.

The DMAX function can be used to find the largest or maximum number for data that meets the set criteria.

DMAX Syntax and Arguments

The syntax for the DMAX function is:

=DMAX ( database, field, criteria )

The arguments for the DMAX function are:

Database: (required) Specifies the range of cell references containing the database. The field names must be included in the range.

Field: (required) Indicates which column or field is to be used by the function in its calculations. Enter the argument either by typing the field name in quotes - such as "Radius" - or enter the column number - such as 3.

Criteria: (required) Lists the range of cells containing the conditions specified by the user. The range must include at least one field name from the database and at least one other cell reference indicating the condition to be evaluated by the function.

Example Using Excel's DMAX Function

This example will use DMAX to find which of the inner planets (Mercury, Venus, Earth, Mars) has the largest radius.

Click on the image above for a larger view of this example.

Note: The tutorial does not include formatting steps. Information on worksheet formatting options is available in this Basic Excel Formatting Tutorial.

Entering the Tutorial Data

  1. Enter the data table as seen in the image above into cells A1 to F11

  2. Copy the field names in cells A2 to F2

  3. Paste the field names in cells A13 to F13 - these will be used as part of the Criteria argument

Selecting a Criterion

Since we only want DMAX to look at data for the inner planets, we need to choose a criterion that will eliminate the remaining five outer planets.

The distance from a planet to the Sun is measured in astronomical units (AU). The distance from Earth to the Sun is set as one AU.

Since all of the inner planets are less than two AU from the sun, we can use " < 2 " as a criterion that will restrict DMAX to looking only at data for the first four planets.

In Excel, the " < " character is the less than symbol.

  1. In cell B14 type the criteria < 2

  2. In cell B15 type the heading Largest Radius: to indicate the information we will be finding with DMAX

Naming the Database

Using a named range for large ranges of data such as a database can not only make it easier to enter this argument into the function, but it can also prevent errors caused by selecting the wrong range.

Named ranges are very useful if you use the same range of cells frequently in calculations or when creating charts or graphs.

  1. Drag select cells A2 to F11 in the worksheet to highlight the range

  2. Click on the name box above column A in the worksheet

  3. Type Planets into the name box to create the named range

  4. Press the Enter key on the keyboard to complete the entry
  5. Opening the DMAX Dialog Box

    A function's dialog box provides an easy method for entering data for each of the function's arguments.

    Opening the dialog box for the database group of functions is done by clicking on the function wizard button (fx) located next to the formula bar above the worksheet - see image above.

    1. Click on cell C15 - the location where the results of the function will be displayed

    2. Click on the function wizard button (fx) icon to bring up the Insert Function dialog box

    3. Type DMAX in the Search for a function window at the top of the dialog box

    4. Click on the GO button to search for the function

    5. The dialog box should find DMAX and list it in the Select a function window

    6. Click OK to open the DMAX function dialog box

    Completing the Arguments

    1. Click on the Database line of the dialog box

    2. Type the range name Planets into the line

    3. Click on the Field line of the dialog box

    4. Type the field name "Radius" into the line - be sure to include the quotation marks

    5. Click on the Criteria line of the dialog box

    6. Drag select cells A13 to F14 in the worksheet to enter the range

    7. Click OK to close the DMAX function dialog box and complete the function

    8. The answer 6378 should appear in cell C15

    9. 6,378 km ( 3,963 miles ) is the radius of Earth which is the largest of the inner planets

    10. When you click on cell C7 the complete function
      =DMAX ( Planets, "Radius", A13:F14) appears in the formula bar above the worksheet

    Note: If we wanted to find the largest radius for all the planets, we could use the regular MAX function, since we do not need to specify criteria to limit what data is used by the function.

©2014 About.com. All rights reserved.