Overview

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.

Share
Reviews Add a review
No reviews yet

More From Andrei Okhlopkov

Browse our top rated business templates. See All
Descriptive statistics for grouped (weighted) data
86
6
Excel has a powerful set of tools to perform statistical analysis, but they apply only to ungrouped data. As an…
Dates and Timelines in Excel
1,021
70
In this post we will look at: a) Extending or shortening time schedules b) Modelling interest expense c) Planning orders,…
Charts and Dashboards in Excel (full version)
1,187
3
The attached file: - defines key chart elements and explains how to edit them - demonstrates how to deal with…
Charts and Dashboards in Excel (short version)
1,308
142
This is an introductory part to creating professional charts which:   - defines key chart elements and explains how to…
Analysing sensitivities with indifference curves (unlocked)
1,059
1
This is an unlocked version of the same utility published on my page. In this version you can access and…
Analysing sensitivities with indifference curves
1,049
113
Indifference curves depict sensitivity of an output to the two inputs. Each curve corresponds to a fixed output amount and…
See All