Practice 5: Functions & Lookup Logic

Key Topics & Core Concepts

Statistical Functions: Computing summary metrics using MEDIAN, MODE.SNGL, COUNT, and COUNTA.
Logical Decisions (IF): Building single and nested decision tests with conditional outcome strings (VIP, Goal Met).
Financial Modeling (PMT): Calculating loan payments with type flags (0 for end of period vs. 1 for beginning of period).
Lookup Logic (XLOOKUP): Retrieving values across sheets using exact match lookups without rigid column indexing.
Volatile Date Functions: Inserting current system timestamps using =TODAY().

Assignment Overview & Deliverables

In this practice exercise, you will apply statistical aggregations, loan payment modeling, conditional logic, and modern XLOOKUP formulas to the Perfect Vacation Rentals dataset.

Starter Workbook: Download PerfectRentals-02.xlsx (or continue with your completed file from Practice 4)
Completed File Name: [your initials] Practice02_Week3.xlsx
Active Worksheets: Rates, Rental Days, P&L, Guest Data, Bicycles, and Start-Up

Step-by-Step Instructions

Step 1: Open Workbook & Calculate Statistics (Rates Sheet)

Open PerfectRentals-02.xlsx and save it as [your initials] Practice02_Week3.xlsx.
Click the Rates tab. In cell B15, type =MEDIAN(B4:B13) to find the middle rental rate.
In cell B16, calculate the most common rate using =MODE.SNGL(B4:B13).
In cell B17, count the number of numeric rate entries using =COUNT(B4:B13).

Step 2: Non-Empty Counts (Rental Days Sheet)

Click the Rental Days tab.
In the summary area, use =COUNTA(A4:A25) to count the total registered property locations (counting non-empty text cells).

Step 3: Logical IF Statements (P&L and Guest Data)

Click the P&L tab. Locate the Performance Evaluation cell.
Enter an IF test to evaluate whether profit margin meets the 25% target: =IF(Profit_Margin>=0.25, "Goal Met", "Goal Unmet").
Click the Guest Data tab. In column D (Loyalty Tier), check if guest nights exceed 5: =IF(C4>5, "VIP", "Not yet") and copy the formula down.

Step 4: Loan Amortization Model (Bicycles Sheet)

Click the Bicycles tab to review the equipment loan terms.
Click cell B7 (Monthly Payment). Type: =PMT(B6/12, B5*12, B4, , 1) and press Enter.
Verify that setting the type argument to 1 reflects payments due at the beginning of each period.

Step 5: Modern Lookups & Date Timestamp (Start-Up Sheet)

Click the Start-Up tab.
In cell B4, retrieve property category codes from the Rates sheet using XLOOKUP: =XLOOKUP(A4, Rates!$H$4:$H$14, Rates!$G$4:$G$14). Copy the formula down through cell B14.
In cell A23, insert the volatile current date function: =TODAY(). Format as Short Date.
Save the completed workbook.

Practice exercises are self-paced and designed to prepare you for graded assignments.

Key Skills Evaluated

Skill Area	Verification Target
Statistical Summary	`MEDIAN`, `MODE.SNGL`, and `COUNT` vs. `COUNTA` properly utilized.
Conditional Testing	`IF` statements correctly formatted with threshold comparisons and text outcomes.
Financial PMT	`=PMT(rate/12, nper*12, pv, , 1)` configured with beginning-of-period cash flow.
XLOOKUP Search	Exact-match lookup configured across sheets without obsolete index offset math.