Skip to content

Dispersion Measures with Excel

Last Update: January 14, 2021

Descriptive statistics consists of quantitative or qualitative data population or sample frequency distribution, central tendency measures, dispersion measures, association measures and frequency distribution shape.

This topic is part of Business Statistics with Excel course. Feel free to take a look at Course Curriculum.

This tutorial has an educational and informational purpose and doesn’t constitute any type of forecasting, business, trading or investment advice. All content, including spreadsheet calculations and data, is presented for personal educational use exclusively and with no guarantee of exactness of completeness. Past performance doesn’t guarantee future results. Please read full Disclaimer.

1. Dispersion measures

Dispersion measures consist of data population or sample variability. Main dispersion measures are standard deviation, variance and average deviation or mean absolute deviation.

  • Standard deviation consists of squared root of average data population or sample squared dispersion.

\sigma=\sqrt{\frac{1}{n}\sum_{t=1}^{n}(x_{t}-\mu)^2}\;\;or\;\;\bar{\sigma}=\sqrt{\frac{1}{\bar{n}-1}\sum_{t=1}^{\bar{n}}(x_{t}-\bar{\mu})^2}

Where \sigma = population standard deviation, x_{t} = data, \mu = population mean, n = population number of observations, \bar{\sigma} = corrected sample standard deviation, \bar{\mu} = sample mean, \bar{n} = sample number of observations.

  • Variance consists of average data population or sample squared dispersion.

\sigma^2=\frac{1}{n}\sum_{t=1}^{n}(x_{t}-\mu)^2\;\;or\;\;\bar{\sigma}^2=\frac{1}{\bar{n}-1}\sum_{t=1}^{\bar{n}}(x_{t}-\bar{\mu})^2

Where \sigma^2 = population variance, x_{t} = data, \mu = population mean, n = population number of observations, \bar{\sigma}^2 = corrected sample variance, \bar{\mu} = sample mean, \bar{n} = sample number of observations.

  • Mean absolute deviation or average deviation consists of average data population or sample absolute dispersion.

ad=\frac{1}{n}\sum_{t=1}^{n}\left | x_{t}-\mu \right |\;\;or\;\;\bar{ad}=\frac{1}{\bar{n}}\sum_{t=1}^{\bar{n}}\left | x_{t}-\bar{\mu} \right |

Where ad = population mean absolute deviation or average deviation, x_{t} = data, \mu = population mean, n = population number of observations, \bar{ad} = sample mean absolute deviation or average deviation, \bar{\mu}= sample mean, \bar{n} = sample number of observations.

2. Excel example.

2.1. Dispersion measures data and data daily arithmetic returns calculation.

  • Data: S&P 500® index replicating ETF (ticker symbol: SPY) daily adjusted close prices (2007-2015).
  • Initial data daily arithmetic return assumption not fixed and only included for educational purposes.

2.2. Dispersion measures data and data daily arithmetic returns calculation formulas.

2.3. Dispersion measures calculation.

2.4. Dispersion measures calculation formulas.

My online courses are closed for enrollment.
+