Week 9 summary

20 Oct 2011

The last few weeks we completed a bunch of examples in Excel.
Completed versions are here:

The final simulation example used some of the "What-if analysis" features in Excel. This page goes through the steps of setting up a table for the experiment. A brief outline of the steps goes like:

  • Create a model for a single iteration of the experiment, including a formula that gives your final answer.
  • Create a table that contains rows for each iteration of your experiment, and columns for each test value. Here is a small example for 5 iterations and 3 test values:


  • Insert a reference to your final answer formula in the top left corner (the cell marked X above).

  • Highlight the full table and choose "Data table" from the What-if analysis menu.
  • Insert a reference to the test value for the row input cell, and insert a dummy value for the column input cell by selecting a blank cell.
  • Excel will then fill in the table. If it's big, it could be slow, and you might want to disable automatic calculation in the Formulas/Calculation Options menu.