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.

DAVERAGE

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.

Database: A10:F43

Field: “Salary”

Criteria: E3:E4 (for more details on how the criteria argument works, refer to Part 1)

  • Column header: “Date hired”
  • Value: >01.01.2006

Syntax: =DAVERAGE(A10:F43,”Salary”,E3:E4)

 

 

 

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.

Database: A10:F43

Field: BLANK (note that for DCOUNT and DCOUNTA, the field is not necessary and can be left blank)

Criteria: B3:D4

  • Column header: “Gender” and “Age”
  • Value: F and <40

Syntax: =DCOUNT(A10:F43,,B3:D4)

 

DGET

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.

Database: A10:F43

Field: “Cat”

Criteria: A3:A4

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

Syntax: =DGET(A10:F43,”Cat”,A3:A4)

 

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.

Database: A10:F43

Field: “Age”

Criteria: F3:F5

  • Column header: “Cat”
  • Value: A and C

Syntax: =DMAX(A10:F43,”Age”,F3:F5)

 

DPRODUCT

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

Database: A10:C16

Field: “Cost/Carton”

Criteria: A3:A4

  • 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.

Database: A10:F43

Field: “Age”

Criteria: D3:D4

  • Column header: “Gender”
  • Value: M

Syntax: =DSTDEV(A10:F43,”Age”,D3:D4)

 

DSUM

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.

Database: A10:F43

Field: “Salary”

Criteria: F3:F4

  • Column header: “Cat”
  • Value: B

Syntax: =DSUM(A10:F43,”Salary”,F3:F4)

 

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

Database: A10:F43

Field: “Age”

Criteria: D3:D4

  • Column header: “Gender”
  • Value: M

Syntax: =DVAR(A10:F43,”Age”,D3:D4)

 

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!

Get our Database Functions Cheat sheet and never forget how to use Database functions!


Enter your First Name & Email to get our database functions cheat sheet and subscribe to our Newsletter!

Powered by ConvertKit

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to ConvertKit ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Abdelrahman Abdou
Latest posts by Abdelrahman Abdou (see all)
It's only fair to share...Share on Facebook
Facebook
0Pin on Pinterest
Pinterest
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin