Documentation/Calc Functions/DMIN

From The Document Foundation Wiki
Jump to: navigation, search

Function name:

DMIN

Category:

Database

Summary:

For all rows (database records) that match the specified search criteria, DMIN calculates the minimum value across the cells (fields) of the specified column that contain numeric values. Blank cells or cells containing non-numeric characters are not included.

Syntax:

DMIN(Database; DatabaseField; SearchCriteria)

Returns:

Returns the minimum value of a cell in the given Database that qualifies the SearchCriteria for the given DatabaseField.

Arguments:

Database specifies the range of cells occupied by the database table. The first row of the range contains the field names, and subsequent rows are records with corresponding field values.

One way of defining the range of cells is to enter the cell reference for the upper left-hand cell, followed by a colon (:), and then the lower right-hand cell reference. An example might be A1:E10.

Calc provides two methods for assigning names to cell ranges and these are known as named ranges and database ranges. The Database argument may also be specified by passing the name of a defined named range or database range. Using a meaningful name to define the cell range can enhance formula readability and document maintenance.

Im-jabber.svg

Note:
Unlike the named range, the Database range can be connected to a data source, from where you can fetch data into the spreadsheet document. See the Additional Details section below for more details.

DatabaseField specifies the column which the function will use for its calculations after the search criteria have been applied and the data rows have been selected. It is not related to the search criteria.

Specify the DatabaseField argument in any of the following ways:

• By entering a reference to a header cell within the Database area. Alternatively, if the cell has been given a meaningful name as a named range or database range, enter that name. If the name does not match the name of a defined range, Calc reports a #NAME? error. If the name is valid but does not correspond to one cell only, Calc reports Err:504 (error in parameter list).

• By entering a number to specify the column within the Database area, starting with 1. For example, if a Database occupied the cell range D6:H123, then enter 3 to indicate the header cell at F6. Calc expects an integer value that lies between 1 and the number of columns defined within Database and ignores any digits after a decimal point. If the value is less than 1, Calc reports Err:504 (error in parameter list). If the value is greater than the number of columns in Database, Calc reports a #VALUE! error.

• By entering the literal column header name from the first row of the Database range, placing quotation marks around the header name. For example, “Distance to School”. If the string does not match one of the Database area’s column headings, Calc reports Err:504 (error in parameter list). You can also provide a reference to an arbitrary cell (not within the Database and SearchCriteria areas) that contains the required string.


SearchCriteria specifies the range of cells containing search criteria. Like Database, its first row is also field names, and subsequent rows are conditions for related fields. The Database and SearchCriteria areas need not be adjacent, or even on the same sheet. One way of defining the range of cells is to enter the cell reference for the upper left-hand cell, followed by a colon (:), and then the lower right-hand cell reference. For example, A13:B14. The cell range may also be specified by passing the name of a defined named range or database range. The contents of the SearchCritera area are described in more detail in the Additional Details section below.

  • See also the Wiki page about Conditional Counting and Summation.
  • For the Database, if the name does not match the name of a defined range, Calc reports a #NAME? error. Other errors that might be reported as a result of an invalid Database argument are #VALUE! and Err:504 (error in parameter list).
  • For the SearchCriteria, if the name does not match the name of a defined range, Calc reports a #NAME? error. Err:504 (error in parameter list) may also be reported as a result of an invalid SearchCriteria argument.
  • If no matches are found for the given SearchCriteria, or if there are no non-zero numeric values in the cells of the specified column for the matching records of the Database, then the function returns 0.

Additional details:

  • The Database category may be confused with a database integrated into LibreOffice. However, there is no connection between a database in LibreOffice and the Database category in LibreOffice Calc.

Fetching data from a data source to create a Database range

  • To fetch data from a data source into the spreadsheet, open the Data Source Explorer with menu View > Data Sources (Ctrl + Shift + F4), select the data source in the left pane and open the tree to display the tables or queries. Click on the table or query to display it in the right pane of the explorer.
  • Click on the top left corner of the data displayed in the Explorer then drag and drop to the top left corner of the target cell in the spreadsheet. A new database range is created with the default name of import1.
  • Choose Data > Define Range to open the Define database range dialog and set the database range properties.
Note pin.svg

The Database range contents can be refreshed once the data in the data source is updated. To update the database range, choose menu Data > Update range.


Defining Search Criteria

  • The number of columns occupied by the SearchCriteria area need not be the same as the width of the Database area. All headings that appear in the first row of SearchCriteria must be identical to headings in the first row of Database. However, not all headings in Database need appear in the first row of SearchCriteria, while a heading in Database can appear multiple times in the first row of SearchCriteria.
  • Search criteria are entered into the cells of the second and subsequent rows of the SearchCritera area, below the row containing headings. Blank cells within the SearchCritera area are ignored.
  • Create criteria in the cells of the SearchCritera area using the comparison operators <, <=, =, <>, >=, and >. = is assumed if a cell is not empty but does not start with a comparison operator.
  • If you write several criteria in one row, they are connected by AND. If you write several criteria in different rows, they are connected by OR.
  • Criteria can be created using wildcards, providing that wildcards have been enabled via the Enable wildcards in formulas option on the Tools > Options > LibreOffice Calc > Calculate dialog. When interoperability with Microsoft Excel is important for your spreadsheet, this option should be enabled.
  • Even more powerful criteria can be created using regular expressions, providing that regular expressions have been enabled via the Enable regular expressions in formulas option on the Tools > Options > LibreOffice Calc > Calculate dialog.
  • The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in Tools > Options > LibreOffice Calc > Calculate.
Gnome-dialog-warning.svg

Warning:
When using functions where one or more arguments are search criteria strings that represent a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


  • One final setting that affects how the search criteria are handled is the Search criteria = and <> must apply to whole cells option on the Tools > Options > LibreOffice Calc > Calculate dialog. This option controls whether the search criteria you set for the Database functions must match the whole cell exactly. When interoperability with Microsoft Excel is important for your spreadsheet, this option should be enabled.

Examples:

The following data will be used in some of the function description examples:

The range A1:E10 lists the children invited to Joe's birthday party. The following information is given for each entry: column A shows the name, B the grade, then age in years, distance to school in meters and weight in kilograms.

A B C D E
1 Name Grade Age Distance to School Weight
2 Andy 3 9 150 40
3 Betty 4 10 1000 42
4 Charles 3 10 300 51
5 Daniel 5 11 1200 48
6 Eva 2 8 650 33
7 Frank 2 7 300 42
8 Greta 1 7 200 36
9 Harry 3 9 1200 44
10 Irene 2 8 1000 42
11
12
13 Name Grade Age Distance to School Weight
14 >600
15
16 DCOUNT 5

The formula in cell B16 is =DCOUNT(A1:E10;D1;A13:E14)

Formula Description Returns
=DMIN(A1:E10;"Distance to School";A13:E14) where we delete everything in 14th row and then enter 3 in B14 cell To find out how much the shortest distance to school for children in 3rd grade 150
=DMIN(A1:E10;"Distance to School";A13:E14) where we delete everything in 14th row and then enter 1 in B14 cell and enter >600 in D14 cell No entry under Distance to School qualify the search criteria so function returns zero 0

For the first example, under Grade, enter 1, 2, 3, and so on, one after the other. After entering a grade number, the shortest distance to school for the children in that grade appears.

For more detailed examples, download and view this Calc spreadsheet.

Related LibreOffice functions:

MIN

DMAX

MAX

ODF standard:

Section 6.9.7, part 2

Equivalent Excel functions:

DMIN