Database Functions Part 2: 12 formulas that cover all common calculation needs
If you are still unsure about what Dfunctions are and how to use them, click here to be directed to Part 1 of Database functions.
All examples below are based on a sample Excel spreadsheet, click here to download it.
There are 12 basic database functions. In this article, we will list them, explain briefly what they are and illustrate its uses with examples.
This function calculates the average of selected entries (in this article we will call “selected entries” the fields in the database with specified conditions).
There are many cases where this function can be used: From the average salary for a specific department of a company to the average age of people pertaining to a certain group.
In our example, we are looking for the average salary of people that got hired after 2006.
Criteria: E3:E4 (for more details on how the criteria argument works, refer to Part 1)
- Column header: “Date hired”
- Value: >01.01.2006
DCOUNT and DCOUNTA
These functions are very similar in the way they work as they both count the number of cells containing the entries. There is a slight difference, however, DCOUNTA will only include cells with a value in it (whatever the value is) and ignore if the cell is blank, whereas DCOUNT includes all cells, with a numerical value only.
These two formulas are very common and can be used in many cases: If you want to know the number of people that belong to a specific category, are under a certain age, or were hired after a certain date etc.
To refer back to our example, we can calculate the number of women working in the company that are under 40 years old.
Field: BLANK (note that for DCOUNT and DCOUNTA, the field is not necessary and can be left blank)
- Column header: “Gender” and “Age”
- Value: F and <40
This function allows the user to extract a single value that matches the criteria. Note that like all the other database functions, if no records match the criteria, the result will show up as #VALUE!. However, unlike the others, DGET will return #NUM! if more than one record matches the criteria.
This formula can therefore only be used when only one result will show up, for example, if you are looking to find information relating to a specific code or ID.
In our example, we are wanting to find the Category Leda belongs to.
- Column header: “Name”
- Value: Leda * (adding a star to the criteria value allows the formula to search amongst the “Name” field all values starting with Leda)
DMIN and DMAX
These two formulas return the minimum value and maximum value of selected entry.
They are commonly used and can work in many different cases: you can use it to find the person with the highest salary from a certain group, or the youngest person that started after 2008.
In our example, we are looking at finding the eldest person amongst group A and C combined.
- Column header: “Cat”
- Value: A and C
The product formula multiplies the values of the selected entries.
It can be used to calculate total scores or multiply costs for example.
In our case, the DPRODUCT is not applicable. For example, if you were interested in gathering data around Salary or Age of a particular Category or Gender and Hired date, a multiplication will not provide any relevant information as it would multiply the entries amongst each other, which would give a meaningless result.
The DPRODUCT function is actually a one of a kind as it is quite rare to find relevant examples of using it in.
For the sake of this article, we have created another example to demonstrate which cases it can be used in. In this case we want to know the total cost of sending our stock of Product A
- Column header: “Product”
- Value: Product A
Syntax: =DPRODUCT(A10:C16,” Cost/Carton”, A3:A4)
DSTDEV and DSTDEVP
Calculates the standard deviation of the entries
Calculates standard deviation based on the whole population of the database
Standard Deviation (DSTDEV) and Standard Deviation P (DSTDEVP) compare the database against the average value of the field. For example, lower the value of standard deviation, the closer the database is to the average of the field. Similarly, higher value means that the figures of the database are spread out and not close to the average. The difference between DSTDEV and DSTDEVP is that the former uses figures from the database that matches the criteria in comparison to DSTDEVP which uses the whole database.
These two formulas can be used in any context, for example, if you want to know the age difference in a certain group or the salary gaps amongst females in a company.
In our example, we are trying to find out the standard deviation of the age of all males.
- Column header: “Gender”
- Value: M
This formula simply adds the values of selected entries.
It is a very simple formula however it can be extremely useful in many cases: Finding out a total cost for a certain category or total profit for a certain region.
In our example, we want to know the total salary spend for category B only.
- Column header: “Cat”
- Value: B
DVAR and DVARP
Variance (DVAR) and Variance P (DVARP) calculates the variances, which is the Standard Deviation of the database squared. Similar to DSTDEV and DSTDEVP, DVAR uses the values from the database that satisfies the criteria whereas DVARP uses the complete database.
Also similar to the standard deviation, the variance is used to calculate the entries values to the average.
For our example, we will then find out the variation of the age of all males like we did with the standard deviation
- Column header: “Gender”
- Value: M
If you found this article useful, let us know! We love feedback. Also If you have any question or remark, do not hesitate to leave a comment down below or send us an email at [email protected] or [email protected] .
You can also download our Database functions cheat sheet by entering your email address below! Keep this cheat sheet on your desk and always be reminded of how to use Excel’s powerful Database functions!
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018