Calculating the Mean, Standard Deviation, and Coefficient of Variation

Suppose you want to calculate the mean, standard deviation, and coefficient of variation and you entered the data in an Excel spreadsheet as follows:

-.10 in cell C1
+.27 in cell C2
-.25 in cell C3
+.35 in cell C4
-.10 in cell C5
+.48 in cell C6
-.18 in cell C7
+.59 in cell C8
-.11 in cell C9
-.25 in cell C10
+.46 in cell C11
+.01 in cell C12

The above numbers represent the returns of stock ABC over a 12-year period. Thus, -.10 means that the stock lost 10% of its value over the first year, +.25 means that the stock gained 25% over the second year (by the way, the + sign is not necessary; a number without a negative sign in front of it is assumed to be positive), ...

To calculate the average return, go to the Function Wizard.  The function you are going to use is in the function category of STATISTICAL and is AVERAGE.  You will have to enter the data range (the data is in C1:C12)

NUMBER 1:  C1:C12

For the above problem, the average return is .0975 or 9.75%.  

To calculate the standard deviation, go to the Function Wizard. The function you are going to use is in the function category of STATISTICAL and is STDEV.  Again, you have to enter the data range.

NUMBER 1:  C1:C12

The answer I got for the above data set is .310575 or 31.06%.  When the standard deviation is greater than the mean, you have a very volatile stock.

The coefficient of variation is the (standard deviation/average) times 100%.

For the above problem, the coefficient of variation is 319%. 

PROBLEM 2-- Stock PQ

+.06 in cell C1
+.05 in cell C2
+.04 in cell C3
+.07 in cell C4
+.08 in cell C5
+.06 in cell C6
+.07 in cell C7
-.02 in cell C8
+.01 in cell C9
+.08 in cell C10
+.10 in cell C11
-.01 in cell C12

Average Return is 4.92%
Standard Deviation is 3.75%
Coefficient of Variation is 76.22%
This stock is not as volatile as stock ABC, but has a lower average return. 

If you want to obtain all the descriptive statistics (mean, standard deviation, variance, standard error, kurtosis, etc.) in one shot, do the following:

(1) (Older versions of Excel) From Toolbar:  Go to Tools -- Data Analysis -- Descriptive Statistics  click ok
(If you do not have the Descriptive Statistics option in Data Analysis, go to Tools -- Add-ins and click on Analysis ToolPak)
(2)  You must indicate the Input Range:  for the above data set it is c1:c12
Indicate the Output Range:  where you want the output to appear, e.g., G5
Click on Summary Statistics
If you wish, you can click on Confidence Level for Mean and obtain a confidence interval for the mean.  The default is 95% confidence interval.

IN the newer versions of Excel, here is how to get to Analysis ToolPak which you need for any kind of Data Analysis:
(1)  Open Excel. You will see the Excel Spreadsheet on the page.  On the top left of this page, you will see the Office Button (it has the MS Office logo).  
(2)  Click on the Office Button  (3)  Click on Excel Options  -- it is on the bottom line of the box (4)  Click on Add-Ins -- it's on the left side of the page
(5) On the bottom of the box, you will see  "Manage Excel Add-Ins" and a G0...  next to it.  Click on GO...
(6)  Check the Analysis ToolPak box; Click OK.

(1) (Newer versions of Excel).  Click on Data; Click on Data Analysis; Click on Descriptive Statistics; Click OK
(2)  You must indicate the Input Range:  for the above data set it is c1:c12
Indicate the Output Range:  where you want the output to appear, e.g., G5
Click on Summary Statistics
If you wish, you can click on Confidence Level for Mean and obtain a confidence interval for the mean