What-If Analysis and Specialized Functions

Treat weights and rates as parameters. Look up descriptions; Goal Seek and scenarios record experiments.

This week

Weeks 9–10 · Excel Ch. 6–7 · Excel Projects 9–11. Next: statistics and Copilot (lecture 9).

Objectives

After this lecture you should be able to
  • Separate inputs you change (parameters) from outputs you watch
  • Use Goal Seek or a one-variable data table to answer “what input gives this result?”
  • Record named scenarios instead of overwriting the only model
  • Join a code to a description with XLOOKUP (and know what VLOOKUP still does)

Models have parameters

Scientific method, spreadsheet edition
  • Hypothesis: “If the exam weight rises to 40%, more students fall below 70%”
  • Parameter cells: weights, tax rate, price, capacity — absolute references (lecture 3)
  • Formulas and IFs depend on those cells, not on typed copies of the rate
  • Output: count of Review, total cost, break-even units
  • Record what you changed; do not save over the only copy of the model (File → Save As or a Scenarios sheet)
Layout
Sheet Contains
Data Table of observations
Model Parameter cells + formulas that read the Table
Checks Hand-computed rows
Scenarios Notes: date, what changed, what happened
  • Yellow (or a named style) for parameter cells is a convention, not magic
  • Protect the sheet later if others will type only in yellow cells

Goal Seek, Data Tables, Scenarios, Solver

Goal Seek
  • Question: “What exam score makes weighted total = 0.70?”
  • Data → What-If → Goal Seek: set cell (output) to value, by changing (parameter)
  • Works on one input; the relationship should be something Excel can nudge
  • Always sanity-check: is that exam score possible (0–1 or 0–100)?
Data Tables
  • One-variable: a column of trial tax rates, a row (or column) of resulting totals
  • Two-variable: grid of two parameters (use sparingly; it is easy to misread)
  • Data Table is not an Excel Table (lecture 6) — unfortunate name
  • Must sit on the same sheet as the formula in older workbooks; follow the textbook’s layout
Scenario Manager
  • Named bundles of parameter values: Base, HeavyExam, QuizHeavy
  • Switch and compare; add a scenario summary sheet
  • Better than “I think I undo’d back to the original weights”
Solver (preview)
  • Several inputs, constraints (“weights sum to 1”, “hours ≥ 0”)
  • Overkill for this course’s first pass; know it exists for optimization-style questions
  • Placeholder: one demo if time (maximize average without any weight > 0.5)

Lookup functions

Join a code to a description
  • Data: section is A, B, C
  • Table Sections: code room meeting time
  • XLOOKUP([@section], Sections[code], Sections[room])
  • This is a join, the same idea as matching IDs — do not retype rooms by hand
XLOOKUP vs VLOOKUP
  XLOOKUP VLOOKUP
Lookup column Any Leftmost of the range
Insert columns Safer Breaks if you insert inside the range
Not found Custom if_not_found #N/A unless wrapped
Approximate match Explicit Easy to leave TRUE by accident
  • Textbook may still teach VLOOKUP; if you use it, FALSE (exact match) is the default you want for IDs
  • INDEX+MATCH is the older flexible pair; optional
Lookup failures
  • Extra spaces: A vs A
  • Number vs text IDs (lecture 1 types)
  • Duplicate keys: lookup returns the first match — is that the rule you wanted?
  • Approximate match on unsorted data: silent wrong room

Other specialized functions

Text and dates (you will need a few)
  • LEFT / RIGHT / TRIM / TEXTSPLIT — when two variables were stuffed in one column (lecture 2)
  • YEAR / MONTH / EOMONTH / NETWORKDAYS — only on real date serials
  • TEXT to display a date; the stored value should stay a date
  • Do not parse dates as text if Excel already stores them as dates
Logical lookups
  • IFNA / IFERROR around a lookup: show Missing section instead of #N/A
  • Teach IFERROR after you have seen the error — it can hide type bugs

Try this in Excel

Lab 9 — Goal Seek and scenarios on the gradebook
  1. Parameters: three weights that SUM to 1
  2. Output: COUNTIF of Review
  3. Scenario Manager: Base (0.3/0.3/0.4), ExamHeavy (0.2/0.2/0.6)
  4. Switch; record both Review counts on Scenarios
  5. Goal Seek: set one student’s weighted total to 0.70 by changing their exam cell (not the class weight)
  6. Write in Word: which parameter you would not Goal Seek in real life (ethics / fairness)
Lab 10 — XLOOKUP
  1. Small Sections Table: code, room, seats
  2. On Scores, Room = XLOOKUP([@section], …)
  3. Break it: a typo section code; handle with if_not_found
  4. Optional: Copilot drafts the XLOOKUP; you explain each argument in a comment
Discussion
  • Designate a note-taker
  • How is Goal Seek different from “I typed numbers until it looked right”?
  • If a lookup is #N/A, is that a formula error or a data-quality finding?

Takeaways

Carry these into stats and Copilot week
  • Parameters live in cells; experiments get names
  • Goal Seek / data tables / scenarios beat random typing
  • Lookups join tables; exact match and types matter
  • Next: describe a distribution (MEDIAN, STDEV) and use Copilot only inside the check loop
Placeholder notes for expansion
  • Two-variable data table screenshot from the textbook
  • Solver one-pager
  • XLOOKUP with two criteria (optional)