Microsoft Office Tutorials and References
In Depth Information
the cell functions in the Translation table can easily be changed to reflect the new
distribution—(
IF (K3<0.15, “Red”, IF (K3<0.52, “White”, “Blue”)). Note that
the second condition (K3<0.52) is the cumulative value of the first two probabil-
ities (0.15 + 0.37
=
0.52). If there are four possible discrete outcomes, then there
will be a third cumulative value in a third nested IF in the translation table.
4. We can use larger sample sizes, to achieve greater accuracy in outcomes. In
Exhibit 8.2 we also see a small table entitled Table for Various Sample Sizes .This
table collects samples of various sizes (10, 20, 30, 50, and 100 observations) to
show how the accuracy of an estimate of the population (the entire bowl) propor-
tions generally increases as sample size increases. For example, for sample size
10, cells B14:K14 form the sample. This represents the top row of the translation
table. As you can see, there are 3 red, 2 white, and 5 blue randomly selected col-
ors. If we use this sample of 10 observations to make a statement about our belief
about the distribution of colors, then we conclude that red is 30% (3/10), white
is 20% (2/10), and blue is 50% (5/10). This is close, but not the true population
color distribution.
What if we want a sample that will provide more accuracy; that is, a sample
that is larger? In the table, a sample of 20 is made up of observations in B14:K14
and B15:K15. Of course, for any one sample, there is no guarantee that a larger
sample will lead to greater precision, but if the samples are repeated and we
average the outcomes, it is generally true that the averages for larger samples
will converge to the population proportions of colors more quickly than smaller
samples. It should also be intuitively evident that more data (larger sample sizes)
leads to more information regarding our population proportions of colors. At one
extreme, consider a sample that includes the entire population of 1 million col-
orful stones. The sample estimates of such a sample would estimate population
proportions exactly—30% red, 30% white, and 40% blue. Under these extreme
circumstances, we no longer have a sample; we now have a census of the entire
population.
Note how the sample proportions in our example generally improve as sample
size increases, although the sample size of 50 yields proportion estimates that are
less accurate than the sample size of 30. This can and does occur, but in general,
we will see better estimates with higher sample sizes. The sample size of 100
results in the exact values of the color proportions. Another sample of 100 might
not lead to such results, but you can be assured that a sample size of 100 is
usually better than a sample of 10, 20, 30, or 50.
To generate 100 new values of RAND() we recalculate the spreadsheet by
pressing the F9 function key on your keyboard. This procedure generates new
RAND() values each time F9 is depressed. Alternatively, you can use Calculation
Group in the Formulas Ribbon. In Options, a tab entitled Calculation permits you
to recalculate and also control the automatic recalculation of the spreadsheet. See
Exhibit 8.3. You will find that each time a value or formula is placed in a cell
location, all RAND() cell formulas will be recalculated if the Automatic button is
selected in the Calculation Options subgroup. As you are developing models, it
is generally wise to set Calculation to Manual. This eliminates the repeated, and
=
Search JabSto ::




Custom Search