See LAB I and download the spreadsheet linked at the bottom of that page.


A "perfect" test is on that has zero chance of yielding a false-positive and zero chance of yielding a false-negative.

In our decision trees this shows up in the "do test, test says OK" and "do test, test says not OK" branches where the chance node on what happens has branches with probabilities of 0 and 1.


Revisiting the Generator

Polish off your own model or load this spreadsheet Generator-with-imperfect-tests2016.xlsx and look over its parts to be sure you understand how it modifies the model we built last week.

TO DO: Record a small amount of data in which you experiment with two variables (for example, 3 values each of P_FalseNeg, P_FalsePos, or 3 values each of payoff to generator runs well and 3 values of likelihood of this) and the decision results and expected value outcome for each and comment on your finding.


We've previously learned how to install a spinner to control a cell's value, how to divide the spinner value to get fractional numbers, how to draw, and how to get text message output based on the values in a model.

Now we'd like to add one skill: conditional formatting.


Open a spreadsheet. Create a spinner that will control a cell and allow you to vary the value from 0 to 10.
Now select that cell. Conditional Formatting > New Rule > Use a formula to determine which cells to format.


In this case we are going to implement the following logic: IF the value of this cell is less than or equal to 5 then set its fill color to green.

Read what it says in the dialog box above: "Format values where this formula is TRUE." Like all formulas we will start this one with an equals sign.

= C4 <= 5

And then we use the options below the formula to set the formatting we'd like to use when this formula is true.


There are other ways to do conditional formatting that you ought to investigate but that we won't talk about here.


We WILL take a look at these:


Suppose we would like to conditionally format a range of cells as follows: if the cell value is under 5 blue, over five red, and equal to five violet.

  1. select a range of ten or so cells
  2. Conditional Formatting > Highlight Cell Rules > Greater Than…
  3. 5 and red fill
  4. Conditional Formatting > Highlight Cell Rules > Less Than…
  5. 5 and blue fill
  6. Conditional Formatting > Highlight Cell Rules > Equal To…
  7. 5 and violet fill
  8. Type 1 in first cell, 2 in second and then autofill the rest of the range.

Simple Decision Tree with Color Coding

Create a simple decision tree to model the following situation (download XLSX here):

  • We can go have the party indoors or out.
  • It may be warm (.75) or it may be cold (.25).
  • If it is cold and outside we expect 20 people. Cold and indoors 50.
  • If it is warm and outside we expect 75 people. Warm and indoors, expect only 20.

TO DO: Use conditional formatting to jazz this up. For example, we might cause the cells around the branch that gets chosen to turn yellow as below. Another option would be to highlight all the values associated with a chosen branch. Be creative.




1. Open a new spreadsheet and adjust column and row sizes to yield a grid of square cells about 30 rows by N columns.
2. Use borders to sketch a simple tree in the range C5:L12
3. Merge the cells at the end of the tree branches: M5:M6, M7:M8, M9:M10, and M11:M12
4. Merge the three cells above the horizontal branches of the tree
5. Insert labels for model parameters and give parameter cells a fill color. I'm using "P_REALITY" "falpos" "falneg" and "problem" (for the cell containing the name of the reality we are testing for).
6. Create shapes for the Reality and Test chance nodes
7. Insert "dummy" values for parameters in column A and in cell K2. Type formulas in column B that divide the whole numbers in column A by 100 so we can turn the integers that will be controlled by spin buttons (eventually) into probabilities.
8. Give names to the cells holding the parameters.
9. Insert formulas for the branches coming off the Reality node. For the lower branch always use the formula $1-p$ where p is the upper branch probability. This way the model has integrity and things change together.
10. Enter formulas so that the branches coming off the Reality node are properly labeled. We are using a text formula that concatenates either "says" or "says NO" with the parameter that describes the "problem."
11. Next we will insert the probabilities for false positive and false negative branches coming off the Test nodes using a formula that refers to the parameter cells that hold these values.
12. And then put in the "1-p" formula for each of these.
13. And then formulas to label the branches coming off the T nodes.
14. Add the joint probability products at the right ends of the tree branches - $P(A {and} B)=P(A) \times P(B)$


15. Add spin controllers for each of the three numerical parameters. These should control the integer values in column A.
16. Copy the entire decision tree and paste a copy below the original. Create a second R node and move the node graphics around.
17. Set the right most values by formulas that refer to the original tree. Set the branch labels similarly. Set the T branch probabilities based on the sum of the downstream branches. And finally, set the 4 R branch probabilities by dividing the final branch joint probability by the T branch probability.

TO DO: Build the tool as above or download this spreadsheet.

Make three copies of the worksheet and label them 104, 105, and 106.

Use these to build model for problems 104, 105, 106.

TO DO: For at least one of these, do the experiments described above.

TO DO: Use the model to work on a problem from this week's problems.