Question

If a fair coin is tossed 25 times, the probability distribution for the number of heads, X, is given below. Find the mean and the standard deviation of the probability distribution using Excel

• Enter the mean and round the standard deviation to two decimal places.

x   P(x)
0   0
1   0
2   0
3   0.0001
4   0.0004
5   0.0016
6   0.0053
7   0.0143
8   0.0322
9   0.0609
10   0.0974
11   0.1328
12   0.155
13   0.155
14   0.1328
15   0.0974
16   0.0609
17   0.0322
18   0.0143
19   0.0053
20   0.0016
21   0.0004
22   0.0001
23   0
24   0
25   0

Answer 1

Given that a fair coin is tossed 25 times, the probability distribution for the number of heads X is given.

Here we need to find the mean and the standard deviation using Excel.

Now before we go on to solve the problem using Excel let us know a bit about mean and standard deviation of a discrete probability distribution.

If p(x) be the probability distribution of a random variable X.

Mean

where x are the values taken by the random variable X and 'E' is expectation.

Variance

Now,

and,

Standard Deviation

Coming back to our problem,

First of all enter the data in the Excel datasheet,

Under column C in cell C1 name it "x.P(x)" and in cell C2 write "=A2*B2"

Then press Enter.

Now to calculate all the other values in the Column C we use a very useful feature called the "Auto Complete" feature of Excel. To do that select the cell C2 and bring the mouse pointer to the bottom right corner of the cell the mouse pointer changes to a "+" sign now click and drag the your mouse to cell C27 you will see all the other values calculated automatically.

Now under column D in cell D1 name it "x².P(x)" and in cell D2 write "=(A2^2)*B2"

Then press Enter.

Similarly like in column C use the auto complete feature in column D.

Now write "Total" in cell A28. In row 28 we are going to calculate the sum of the columns C and D. To do that write in cell C28 "=SUM(C2:C27)"

Then press Enter.

Now we calculate the sum of column D. To do that we again use the auto complete feature. Select cell C28 and bring the mouse pointer to the bottom right corner of the cell the mouse pointer changes to a "+" sign now click and drag the your mouse horizantally towards column D. You will see sum of the column D calculated.

Therefore from the table its clear that,

Now its time for us to calculate variance and standard deviation.

Now in anywhere in the worksheet write mean, variance, standard deviation.

as we know that Mean=E(X)=12.5. We write 12.5 in cell G5.

Now we know that,

Now we know that cell D28 is E(X²)=162.5076. Therefore in cell G6 we write "=D28-(G5^2)"

Then press Enter.

Now we know that,

To do that we write in cell G7 "=SQRT(G6)"

Then press Enter.

Hence,

To Round of to two decimal places in Excel the standard deviation we write in cell G7 "=ROUND(SQRT(G6),2)"

Then press Enter.