Handy Database Functions in Excel 2013

While working with spreadsheets, we frequently need to retrieve a specific value from large data sets. Sometimes, we need to filter out values on the basis of some criterion. And at other times, we wish to perform a simple arithmetic operation which can be as simple as calculating sum or finding average of a particular column. Today, we will learn the different database functions offered by the excel 2013 to help us to handle bulky spreadsheets effectively.

1-DAverage

As depicted by its name, DAverage computes average of any column in database. It has three arguments: database, field and criteria.

• Database is the entire spread sheet or you may select a part of it.
• Field is the column on which you will apply the average formula. You can refer to a particular column by specifying its number or its name (inside quotation marks).
• Criterion is the basis on which values will be extracted from database and their average will be calculated. It is the range of cells that contains your specified column name and the condition.

In our example, we have calculated the average of ‘Cost Ctr’ column of values in which ‘Cost Ctr’ is greater than zero.  You must be thinking that we can calculate the average by using the simple Average formula, why shall I get into complexity? Let’s consider for a moment, that you need to calculate the average of ‘Cost Ctr’ that was recorded in the Year 2013.  Can you do this with the traditional Average formula?

No! And, this is where DAverage function shows its significance. We mention the complete database so that we can calculate the average by filtering the values on the basis of any column included in the database. To achieve the above target, I can write Year as label and 2013 as condition in the criteria cell range.