Database functions: daverage, dcount, dcounta, dget

Assuming the following data, let's go through the excel database functions.

Year Quarter Profit Criteria
2011 1 1000 Quarter Profit
2012 1 2000 2 >2000
2011 2 1340
2012 2 3400 Quarter Profit
2011 3 2345 1 >1000
2012 3 4500
2011 4 6000 Quarter Profit
2012 4 10000 4 <>5000

 

 

Excel Function DAVERAGE

The DAVERAGE() function returns the average of values in a column of a database or range,

that satisfies a specified criteria.

The syntax of this function is as given below:

DAVERAGE(database or range, column or field, condition)

DAVERAGE () Formula
3823.125 =DAVERAGE(L3:N11,"Profit",O4:P5)
2000 =DAVERAGE(L3:N11,"Quarter",O7:P8)
8000 =DAVERAGE(L3:N11,"Profit",O10:P11)

 

 

Excel Function DCOUNT

The DCOUNT() function returns number of number of cells with numeric values from a database.

These values are fetched from a specified column based on a specified condition.

The Syntax of this function is given below:

DCOUNT(database,field,condition)

DCOUNT() Formula
1 =DCOUNT(L3:N11,"Profit",O4:P5)
1 =DCOUNT(L3:N11,"Profit",O7:P8)
2 =DCOUNT(L3:N11,"Profit",O10:P11)

 

 

Excel Function DCOUNTA

This function returns the count of non blank cells from a specified column of a database

This function is also based on some user specific criteria.

The syntax is as given below:

DCOUNTA(database,field,condition)

DCOUNTA() Formula
1 =DCOUNTA(L3:N11,"Quarter",O4:P5)
1 =DCOUNTA(L3:N11,"Profit",O7:P8)
2 =DCOUNTA(L3:N11,"Year",O10:P11)

 

 

Excel Function DGET

The DGET() function returns a value from a specific column of a database based on a specific criteria.

The syntax of the function is as follows:

DGET(database,field,condition)

DGET() Formula
3400 =DCOUNTA(L3:N11,"Quarter",O4:P5)
2000 =DCOUNTA(L3:N11,"Profit",O7:P8)

 

You can find similar Excel Questions and Answer hereunder

1) Millions thousands custom number formatting in Excel

2) Vba clear the contents of an entire sheet in Excel

3) database functions dmax dmin dsum dproduct

4) how to combine the content of two or more cells in Excel

5) Vba code to password protect workbook in Excel

6) What is activeX data object in Excel. How to use it? Here the basic explanations

7) How to concatenate strings in vba in Excel

8) Generate a list of unique values in Excel

9) Write to text file without quotes in vba in Excel

10) Import txt file in Excel

 

Here the previous and next chapter