Tags: RELATED

Compute year wise weighted average on a large dataset

{2 Comments}

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.

Create a Pivot Table from multiple individual ranges without using ancillary columns

{16 Comments}

Assume that there are three separate tables showing the following information:

1. Date of visit data for visitors to a certain recreation facility.  The same visitor may visit the facility multiple times
2. Fee per visit in different cites
3. Region in which each city lies

The question is to analyse the three tables above via a Pivot Table to generate the following:

1. Region wise and visitor wise:
a.  Fee per visit
b.  Frequency of visit
c.  Revenue
2. Revenue collected by month wise and by visitor

In this workbook, I have shared two solutions:

1. PowerPivot solution - This solution answers both questions above without using any calculated/ancillary columns in the base data.  By establishing simple relationships in the PowerPivot window and by writing two calculated Field formulas, both questions above have been answered.  To use this PowerPivot solution, you need to be using the PowerPivot add-in for MS Excel.  This add-in is only available for Excel 2010 and higher versions.

2. Pivot Table solution - This solution answers both questions above by using calculated/ancillary columns in the base data.

Here's another example.  Assume a four column table showing Date of session, Client, Location and Participant Name.  Assume another four column table showing Client, Date of session, Date of invoice and Amount Billed.  The task is to determine the Amount billed per location.  You may refer to my PowerPivot and Pivot Table solution in this workbook.