1. Home
  2. More Nuggets

  3. Advanced Criteria

Advanced Criteria:

Specifying more advanced criteria

All of Excel's Database functions and some of the Statistical functions have a criteria argument that allows you to filter specific data for which the function performs it's calculation on. The criteria can be very simple or more complex. Here, we'll discuss some of the more advanced uses of the criteria argument.

#1)
Let's use the DAVERAGE function to illustrate:

First, our database:

The range B3:E17 represents our database.
#2)
Now let's talk about the field - the data we are interested in:
Later in the example, we will be seeking information about the age of fruit at the various warehouses. So, "Age" or cell E3 will be our field.
#3)
Now, let's specify the criteria and look at the results:
Specifying the criteria allows you to limit or filter the records (rows) that are included in a database function's calculation. Although criteria is a required part of the syntax of a database function, you can set it to "blank" so that all records in a database are included in the calculation.

Here we are setting the critera on the WarehouseID and Fruit data and we are limiting the records or rows to cherries. Since the WarehouseID criteria (cell B21) is blank, all warehouse's are included in the calculation.

Note that, in this example, the criteria is the range B20:C21. The criteria argument must include the column header (field name) in the database.
The criteria argument:
The result based on the above criteria:
The same example in edit mode:
Note:
This is a simple use of the criteria argument. We'll look at more advanced criteria below.
#3)
Using a more advanced critera:
Example 1:

Here, the structure of the function was not changed. We simply specified different criteria. In this case, we are limiting the data to all cherries in all Ellensburg warehouses.

Example 2:

Here, the criteria for Fruit("Cherries") is on a different line than the criteria for Location("Ellensburg").

When criteria is placed like this, we are using "OR" boolean logic. In other words, here we are saying we want the average age of all fruit that is either Cherries or in Ellensburg.

Example 3:

Here, the criteria for Fruit("Cherries") is on the same line than as the first criteria for Location("Ellensburg").

The second criteria for Location("Yakima") does not have have a corresponding criteria for Fruit.

So, we are averaging all inventory in Ellensburg that are cherries and all inventory in Yakima regardless if cherries or apples.

Example 4:

Here, the criteria for WarehouseID is "*E*. The asterisk * is a wild card character meaning that any WarehouseID that contains the letter "E" will be included in the calculation of the average.

Example 5:

Here, the Location and Fruit criteria are blank. We added 2 columns to the criteria, each for Age

The first Age criteria is >20 (greater than 20) and the second Age criteria is set to <30 (less than 30). So, we are averaging the age of all inventory at all warehouses for which the average age of the inventory is between 20 and 30.

Summary

All of Excel's Database functions and some of the Statistical functions have a criteria argument that allows you to filter specific data for which the function performs it's calculation on. The criteria can be very simple or more complex.