--:-- --
↓ Scroll for more

Unit 3.3

Advanced Formulas & Data

PRC 110: Software Skills Practicum

🎯 Learning Objectives

  • ✅ Use **Logical Functions** (IF, AND).
  • ✅ Master **Lookup Functions** (VLOOKUP).
  • ✅ **Sort and Filter** large datasets.

🤔 The IF Function

Make decisions based on conditions.

`=IF(Condition, Value_if_True, Value_if_False)`

Example: Pass/Fail

`=IF(B2>=45, "Pass", "Fail")`

  • If cell B2 is greater than or equal to 45, show "Pass".
  • Otherwise, show "Fail".

🔍 VLOOKUP

Search for data in a table.

`=VLOOKUP(Lookup_Value, Table_Array, Col_Index, [Range_Lookup])`

  • **Lookup_Value:** What ID are you looking for?
  • **Table_Array:** Where is the data?
  • **Col_Index:** Which column has the answer? (1, 2, 3...)
  • **Range_Lookup:** False (0) for Exact Match.

📂 Sorting & Filtering

Sorting

Arrange data A-Z, Z-A, or Smallest-Largest.

  • Data Tab > Sort.
  • Custom Sort: Sort by Department, then by Name.

Filtering

Show only specific rows.

  • Data Tab > Filter (Funnel Icon).
  • Use checkboxes to hide/show data (e.g., Show only "Sales" department).

🧪 Practicum 3.3

Data Analysis

  1. Open the `sales_data.csv` (Download Sample) file.
  2. **IF Function:** Create a column "Target Met?". If Sales > 5000, "Yes", else "No".
  3. **VLOOKUP:** On a separate sheet, search for a Product ID and return its Price.
  4. **Filter:** Show only sales from the "East" region.
  5. **Sort:** Arrange the filtered list by Sales Amount (Largest to Smallest).