Arrays of Data in a Spreadsheet

Problem:

My data is in a 2-dimensional spreadsheet range, where the row corresponds to the state of one uncertainty and the column corresponds to the state of another. I'd like to use it in my DPL model, but I'd rather not retype it.

Solution:

1. Give the range a range name in Excel.
2. Convert the spreadsheet. If the range does not convert into a DPL array, it is probably not referenced anywhere else in the spreadsheet. Either turn off the option in the conversion dialog box that suppresses generation of unreferenced ranges ("Ignore unreferenced range names"), or set up a dummy reference in the spreadsheet.
3. You have two options for setting up your model. You can either have your whole model in DPL code, or you can create your model graphically in the Model window and include your converted spreadsheet.

   DPL Code

   If you would like to have your model in a DPL program, modify the data definition section to look like this:
array a =
{1, 2, 3; //from the spreadsheet
4, 5, 6;
7, 8, 9};
chance b.{3} = {.1, .2};
chance c.{3} | b =
{.1, .2},
{.3, .4},
{.2, .3} =
default: a[state(b)-1][state(c)-1];


   or, if chance c doesn't depend on b:
chance c.{3} = {.2,.3};
value c = a[state(b)-1][state(c)-1];


   This takes advantage of the fact that the state function returns a number associated with the state of the chance event. This number can be used as an index into the array. Remember, the first element in an array is [0][0] (not [1][1]), so you must subtract one from the state result.

   Influence Diagram

   You can also create your model in the influence diagram / decision tree window. Create chance nodes b and c assigning the proper probabilities to each branch. If c is dependent on b you will need an arrow from b to c. Create another node v, for value. This node is going to contain the value information. Add a black arrow from c to v and from b to v. In the data definitions, assign v to be:

   a[state(b)-1][state(c)-1];

   Make sure the program with the array is selected in Model | Links as a "DPL Program for Data Definitions".

Caveat:

You can't "don't gamble" b or c, since the state function needs to know which state a chance event is in on the current tree path. "Don't gamble" reduces the lottery for the chance event to a single state. Since this new state doesn't necessarily correspond to one of the original states, DPL does not assign a number to the state when the state function asks, which means that the expression containing the state function can't be evaluated. When it encounters this situation, DPL will give an error message and stop running the analysis. It does not use a default state number, since this would give misleading and potentially incorrect results. This means that you can't do an event sensitivity comparison on this program unless you go into the code / value definition and replace the state function with a number.