Étiquette : data base function

This function counts the number of records that contain numerical values in the specified field column, provided they match the defined conditions.

Syntax

DCOUNT(database; field; criteria)

Arguments

• database (required): This is the cell range that identifies your list or database.
• field (optional): This indicates which column the function will use for counting. You can enter the column label enclosed in quotation marks (e.g., « country/region », « sales », or « company »). Alternatively, you can use a number representing the column’s position within the list (e.g., 1 for the first column, 2 for the second, and so on).
• criteria (required): This is the cell range that holds your field names and the filter criteria. Any range can be used for the criteria argument, as long as it includes at least one column label and at least one cell below that label where you specify a condition for the column.

Background

The DCOUNT() function performs a simple task, but it can be a huge time-saver, especially when you’re working with databases and need to count many records. Use the DCOUNT() function to count records that contain numbers and match specific criteria within a database.

Example

Imagine you’re a wholesaler and you want to know how many deliveries have been invoiced. This means you want to count all sales that are greater than zero.

First, open a new worksheet and define your criteria range using the « Sales » field from your original data. Then, specify the search criterion as >0.

Now, use the DCOUNT() function to calculate the number of sales in your database that are greater than zero (see Figure below).

For the database argument, specify the named range « Database », which refers to A1:F7008. In this example, using a dynamic name like « Database » saves you from having to type the cell range A1:F7008 every time you refer to your database.

As you can see in Figure below, you’ll get the same result if you enter the name « SEARCH1 » for the criteria range (A11:A12 in the figure) and specify « Sales » instead of cell A11 for the field argument. Remember to enclose the field name, « Sales », in quotation marks. As shown in Figure below, the result remains 7,000.

This means your database contains 7,000 records for sales greater than zero. You can quickly count sales greater than 1,500 in the same way.

This function calculates the arithmetic mean for values within a list or database column that meet specified conditions.

Syntax

DAVERAGE(database; field; criteria)

Arguments

• database (required): The cell range that constitutes the list or database.
• field (optional): Specifies which column the function will use.
• criteria (required): The cell range that contains the field names and the filter criteria.

Background

The arithmetic mean is the most commonly recognized mean value. Its ease of calculation makes it crucial for summarizing and analyzing data. To compute the mean, values in a range are summed, and then the sum is divided by the total number of values.

Use the DAVERAGE() function to calculate the mean for selected database records.

Example

It’s year-end, and you’re analyzing sales. Specifically, you want to determine the average sales of products in various countries. The DAVERAGE() function can help you calculate these values.

To find the average sales for « Chang » in the United States, you would specify « USA » as the country/region and « Chang » as the item name. Since you’re looking for average sales, you’d use « Sales » as the database field. DAVERAGE() would then return $1,522.85 (refer to Figure below).

You also want to calculate the overall average sales for « Chang ». To do this for « Chang » across all countries and regions, simply omit the country/region as a search criterion. This ensures all countries and regions are included in the calculation. DAVERAGE() will return $1,560.02 as the average sales order value (refer to Figure below).

By using the DAVERAGE() function, you can effectively compare the average sales for products across different countries.