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

Background/Intro

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.

Skills

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.

Example

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.

Tests

Lab Part II BUILDING A TREE FLIPPER

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.

page revision: 114, last edited: 18 Feb 2016 23:54