Two parameters define a sample of data: location and spread or dispersion. It is easy to become misled in business when paying attention only to the location -- usually the average or median value -- while ignoring the dispersion of the data. For instance, all of your products may have an above average rating, but a large spread of ratings for a product means a portion of your customers are extremely dissatisfied. Microsoft Excel allows you to easily calculate a variety of dispersion measures to aid in your business planning.
Step 1
Open Microsoft Excel and load a worksheet that contains the data you wish to calculate dispersion statistics for.
Video of the Day
Step 2
Select a blank cell and label it "Data Variance," replacing "Data" with the name of the data being analyzed. The variance of a sample of data is a measure of the average value each data point differs from the sample mean. Variance is equal to the standard deviation squared. Variance is best suited to describe a normal or bell curve distribution. Its value will be less meaningful if your data is not a good fit to a normal distribution or contain many outliers, or extremely low or high values.
Step 3
Enter the following into the Excel function box with the cell that will contain the variance measure selected:
=VAR(A1:A100)
Replace "A1:A100" with the range of cell containing the data you wish to calculate the variance for.
Step 4
Select a blank cell and label it "Data First Quartile." The first quartile of a data sample is the value of the data point where 25 percent of the data is less than the value. Enter the following into the Excel formula box with the cell selected:
=QUARTILE(A:A100,1)
Step 5
Select a blank cell and label it "Data Third Quartile." The first quartile of a data sample is the value of the data point where 75 percent of the data is less than the value. Enter the following into the Excel formula box with the cell selected:
=QUARTILE(A:A100,3)
Step 6
Calculate the difference between the first and third quartile to calculate the interquartile range. This is a more robust measure of dispersion, applicable when your data does not fit a normal distribution and contains outliers.
Video of the Day