Assume quantity sold date by date and City in a three column database. The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table. The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running […]
Read More →
{ 16 Comments }
Assume that a tabular database shows Year wise, month wise. Account and Activity wise expenditure under difference Expenditure heads. While it is easy to drag columns inside a Pivot Table to analyse data, one may want to compute the Account wise and Activity wise Variance between May and August for all expense types except two. If […]
Read More →
Let’s say the data sheet has Sales and Profit data by Product, Region, Salesman and Date. One may want to analyse the following via a Pivot Table “The Sales Delta by year, month, salesman and product i.e. for May 2012, compute the [Sales in May 2012] less [Sales in May 2011] for every salesman and product. Do […]
Read More →
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 →
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 →
{ 0 Comments }
Assume there is data for complaints received and complaints resolved for two years – 2009 and 2010 for some regions regions. Each Region has its own worksheet. There are three headings on each sheet – Particulars, 2009 and 2010. While the Particulars column has the text “Complaints received” and “Complaints resolved”, the year columns have number of complaints received […]
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 two workbooks named Book1.xls and Book2.xls. In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb. In both the worksheets – Jan and Feb, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of […]
Read More →
Compute “running total in” across years in a Pivot Table
{ 10 Comments }