Imagine a two column dataset – Customer Code and Material Number (with alphanumeric data). The objective is to determine the second highest quantity sold per Customer code. Since we will first have to determine the Customer wise and Material Number wise quantity sold, a good way to get started is to use a Pivot Table. […]
Read More →
{ 0 Comments }
Assume a dataset with two columns which lists down the student names in column A and courses opted for in column B. Since one student can opt for multiple courses and the same course can be taken up by multiple students, there can be repetitions in both columns. The objective is to create a matrix […]
Read More →
{ 0 Comments }
Assume a two column dataset with Date in the first column and Price in the second one. The purpose is to identify times to buy and sell – buying would be just after the lowest low is confirmed and sell before or just after the highest high is in place. Confirmation is achieved through crossover of […]
Read More →
{ 0 Comments }
Assume a simple four column dataset with the following columns – User, Month, Leads and Sales. The dataset shows the user and month wise leads generated and revenue earned. One may want to analyse this data in a Pivot Table with the User field appearing in the Row labels section, Months field appearing in the […]
Read More →
{ 0 Comments }
Assume a five column dataset – ID, Age, Gender, Time and Class. For chosen ID’s, the objective is to: 1. Assign a Rank (in ascending order of time i.e. lowest time will be rank 1 and so on) to each ID 2. Determine the overall place of each ID – Count of unique time entries […]
Read More →
{ 0 Comments }
Assume a three column dataset which has Year, Company ID and Cash flows. For each Company, there are cash flows for multiple years. So for Company ID A001, there are 7 rows, one each for 2010 to 2004 and cash flows appearing in a third column. Let’s assume the number of rows are 750,000. The task is to compute […]
Read More →
{ 0 Comments }
Assume a three column dataset showing Audit ID, Date of receipt of audit mandate and Date of audit completion. There are other columns as well but they are not important for our Analysis. One may want to compute the following month wise: 1. Which (Audit ID) are the audits pending at the end of every […]
Read More →
{ 8 Comments }
Assume that someone has created a Pivot Table using the PowerPivot tool. Now one may want to customize the Pivot Table even further by: 1. Shuffling rows in the Pivot Table; and 2. Recomputing subtotals and Grand Totals after reshuffling rows A Pivot Table created via the PowerPivot tool can be converted into a normal range […]
Read More →
Assume a simple three column dataset showing hours worked by different machine on different dates. So column A is Date, column B is Machine Name and column C is hours worked. There are duplicates appearing in column A and B . Blanks in column C depict machine idle time. The task is to create a simple […]
Read More →
{ 30 Comments }
Visualise Sales Data of a Non-Alcoholic Beverage Company with basic columnar information such as Date of Sale, Time of Sale, Brand, Stock Keeping Unit (SKU), State, City, Quantity sold, Unit Price and Salesman Code. In this sales dataset, each line item represents one visit for one SKU. If nothing is sold in a certain visit, then the SKU […]
Read More →
Filter the Rank Field in a Pivot Table
{ 4 Comments }