Excel has a powerful set of tools to perform statistical analysis, but they apply only to ungrouped data. As an example, the AVERAGE function calculates a simple average of ungrouped numbers, but calculating a weighted average requires a different approach.
This review compiles the formulae to perform statistical calculations for grouped data. They cover the same (and even greater) scope as Excel’s native statistical functions.
The file includes calculations for:
- single-array data (average, median, variance, standard deviation, percentiles, skewness, kurtosis)
- dual-array data (covariance, correlation, standard error, linear trend).
I have also given an overview of the basics of statistics and how it applies to financial analysis and included a method to assign weights to your numbers, depending on their relevance to your benchmarks.
