Descriptive statistics for grouped (weighted) data
Originally published: 19/04/2019 07:40
Last version published: 08/04/2021 07:51
Publication number: ELQ-77456-6
View all versions & Certificate

Descriptive statistics for grouped (weighted) data

Calculating key statistics measures (average, median, standard deviation, skewness etc.) for grouped or weighted data

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.

This Best Practice includes
1 Excel file, 1 PDF file

Andrei Okhlopkov offers you this Best Practice for free!

download for free

Add to bookmarks


Further information

Calculate statistical parameters of the data to get a better feeling and insight of the numbers and sense the 'story behind the numbers'

Grouped or weighted data (e.g. a sales plan for a product portfolio)


4.8 / 5 (33 votes)

please wait...