Database functions in Excel 2007
|Database functions in Excel are
analytical and calculation tools.
The functions take 3 arguments or parameters and then execute the result.
The 3 parameters are:
Some of the database functions are described below:
DAVERAGE Returns the average of selected database entries.
DCOUNT Counts the cells that contain numbers in a database.
DCOUNTA Counts nonblank cells in a database.
DGET Extracts from a database a single record that matches the specified criteria.
DMAX Returns the maximum value from selected database entries.
DMIN Returns the minimum value from selected database entries.
DPRODUCT Multiplies the values in a particular field of records that match the criteria in a database.
DSTDEV Estimates the standard deviation based on a sample of selected database entries DSTDEVP Calculates the standard deviation based on the entire population of selected database entries.
DSUM Adds the numbers in the field column of records in the database that match the criteria.
DVAR Estimates variance based on a sample from selected database entries.
DVARP Calculates variance based on the entire population of selected database entries.
Select the labels of the columns, copy and paste them 3-4 rows below your data or 3-4 columns away from your data.
Now specify your criteria like 'Manager' under the designation label and or some salary value under the salary label as shown in our example.
Next click inside a cell where you wish to get your reults. Type the '=dsum(.......)' formula by providing the arguments as shown in the video. The first argument is your data. Select the data range including the labels. You can also assign a 'name' to the range like 'mydata' and use it.
Then define the 'field' using the column label like 'Salary'.
The final step is to define the criteria range which includes the headers that you copied and pasted and the criteria values. After closing the bracket press enter and the result will be displayed.
We have demonstrated how to use the database functions like DSUM, DAVERAGE,DMAX, etc.