DATABASE FUNCTIONS

There are six type of Database Functions. Following are the types of Database functions

• DMIN
• DMAX
• DAVERAGE
• DCOUNT
• DCOUNTA
• DGET

DMIN

DMIN function is useful to find out the lowest value of a field based on the criteria provided by you. The synatx of the function is =DMIN (Database, “Field”, “Criteria”). Database is the table form where the result has to be extracted. Field is the field name (Column name) or the position of the column in the table of which minimum value has to be found out and criteria is the range of cells where you specify the condition.

This function is extremely helpful when you have multiple records for a particular item and you need to find the lowest value among them on certain criteria.

Let us suppose we have a database of different products with multiple values and we need to find out the lowest selling price of the product “BLUE TOOTH”, here it is most suitable function that can give you the required solution. Following is the database:

To solve this problem we first have to write down the criteria/condition. Write down the condition in the following manner in somewhere else in the worksheet.

Product: BLUETOOH

Now place your cursor where you want to put your result. Write =DMIN and press CTRL+A. The following formula palate will appear. Fill the information properly as follows and click on OK to get the result.

DMAX

This function is used to return the largest number of the field or column in a database based on certain criteria. The synatx of the function is: =DMAX(Database,Field,Criteria). Meanings of the agrumnets are same as DMIN functions.

From the same database (What we have used to explain DMIN (function) if we have to find out the maximum selling price of a particular product, say BLUETOOTH, then;

Write down the criteria same as the example before (as we are working with the same product and thus our criteria is still unchanged, i.e product is BLUETOOTH).

Keep your cursor where you want to put the result, write = DMAX and press CTRL+A to bring the following formula palate:

Fill the information accordingly and press ok to get the result.

DAVERAGE

This function also takes the same arguments as DMIN and DMAX functions but returns the average of the numbers of a particular field based on certain criteria. To find out the avarage selling price of BLUETOOTH from the previous database we can use this function. Here also the all the arguments of the formula take the same data as the database, field and criteria are same. See the formula palate below:

DCOUNT

Counts the cells that contain numbers in a column of a list or database that match conditions you specify. The Syntax of DCOUNT function is =DCOUNT (DatabaseRange, FieldName, CriteriaRange).

This function examnines a list of information and counts the values in a specified column. It can only count numeric or data values, the text items and blank cells are ignored.

In the example we have to find out the number of Bulb that has wattage between 80 and 100. In Cell E24 we have written the function like this: =DCOUNT(B1:I17,C1,C21:E22).

DatabaseRange: Entire data table (B1:I17)

FieldName: The field which you want to count (C1).

CriteriaRange: The range based on which result will be generated. Write your criteria as it is written in cells C21:E22.

The criteria range actually passing the information to the DCOUNT function to count the records having Product name Buble and Wattage is between 80 and 100.

DCOUNTA

It is same as DCOUNT function but it does not ignores the text items. The Syntax of DCOUNTA function is =DCOUNTA(DatabaseRange, FieldName, CriteriaRange).

DGET

This function examines a list of information and produces one result. If more than one record matches the criteria the error #NUM is shown. If no record match the criteria the eror #VALUE is shown

The syntax of DGET function is =DGET(DatabaseRange, FieldName, CriteriaRange).

In the example we are going to find out the quantity in hand of the product Bulb with brand name Horizon and Wattage 100. In cell E24 the function is written like this:

=DGET(B1:B17,H1,B21:E22).

All the agruments are supplied with the same logic as we have discussed in DCOUNT function.

This example extracts information from just one record as shown in figure.