Compare Best/Base/Worst Scenarios

All versionsWhat-If

Lay your assumptions in columns — worst, base, best — and let one model read whichever scenario is selected. Swap a single selector cell to flip the whole forecast, no Scenario Manager required.

Quick formula: with assumptions in columns and a selector in B1:

=CHOOSE($B$1, worstValue, baseValue, bestValue)

CHOOSE picks the assumption for the active scenario (1/2/3). Point every model input at its CHOOSE and the whole sheet switches at once.

Functions used (tap for the full reference guide):

CHOOSE

The example

Revenue under three growth assumptions.

	A	B	C	D
1	Assumption	Worst	Base	Best
2	Growth	2%	5%	9%
3	Projected (base)		$105,000

The formula

One selector drives the model:

=CHOOSE($B$1, 0.02, 0.05, 0.09) // active growth =base * (1 + activeGrowth) // projection // flip B1 to switch scenarios

How it works

A selector plus CHOOSE switches assumptions:

Lay each scenario’s assumptions in columns (worst / base / best).
Add a selector cell holding 1, 2, or 3 (a dropdown is nice).
Each model input reads =CHOOSE(selector, worst, base, best) to grab the active scenario’s value.
Change the selector and every dependent result updates — an instant scenario switch.

Built-in alternative: Data → What-If → Scenario Manager stores named scenarios and can produce a summary report. The CHOOSE method is more transparent and updates live; Scenario Manager is handy when you want saved, named sets you can audit later.

Try it: interactive demo

Live demo

Pick a scenario; see the projection.

Base revenue

Scenario

Projected:

Variations

Dropdown selector

Data Validation list 1-3 (or names + MATCH).

By name

Selector = text; use MATCH:

=INDEX(row, MATCH($B$1, names, 0))

Scenario Manager

Data → What-If → Scenario Manager → Summary.

Pitfalls & errors

CHOOSE index is 1-based. The selector must be 1, 2, 3 — a 0 or 4 returns #VALUE!.

Lock the selector reference. Use $B$1 so every input reads the same selector.

Keep assumptions separate from formulas. Store the scenario values as inputs, not buried in formulas, so they’re easy to change and audit.

Practice workbook

📊

Download the free Compare Best/Base/Worst Scenarios practice workbook

A worst/base/best model driven by CHOOSE + selector, the name-based and dropdown variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I compare scenarios in Excel?

Lay assumptions in worst/base/best columns, add a selector cell (1/2/3), and have each input read =CHOOSE(selector, worst, base, best). Changing the selector switches the whole model.

Should I use CHOOSE or Scenario Manager?

CHOOSE is transparent and updates live; Scenario Manager (Data → What-If) stores named scenarios and can generate a summary report. Use whichever fits your audit needs.

Why does CHOOSE return an error?

CHOOSE is 1-based — the selector must be 1, 2, or 3. A 0 or out-of-range index returns #VALUE!.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: CHOOSE