Descriptive Statistics in Excel

Let’s use a mini data set of 4 observations

	A	B	C
1	4
2	6
3	5
4	5
5
6
7
8
9

1) Finding the mean

Enter =AVERAGE(A1:A4) into an empty cell, such as cell A6

Result:

	A	B	C
1	4
2	6
3	5
4	5
5
6	5
7
8
9

2) Finding the variance

Enter =VAR(A1:A4) into an empty cell, such as cell A7

Result:

	A	B	C
1	4
2	6
3	5
4	5
5
6	5
7	.6666667
8
9

3) Finding the standard deviation

Enter =STDEV(A1:A4) into an empty cell, such as cell A8

Result:

	A	B	C
1	4
2	6
3	5
4	5
5
6	5
7	.666667
8	.816497
9

4) Another way of finding the standard deviation

Enter =SQRT(A7) into an empty cell. In this way we are taking the square root of the variance that we have already computed in (2) above.

5) The long, step by step way of finding the variance. I show this in Excel, but this is also the method that you would use to work by hand.

a) First we must find the difference between each observation and the mean.

Enter =A1 – A$6 into cell B1.

A6 is the location of the mean that we computed in (1) above. By putting the dollar sign in, when we copy the formula from B1 to B2 through B4, Excel will keep looking in A6 for the number. If we do not put the dollar sign in, Excel will change the formula to (A2 – A7), (A3 – A8) and (A4 – A9).

Then copy the contents of B1 into B2 through B4.

Result:

	A	B	C
1	4	-1
2	6	1
3	5	0
4	5	0
5
6	5
7	.666667
8	.816497
9

b) Next we need to square each difference.

Enter =B1*B1 or = B1^2 into cell C1.

In the first formula we square B1 by multiplying it by itself. In the second option we raise B1 to the power 2. Just different ways of writing the same thing.

Then copy the contents of C1 into C2 through C4.

Result:

	A	B	C
1	4	-1	1
2	6	1	1
3	5	0	0
4	5	0	0
5
6	5
7	.666667
8	.816497
9

c) Next we find the sum of the squared differences.

Enter =SUM(C1:C4) into an empty cell such as C6.

Result:

	A	B	C
1	4	-1	1
2	6	1	1
3	5	0	0
4	5	0	0
5
6	5		2
7	.666667
8	.816497
9

d) Finally, we divide the sum of squared differences by (n-1)

Since in this case n=4, (n-1)= 3

Enter =C6/3 into an empty cell, such as C7

Result:

	A	B	C
1	4	-1	1
2	6	1	1
3	5	0	0
4	5	0	0
5
6	5		2
7	.666667		.666667
8	.816497
9

Slightly more advanced Excel technique: Rather than type in the value of (n-1) we can let Excel compute this for us. =COUNT(range) will tell how many of the cells in the range have a number value in them. For example, below I have put =COUNT(A1:A4) into A6, =COUNT(B1:B4) into B6 and so in in C6 and D6 so that you can see how this formula works. It only counts number values, not text or empty cells.

	A	B	C	D
1	2	2	VT	2
2	100	2	NH	2
3	19		ME	UVM
4	2		NY
5
6	4	2	0	2
7
8
9

So, you could use the formula =C6/((COUNT(C1:C4)-1) in cell C7 to compute the variance.

6) The long, step by step way of finding the standard deviation

Follow all steps in (5) above to find the variance.

Enter =SQRT(C7) into an empty cell, such as C8. In this way we are taking the square root of the variance that we have already computed in (5).

Result:

	A	B	C
1	4	-1	1
2	6	1	1
3	5	0	0
4	5	0	0
5
6	5		2
7	.666667		.666667
8	.816497		.816497
9