Copyright

Using Excel to Calculate Measures of Dispersion for Business

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

In this lesson you will learn a practical application for measures of dispersion in business. We will use Microsoft Excel to calculate values for a data set of investment returns.

Disperse!

As a small-business owner, you have decided to make some additional investments to bolster your enterprise. This may provide great opportunity, but also great risk. You have looked at the average price of stocks, the average return, and risk factors. But the average only tells us so much: It doesn't address the variance, or deviation from the mean.

For example, you might have an average rate of return of 33%! That is terrific... until you look at the variance of that data set. The deviation from the mean could be so much that a conservative investor would want to avoid risks. On the other hand, a data set showing a lower rate of return might have a narrower variance, indicating a stronger/safer portfolio.

Variance

In order to understand variance, let's look at some sample investment data (granted this data set is very small). We have the data set labeled Variance Data Set:


Variance Data Set
Variance - data set


In Excel 2010, we will use the function VAR.S. In earlier versions of Excel, the VAR function works just as well. We will be putting our output in cell C2. For this lesson, we'll use the wizard to select our data.

Click the fx icon to bring up the formula selector. Type or select Var.S and click OK.


Select var function


The function will ask for a range of values. While your cursor is in the Number 1 box, select column A, then click in the Number 2 box and select column B. Then click OK.


Variance - run function


Now the output is in Column C:


Variance value


While our investments looked like they were all over the place (the negative 5% return seemed a little scary!), the overall variance is low. This is a good mix of investments and risk appears to be mitigated across the investment portfolio.

Variance is good, but we have a couple of other tricks up our sleeve. Next up, let's tackle standard deviation.

Standard Deviation

Keeping the same data set of investment returns, we can calculate the standard deviation, that is the variance of the set as a whole. We'll be using the STDEV.S function in Excel:

  1. Navigate to the last cell in column A (A7).
  2. Click in the white function box and type =STDEV.S(A2:A6)
  3. Press Enter

Repeat for the last column in B. Your Excel sheet should look something like the Excel Standard Deviation Output image:


Excel Standard Deviation Output
Excel Standard Deviation Output


If you use the Excel function wizard, you'll notice that there are quite a few standard deviation options. For our purposes, the S option is best because it is used for a data sample. Use STDEV.P if you have a population; e.g., text or other data.

With standard deviation calculated, let's look at the next tool to measure dispersion: Z-scores.

Z-Scores

Before we dive into Z-scores, let's combine all of our investment return data into a single column of data, shown in the Rates of Return image:


Rates of Return
Rates of Return Data


Up to now, we've been talking about standard deviations and variance. But we can get a little more detailed and look at each data point and its relation to all other points.

Take the -5% rate of return for example: How far away from the mean is that particular return? In other words, how many deviations is -5% away from the average of all returns?

To get that detail, we use the z-score. In Excel, there are a few steps we need to take.

First, get the average of the range. In the Get Average image, we've added columns to hold z-score and average; however, it shows that we first calculate the average/mean of the data:


Get Average
Get Average Excel


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support