# Computer Assignments and Projects Assignments | Online Homework Help

Computer Assignment 4

Generating Random Numbers using EXCEL

Verifying the Empirical Rule using EXCEL Functions

Generate random numbers using EXCEL

Refer to the INSTRUCTIONS FOR COMPUTER ASSIGNMENT 4 FILE AND

REFER TO:

GENERATING RANDOM NUMBERS FROM A NORMAL DISTRIBUTION

(1) Follow the steps and generate 1000 random numbers using Normal

Distribution

(2) Construct a histogram of the random numbers you generated.

The normal distribution is a symmetrical distribution, therefore, your

the histogram will have a symmetrical or bell-shaped appearance.

Verify the Empirical Rule using the random numbers you generated using

EXCEL functions.

REFER TO THE SECTION: VERIFYING THE EMPIRICAL RULE AND

(3) Follow steps 1]7 to verify the empirical rule.

(4) Do not print all the random numbers. Print only the part of data the

result, and your histogram. (See Figure 3B.5 on page 242. Donft

forget to include your histogram).

(5) Generate 3000 random numbers and repeat the steps to verify the

empirical rule. Construct a histogram of the random numbers and

show the part of the data sheet with your result and histogram.

(6) Generate 5000 random numbers and repeat the steps to verify the

empirical rule. Construct a histogram of the random numbers and

show the part of the data sheet with your result and histogram.

Turn]in the histograms for the random numbers generated and three

EXCEL sheets one each for set of 1000, 3000, and 5000 random

numbers. Your EXCEL sheets will be similar to Figure 3B.5 in the text

with a histogram. Write a brief report and discuss your

understanding of the empirical rule.

Note: Everyonefs numbers will be different because each time you generate

random numbers; you get a different set of numbers.

Turn]in your assignment in class (no e]mail attachments). Include a

cover sheet with your name, day, and time of your class.

INSTRUCTIONS FOR COMPUTER ASSIGNMENT 4

Generating Random Numbers from Normal Distribution and Verifying the Empirical

Rule using Excel

In this exercise, we will generate random numbers from a Normal or bell]shaped distribution. Since the normal

distribution is symmetrical, the empirical rule will apply. Recall that the empirical rule states that whenever

the data are symmetrical or bell]shaped,

. approximately 68% of all observations will lie between the mean and } 1 Standard Deviation. approximately 95% of all observations will lie between the mean and } 2 Standard Deviation. approximately 99.7% of all observations will lie between the mean and } 3 Standard Deviation

We will check to see if the empirical rule holds for the normal or symmetrical distribution by generating 1000

random numbers from a normal distribution.

GENERATING RANDOM NUMBERS FROM A NORMAL DISTRIBUTION

To generate 1000 random numbers from a Normal or symmetrical distribution, follow the steps below.

Steps:

1. From the Excel main menu select

Data. Data Analysis. Random Number Generation

Complete the Random Number Generation dialog as shown below

Number of variables 1

Number of Random Numbers 1000

Distribution Normal (select from the drop-down menu)

Mean = 80

Standard deviation = 5

Output Range $A$2 or click on cell A2

Click OK

This will generate 1000 random numbers from a Normal distribution with mean equal to 80 and standard

deviation equal to 5.

2. Label Colum A as Random Numbers.

3. Round the random numbers generated to two decimal places (for ease of calculations).

4. Construct a histogram of the random numbers to verify that the shape is symmetrical (this step is optional).

The histogram, of course, will display asymmetrical shape as the random numbers were generated using

a normal or bell]shaped distribution. The histogram constructed from the random numbers is shown in

Figure 3B.4.

Next, we will use the random numbers generated to verify the empirical rule.

65 70 75 80 85 90 95

90

80

70

60

50

40

30

20

10

0

Random Numbers

Frequency

Histogram of Random Numbers

Figure 3B.4: Histogram of 1000 Random Numbers from a Normal Distribution

(7) VERIFYING THE EMPIRICAL RULE

In this section, we will use the 1000 random numbers generated to verify the empirical rule. If the empirical

the rule holds, approximately 68% or 680 of the 1000 random numbers will fall within } 1 standard deviation of

mean, approximately 95% or 950 of the 1000 random numbers generated will fall within } 2 standard

deviation of the mean and approximately all of the 1000 random numbers will be within }3 standard deviation

of the mean. Follow the steps below to verify the empirical rule.

Steps:

1. Name the range: Naming the range is a very convenient way of addressing the range of values in a column

while writing the functions. Once the range of values in a column is named, it is not necessary to drag the

cursor from the first to the last value in the cell to specify the range. The range can simply be addressed by

selecting its name. This is particularly desirable if a cell has hundreds or even thousands of numbers. To

name the range of the values in a cell,

. Right-click the label of the column you want to name and select Define Name

(in our case, column A should have Random Number as the label)

. In the New Name dialog box that is displayed, Random_Numbers should be displayed in the Name

box

. Select Sheet1 from the dropdown menu for Scope

. In the Refers to: box, select the range A2: A1001 by clicking the button at the end of the box then

placing the cursor on the first value in cell A2 and dragging it down to A1001

Click OK

The above steps will name the column A. The range in this column can now be referenced by Random

Numbers.

For the steps below, refer to Figure 3B.5

2. Label columns B, C, D as shown in Figure 3B.5. These columns are named as Mean & 1 StdDev, Mean &

2 StdDev, and Mean & 3 StdDev. The columns will store the number of values within 1, 2, and 3 standard

deviations (from the 1000 random numbers generated and stored in column A).

3. Name the cells F2 and F3 as mean and standard deviation. In cell G2, type the functions

=AVERAGE(Random_Numbers)

In cell G3, type the function

=STDEV(Random_Numbers)

This will calculate the mean and standard deviation of the random numbers in column A. Note that the

range in column A has been named Random_Number. As you start typing the name of this range, type the

first word of the range name and it will allow you to select it from the dropdown menu where the range

name is stored.

Figure 3B.5: Verifying the Empirical Rule

4. Label column F5, F6, F8, F9, F11, F12, and F14 through F16 as shown in Figure 3B.5.

5. Calculate the values within 1, 2, and 3 standard deviations of the mean using the calculated mean and

standard deviation in the previous step. To do this, type the following functions in cells indicated

below:

In cells G5 and G6, type

=G2]G3

= G2+G3

Note that in cells G2 and G3, the mean and standard deviation are stored. The above formulas will

calculate the values between the mean and } one standard deviation and store them in Cells G5 and

G6.

To calculate the values within the mean and } 2 and 3 standard deviations, type the following formulas

in the cells indicated:

In cells G8 and G9, type

=G2 . 2*G3

=G2 + 2*G3

In cells G11 and G12, type

=G2 . 3*G3

=G2 + 3*G3

6. Next, we will write the function to count the number of values between the mean and } 1, 2, and 3 standard

deviations in column A where 1000 random numbers are stored. To do this, type the following functions

in the cells indicated:

In cell B2, type the function

=SUMPRODUCT((Random_Numbers>=G5)*(Random_Numbers<=G6))

This will count the number of values between the mean and } one standard deviation from the random

numbers in Column A and store the number in cell B2. Note that your numbers will be different from what

is shown in cell B2 of Figure 3B.5 example, as your random numbers will be different. However, the result

should be close to 68%.

To calculate the number of values between the mean and }2 and 3 standard deviations, type the functions

below.

In cell C2 type

=SUMPRODUCT((Random_Numbers>=G8)*(Random_Numbers<=G9))

In cell D2 type

=SUMPRODUCT((Random_Numbers>=G11)*(Random_Numbers<=G12))

The above functions will count the number of values between }2 and 3 standard deviation of the mean and

store them in cells C2 and D2. Refer to cells B2, C2, and C3 for the values. Note that your numbers will be

different than what is shown in cells C2 and C3 because your random numbers will be different.

7. Finally, we calculate the percent of observations within }1, 2, and 3 standard deviations of the mean. To do

this, divide the numbers in cells B2, C2, and D2 by the total number of random numbers generated (in our

case, it is 1000). The percentages are calculated and stored in cells G14, G15, and G16. To calculate the

percentages, type the following formulas:

In cell G14 type, =B2/1000

In cell G15 type, =C2/1000

In cell G16 type, =D2/1000

The percentages are 69.1%, 95.3%, and 99.7% (0.691, 0.953, and 0.997). These values agree with the

empirical rule.