Useful Excel Functions
Health Economics Modeling Workshop

Useful Excel Functions

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 figure above, we see that cells in the transition probability matrix are listed in terms of the parameter names, not the actual values. For example, looking at the Transition Matrix, we see that the cell for the transition from “Healthy” to “Sick” has value p_HS. In the parameter table, p_HS has a value of 0.14. How can we fill in the cell with 0.14 rather than “p_HS”?

VLOOKUP

The easiest way to query a parameter table is using the excel command VLOOKUP(). The basic syntax is

  1. What you want to look up in the table. In this case we want to look up the value associated with “p_HS”.
  2. The “table” or region of cells where you are looking up a value. This table MUST have as its first column the text elements that you are looking up. So in this case, the first column must have somewhere where Excel can find “p_HS.”
  3. The “column” number in that table where the actual value is.
  4. An optional value–which we highly recommend setting as “0”--that forces an exact match.

In the example above, the “table” we’ll specify has blue highlighing (i.e., cells B3:C5). And within that “table,” the first column lists the parameter name, and the 3rd column (“Baseline”) lists the value. So our VLOOOKUP() command looks like this:

VLOOKUP("p_HS",B3:D5,3,0)

In Excel, our formula looks like this:

And after we hit enter, we see that Excel has “found” the value of p_HS and inputted it in the cell:

MMULT

Our next objective is to use matrix multiplication to construct a Markov trace based on the full transition probability matrix. We start with a “blank” Markov trace (0th cycle) in which the entire cohort starts healthy:

To obtain state occupancy for any given cycle, we perform matrix multiplication between a vector of state occupancies at the beginning of the cycle and the transition probability matrix. This is done using the MMULT() command.

Suppose you have a 1×3 matrix A and a 3×3 matrix P. Matrix multiplication of A and P is accomplished in Excel using the following:

MMULT(A,B)

In our Excel example, A is defined as the state occupancy at the beginning of the cycle, i.e., a matrix defind by cells H11:J11. P is the transition probability matrix defined by cells I3:K5:

The result is shown below:

TRANSPOSE

You may need to transpose a matrix before you can perform matrix multiplication. In that event you can use the function TRANSPOSE():