Practice 17: Database Functions & Multi-Criteria Queries

Key Topics & Core Concepts

Database Function Syntax: Mastering the standard 3-argument syntax: FUNCTION(database, field, criteria).
Criteria Range Architecture: Constructing criteria tables with exact matching headers to evaluate complex Boolean AND/OR logic.
Conditional Statistical Aggregation: Using DAVERAGE, DCOUNTA, and DMAX to compute targeted metrics without filtering the underlying table.
Dynamic Analysis: Updating criteria block inputs to instantly recalculate statistical summaries across large datasets.

Assignment Overview & Deliverables

In this practice exercise, you will analyze property rental transactions by setting up structured criteria ranges and extracting summary statistics using Excel database functions.

Starter Workbook: Download PerfectRentals-06.xlsx
Completed File Name: [your initials] Practice17_DatabaseFunctions.xlsx
Active Worksheet: Rental Data

Step-by-Step Instructions

Step 1: Open Starter & Review Rental Data Table

Download and open PerfectRentals-06.xlsx in Excel.
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice17_DatabaseFunctions.xlsx.
Click the Rental Data worksheet tab.
Review the database range A3:I20 containing property records with headers in row 3.

Step 2: Construct Criteria Range

In cells K1:L1, set up criteria headers that match the database column labels exactly:
- K1: State
- L1: Bedrooms
In cell K2, enter: MI.
In cell L2, enter: 3.
Verify that criteria range K1:L2 defines an AND condition matching 3-bedroom rentals in Michigan.

Step 3: Calculate Conditional Average with DAVERAGE

Click cell K4 (Average Nightly Rate).
Type: =DAVERAGE(A3:I20, "Nightly Rate", K1:L2) (or reference the column index for Nightly Rate) and press Enter.
Format cell K4 as Currency ($#,##0.00).

Step 4: Count Matching Records with DCOUNTA

Click cell K7 (Matching Properties Count).
Type: =DCOUNTA(A3:I20, "Property ID", K1:L2) and press Enter.
Format cell K7 as General or Number with 0 decimal places.

Step 5: Determine Maximum Revenue with DMAX

Click cell K10 (Highest Annual Revenue).
Type: =DMAX(A3:I20, "Total Revenue", K1:L2) and press Enter.
Format cell K10 as Currency ($#,##0.00).
Auto-fit columns K and L to prevent text truncation.
Save your completed workbook.

Practice exercises are formative and prepare you for homework submissions. Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area	Verification Target
Criteria Range Setup	Structured criteria range `K1:L2` matching database headers with exact field criteria.
DAVERAGE Calculation	`=DAVERAGE(A3:I20, ...)` correctly targeting criteria range.
DCOUNTA Calculation	`=DCOUNTA(A3:I20, ...)` correctly enumerating matching non-blank records.
DMAX Calculation	`=DMAX(A3:I20, ...)` identifying maximum field value matching criteria.