Assume a dataset with a Key Performance Indicator (KPI) [appearing in one column] data for years ranging from 1985 to 2010 for 114 countries. This dataset has 170,000 rows of data and one row below the last row for every country, there is a total of the KPI column. So, if there are 25 rows for India, then in the 26th row, there will be the total appearing for numbers in the KPI column. The same is occurring for other countries as well.

There is another dataset (in another worksheet of the same workbook) which has an index value for the same countries and same date range as the first dataset. The second dataset is relatively smaller (with only 1315 rows) because the index value is not available for all years of each country.

The objective is to determine the year wise (for all years from 1985 to 2010) weighted average of KPI. An illustration of the weighted average computation has been shown in range F5:H10 of the “Index” worksheet of the workbook link shared below.

Solving this problem using Pivot Table, filters, formulas will slow down processing speed due to sheer size of data. I have solved this problem using the Power Pivot tool (for Excel 2010 and higher versions).

You may refer to my solution in this workbook.

## Compute year wise weighted average on a large dataset

{ 2 Comments }