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) |