Unit 3.3: Advanced Formulas & Data Management

--:-- --
↓ Scroll for more

Unit 3.3

Advanced Formulas & Data

PRC 110: Software Skills Practicum

📖 Read the full notes: MS Excel: Data Management Basics · MS Excel: Linking Workbooks and Worksheets

Learning Objectives

  • Use IF (and nested IF).
  • Use VLOOKUP / INDEX-MATCH.
  • Sort and Filter data.

The IF Function

`=IF(Condition, Value_if_True, Value_if_False)`

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

Nested: `=IF(D2>25000,"High",IF(D2>10000,"Medium","Low"))`

Check: IF Logic

Ask a student or group first — then click to check.

VLOOKUP

`=VLOOKUP(Lookup_Value, Table_Array, Col_Index, FALSE)`

  • Use FALSE / 0 for exact match on names/IDs.
  • INDEX + MATCH is more flexible when columns move.

Sorting & Filtering

Sort

Data > Sort — single or multi-level.

Filter

Data > Filter — show only matching rows.

Check: Lookup & Filter

Ask a student or group first — then click to check.

Practicum 3.3

Data analysis

Use sales_data_large.csv:

  1. IF “Target Met?” if Sales > 5000.
  2. VLOOKUP / INDEX-MATCH a product.
  3. Filter a region; sort by Sales Amount.