An Overview of Excel Functions and Tools for Decision Modeling
Introduction
This document is designed to provide some useful Excel functions for doing decision analyses.
The running example will be a simplified process of mapping parameter values listed in a parameter table to construct a transition probability matrix:
In the figures above, we see that the parameter table lists the transition probabilities we need to fill out the matrix.
Assigning Variable Names
Our first step is to assign each base case value its variable name. There are two ways to do this:
- Using the naming box in the upper left of the spreadsheet. This is useful for naming one item at a time.
- Batch naming by using a table selection.
Let’s first run through option 1. In the figure below, we will assign the name “variable1” to the value in cell C5. We do this using the naming box highlighted in the upper left. Simply replace “C5” there with what you want to call the value in that cell, i.e., “variable1.”
You can see this process in action in the video below.
Batch Assigning Variable Names
Often it is more efficient to assign groups of variables, rather than do it one by one. To do this we simply select the “table” we want to use, and then go to the Formulas tab and select Create from Selection.
Make sure the variable name you want to assign is either in the leftmost row, or the top row (see below). Also make sure the value you want assigned to that variable is in the second column, as shown in the figure.
Assuming you have structured your parameter table as above, and using the guidance above, when excel prompts you to “Create Names” you can tell it the variable names are in the left column:
Once you have done this, you can select the Name Manager on the Formulas ribbon to confirm the new names have been assigned, and to make changes.
You can edit, delete, or reassign the cell values/range in the Name Manager, too.
A video walkthrough of this process is shown below.
If your parameter names are organized as column headers, you can use the same process, as shown in the video below.
Assigning a Matrix Variable Name
It is also quite useful to assign ranges of cells to a single variable name. For example, you can assign a name to a transition probability matrix, as shown below.
Here is a video version of the above:
Another option is to use the Name Manager to assign a name to a cell region (see the video under the Constructing a Markov Trace section below).
Constructing a Matrix from Variables
With our variables defined we can now simply use these variables to populate a matrix or other cell, based entirely on using variable names. You can then name the matrix, as we did directly above.
This process allows you to define your variables once, and have them propagate throughout your Excel document. That way, if you want to change the value of a parameter, you only need to do it once: in the cell where the variable is defined.