11. Finding Information in the Database

With Excel's Table feature, we can use some terms regularly associated with Database functions. We set up a Criteria Range at the top of our worksheet. We executed a Criteria search with the Advanced Filter command, and noted that our Table then only displayed the records meeting our Criteria.

We also set up an Extract Area to perform the same Criteria search. This time, our selected records were copied to the Extract Area, while our List Area remained unchanged.

That allows us, if we wish, to manipulate our extracted records without making changes to our original list. The key to these operations are in naming the Criteria and Extract Ranges, using the names "criteria" and "extract". These words actually give Excel the contextual clue that it needs to perform these powerful steps.

As you may now know, there are 12 Database/Table Functions in Excel, all beginning with the letter D, for Database (such as, DAVERAGE, DMAX, DCOUNT). Most use the three arguments: Table Range, "Field Name", and Criteria Range.

With that in mind, we created a mini Criteria Area on an outer area of our worksheet, and used it in conjunction with the Database Functions DAVERAGE and DCOUNT.

Using DAVERAGE, we calculated the Average Age for Females, and the Average Age for Males. We can compare that to the TABLE TOTAL row provided by Table Design to have an internal check. Using DCOUNT, we calculated the number of those Reps on our list who had scored an A.

We also used two additional Functions, (not classic Database functions) related to the IF Function, SUMIF and COUNTIF. We used SUMIF to calculate the total YTD Sales for those that scored an A; we used COUNTIF to add the number of Females on our list.

Materials Covered: MS Excel - Project 5:
Creating, Sorting & Querying Lists, pp 380-398

Case 3: Working with Formulas and Functions:

• Follow the instructions to download your file, complete work on it, and then to upload for grading.
• In your journal, write a short note letting me know what you learned from this case. Thanks.