Microsoft Office Tutorials and References

In Depth Information

the cell functions in the
Translation
table can easily be changed to reﬂect 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 ﬁrst 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 ﬁnd 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