Category: POWERPIVOT

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 […]

Read More →

Perform an iterative sum of Top n values across multiple columns

{ 4 Comments }

A tournament has 18 participating teams with 25 players in each team.  Each team has to play five rounds of the Tournament and not all players play all rounds.  Scores earned by each player in each round are shown in individual cells.  If a player does not play a round, that cell is left empty. The task […]

Read More →

Computing penalties by Employee, Group and Labour type using a PowerPivot

{ 0 Comments }

Assume a database of Maximum allowed pay and Actual pay for each employee.  The employees have been further categorized into Groups and labour categories. The task is to create three Pivot Tables (one each with Employee name, Group and Labour category in the row labels) with the following information in the Value area section: 1. […]

Read More →

Count unique values with conditions on large databases

{ 8 Comments }

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all. In this workbook, I have shown the technique to count unique values with conditions on a large database 1. Using PowerPivot – Will only work in […]

Read More →

Calculate a unique count with conditions in a Pivot Table

{ 12 Comments }

Assume a three column table arranged as follows: Circle, Date of Fault and ID.  Dates in the date range span one week – November 26, 2012 to December 2, 2012.  A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week.  Data for one week […]

Read More →

Compute Pro rata growth rate within a Pivot Table

{ 0 Comments }

Assume a four column input data range (refer sheet named “Input” of this workbook) arranged as follows: 1. First tow columns are Group and Type which have text values 2. Third column is a month column with entries such as Dec_11 and Sep_12.  These denote 12 months ended December 2011 and 9 months ended 2012 3. […]

Read More →

Count entries in a range which exclude certain user defined words

{ 5 Comments }

Assume the following sentences in range B2:B6 B2 – This is an Apple Pie B3 – An apple a day keeps a doctor away B4 – These Pears, apples and mangoes are sweet B5 – In this season, prices of mangoes have increased B6 – This is a glass of Guava juice In range B9:B10, type […]

Read More →

Computing growth % inside a pivot table

{ 66 Comments }

Having created a Pivot Table from a Base_Data sheet, one may want to compute the Account wise and Growth % of 2010 over 2009. There are two ways one can go about answering the question above: Solution A – For Excel 2010 and higher versions – This solution is for those using the PowerPivot MS Excel […]

Read More →

Remove duplicates after satisfying additional conditions

{ 3 Comments }

Assume a two column database of patient ID’s and service availed.  One patient may avail the same service multiple times in a year due to which that record may appear as many times as the service is availed.  For e.g., if patient A001 avails the Radiology service twice, then A001 and Radiology will appear in two rows. […]

Read More →

Sum highest n numbers based on conditions

{ 4 Comments }

Assume a two column database with names in column A and numbers in column B.  Names in column A may be repeated.  If a user types a certain name in a cell, a formula should sum the highest three values from column B for that name. Depending upon the version of MS Excel which you are […]

Read More →