Page 465 - Excel for Scientists and Engineers: Numerical Methods

442 EXCEL: NUMERICAL METHODS

Another approach has been to use the Monte Carlo method, illustrated on the
sheet "Method 2."

5. You can't use the expression IFRAND(), since this has the possibility of
returning the same number more than once. The same is true of the
expression RANDBETWEEN(1,15). The same deficiency occurs with the
Sampling Tool in Tools+Data Analysis.. .. If you specify, for example, five
random numbers from the list of integers 1, 2, 3, 4, 5 you could get the result
2,5,2, 1,3.
The only way to do this (that I can think of) is to create a two-column table
with the integers 1-15 and 15 random numbers using RAND() and sort the
table manually in ascending or descending order, in the same was as the
example shown in Figure 15-2. You can also sort the list by using a formula,
as shown in Figure 15-5.

6. This problem is similar to the previous one (you could just create a list of the
integers 1-52 in random order), except that it opens up the possibility of
displaying the 52 values as numbers 1-13 in the four suits: clubs, diamonds,
hearts, spades. The workbook shows several ways to display the results.
The symbols for the four suits are in the Symbol font; Conditional
Formatting was used to provide the red color for the diamonds and hearts.

7. This workbook requires the RANDBETWEEN worksheet function. Some code
has been provided so that if the Analysis ToolPak is not loaded, a Sub
procedure in the sheet Thisworkbook loads the Add-In.

8. The surprising result of this simulation shows that about 30% of all numbers
obtained from real numerical data start with the digit 1. This has been
termed Benford's Law.
Newcomb (1881) observed that the first pages of tables of logarithms were
more worn and dirty than later pages, suggesting that numbers with a low
first digit occurred in calculations more often than ones with a high first
digit. (The counter-argument, of course, is that people start at the beginning
of the table and page through until they reach the page they need.)
Benford (1938) determined the distributions of leading digits in data sets
taken from a wide variety of sources, including molecular weights of
compounds, surface area of rivers, and street addresses. He found the
following distribution: 1, 30.6%; 2, 18.5%; 3, 12.4%; 4, 9.4%; 5, 8.0%; 6,
6.4%; 7, 5.1%; 8, 4.9%; 9, 4.7%
Hill (1996) showed that, for a variety of statistical data, the first digit is D
with the probability loglo (l+l/D).
Benford's law is more than a numerical curiosity; it has practical applications
for the design of computers and for detection of fraudulent data. Benford's
law was used as a plot device in the episode, "The Running Man" (2006), of
the CBS television crime drama NUMB3RS.