Question

Using, for example, the command “=IF(RAND()<0,4;1;0)” in Excel, simulate 40 different realizations with n = 60 and the true supporting rate to be 40%. In order to “freeze” the output generated by Excel, select the cells with all 0s and 1s values that have been randomly generated using the above mentioned command, copy them and paste them (using the paste special option) as values back on top of the cells. Then in each of the 40 different simulated “datasets” calculate Y, i.e. total number of 1s (voters). Finally create a histogram of your 40 values of Y .Calculate the mean,median,standart deviation and the mode....construct as well the frequency and discuss your findings.

Answer 1

SOLUTION:

Using the command "=IF(RAND()<0.4,1,0)", simulated 40 different realization each of n=60. Data consist of either 1 or 0.

Y is obtained by summing each column (i.e, 40 different realizations) which gives the total number of 1's in each column.

Y
25
23
20
30
13
24
28
19
28
28
25
26
19
21
27
27
30
22
28
29
21
26
27
33
22
22
17
18
23
17
21
23
26
24
28
25
29
25
27
18

Histogram

Steps of obtaining Histogram in Excel

1) Click on "Data". Then Click on " Data Analysis". Then Click " Histogram".

2) In the input range select "Y" data. In the Bin range select bin that you want. Bin is the interval in which you want your datapoint to be expressed in consecutive intervals such as 0-5,6-10, 11-15, etc.

BIN

15

20

25

30

3) Tick on "labels" if you included titles in the range and output range.

4) Select " Chart output". Then click "OK".

Output

BIN	Frequency
15	1
20	7
25	15
30	16
More	1

Descriptive Statistics

Steps to obtain descriptive statistics in Excel

1) Click on "Data". Then Click on " Data Analysis". Then Click " Descriptive Statistics".

2) In the input range select "Y" data. Click on "Labels".

3) Click on "Summary Statistics".

Output

Y
Mean	24.1
Standard Error	0.685752
Median	25
Mode	28
Standard Deviation	4.337079
Sample Variance	18.81026
Kurtosis	-0.17363
Skewness	-0.40499
Range	20
Minimum	13
Maximum	33
Sum	964
Count	40

Frequency

How to construct a frequency plot

1) Define Bin and interval in which you want your data "Y".

2)First, select one column. Then at the first cell of the selected column, {=FREQUENCY(A2:A41,B2:B6)} where A2:A41 is the column in which we have "Y" and B2:B6 we have BIN values. Then press, CTRL + SHIFT + ENTER.

3) We will get the frequency as follows

BIN	INTERVAL	FREQUENCY
15	Under 15	1
20	16-20	7
25	21-25	15
30	26-30	16
35	Over 30	1
		40

Frequency plot

Plot the frequency using Column Chart. Select the data Frequency and Interval.

Discussion

Frequency and Histogram look similar. The mean of the Y is 24.1 which gives the true supporting rate of 40 percent. If we calculate by hand in each realization to get a true supporting rate of 40 percent we want 24 1's out of 60. The mean, median and mode are different which imply data is not symmetry. From the histogram, also we can see the data is not symmetrical. It is negatively skewed. And Descriptive statistics gives skewness equal to -0.40499. The Standard Deviation is 4.337079.