Microsoft Office Tutorials and References
In Depth Information
Exhibit 8.2 RAND( ) function example
8.2 you can see two tables entitled Random Numbers Table and Translation of
Random Numbers to Outcomes . Each cell location in Random Numbers Table
has an equivalent location in the Translation of Random Numbers to Outcomes ;
for example, K15 is the equivalent of K3. Every cell in the translation table ref-
erences the random numbers produced by RAND() in the random number table.
An IF statement is used to compare the RAND( ) value in K3 with a set of values,
and based on the comparison, the IF() assigns a color to a sample. The formula
in cell K15 is—(
IF (K3<0.3, “Red”, IF (K3<0.6,“White”,“Blue”)). Thus, if the
K3 value is less than 0.3 Red is returned. If the value in K3 is greater than 0.3,
but less than 0.6, then White is returned. If neither of these conditions is satis-
fied, then Blue is returned. This logical function insures that 30% (0.3–0) of the
randomly selected values are red; 30% (0.6–0.3
=
=
0.3) are white, and the remain-
der (1.0–[0.3+0.3]
0.4) are blue. Since K3 is 0.2570, the first condition is met
and the value returned is the color Red . In the case of K2, the random value is
0.6080, and the value in the translation table is the last condition, Blue . Thus,
values of 0.6000 up to, but not including, 1 will cause the return of Blue . The
value 0.6080 meets this criterion. Incidentally, the cell colors in the Translation
table are produced with conditional cell formatting.
Thus, if the distribution that we want to model is Discrete, as in the colorful
stones example, we can simply partition the range of the RAND() proportionally,
and then use a logical IF to determine the outcome. For example, if the propor-
tion of Red, White, and Blue changes to 15%, 37%, and 48%, respectively, then
=
Search JabSto ::




Custom Search