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 →
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 →
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 →
{ 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 →
{ 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 →
{ 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 →
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 →
{ 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 →
{ 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 →
{ 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 →
Create a Pivot Table from multiple individual ranges without using ancillary columns
{ 16 Comments }