Descriptive statistics for grouped (weighted) data

Description
Excel has a powerful set of tools to perform statistical analysis. One of the drawbacks is that Excel statistical functions can be applied 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.

In this review I have compiled the formulae to perform statistical calculations for grouped data. They cover the same (and in some cases even greater) scope as Excel’s native statistical functions.

The file consists of two principal sheets:

1) calculations for single-array data (average, median, mode, variance, standard deviation, percentiles, percent ranks, skewness, kurtosis)

2) calculations for dual-array data (covariance, correlation, standard error, linear trend).

I have also given a brief overview of the basics of statistics and tried to explain how it can be used in financial analysis.

The file also includes a sheet which explains one of the methods to assign weights to your numbers, depending on their relevance and significance to what you are benchmarking. Doing weighted calculations returns more accurate and relevant outcome and so is always preferred to using simple, unweighted calculations.

So welcome to the exciting world of statistics! Do let me know your thoughts, comments and suggestions.