Excel Tip of the Week #109 - Selecting a scenario

Hello and welcome back to the Excel Tip of the Week!  This week, we have a Developer post in which we analyse various ways of solving a particular problem in modelling - switching a set of inputs between multiple different options for analysis.

There are any number of ways of going about this, but I am going to talk about six, with a comparison of the strengths and weaknesses of each.  In practice, the one that's right for you will depend on your exact situation and what you value more.

CHOOSE

(covered in ITCounts back in TOTW #96)

Overview of approach: CHOOSE can use an index number to choose between numbered sets of inputs.  By having the scenario chosen from a dropdown list, a MATCH function can be used to create an appropriate index number.

Strengths: Directly refers to the cells where the values are taken from, making it clearer to review.

Weaknessess: Doesn't scale well to large numbers of scenarios, as the formula must be pointed at each successive scenario in turn.

OFFSET

(to be covered in a future Tip)

Overview of approach: Like CHOOSE, OFFSET uses an index number to move a given starting cell a certain distance to the desired endpoint.

Strengths: Easily extendable to any number of different scenarios.

Weaknesses: Doesn't in any way refer to the cells used, which can make formulas hard to trace & audit.

INDEX MATCH

(covered in TOTW #50)

Overview of approach: INDEX can be used to pick a value from a list according to a given position.  This uses an index number like the last two examples.

Strengths: Extensible to any number of scenarios and directly refers to the cells used, increasing flexibility and reviewability.

Weaknesses: None to speak of.

INDIRECT / ADDRESS

(covered in TOTW #34)

Overview of approach: ADDRESS can be used to synthesise a cell reference out of a sheet name and a row and column number.  INDIRECT then allows that synthesised reference to be used in a formula.

​Strengths​: Avoids cross-sheet references in formulas, as the name of any inputs sheet is used only as an input to the formula itself.  Can also allow for changing worksheets dynamically.

​Weaknesses​: Can be complex and difficult to follow.

Named ranges

(covered in TOTW #95)

Overview of approach: A named range can be created automatically from a range, with labelling following the way the range is named on the sheet, using Formulas => Create from Selection.  These names can then be plugged directly into an INDIRECT formula to pull the value out.

Strengths: Simple formula and natural-language name structure.

Weaknesses: Requires use of e.g. underscores instead of spaces.  Simplest version only works if columns of inputs are the same as the columns of the formula.

Scenario Manager

(covered in TOTW #84)

Overview of approach: The inputs can, instead of being entered onto a sheet, be plugged in to the Scenario Manager, Excel's built-in tool for scenario management.

Strengths: Built-in ability to combine scenarios from multiple locations; simple comparison tool to produce comparative information.

Weaknesses: Input process is time-consuming; switching between scenarios is more involved.

Check out the attached file for demonstrations of all six, and add any personal twists on the problem in the comments!

Previous post 
TOTW index
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

Choosing inputs.xlsx
Anonymous