Here’s a simple 4 column dataset Bacterin Donor# Recovery Agency Date Donor Received DONOR STATUS B050001 1 09-06-2005 00:00 ACCEPT B050002 3 09-06-2005 00:00 ACCEPT B050003 1 09-06-2005 00:00 ACCEPT B050004 1 09-06-2005 00:00 ACCEPT B050005 1 09-06-2005 00:00 ACCEPT B050006 1 09-06-2005 00:00 ACCEPT B050007 1 09-06-2005 00:00 ACCEPT B050008 4 09-06-2005 00:00 ACCEPT […]
Read More →
{ 4 Comments }
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 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 →
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 →
Assume a simple Sales dataset from which a Pivot Table has been created. The Pivot Table has been sliced by two columns of the dataset. To represent data graphically, a Stacked Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook). The Stacked […]
Read More →
{ 7 Comments }
Assume a simple Sales dataset from which a Pivot Table has been created. The Pivot Table has been sliced by two columns of the dataset. To represent data pictorially, a Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook). Now let’s say, a […]
Read More →
Assume a simple 5 column database with the following data 1. Circle Name – A text field 2. PO_Number – An is an alphanumeric field 3. Quantity sold – A numeric field 4. Unit Price – A numeric field denominated in US$ 5. Revenue – A numeric field which is computed as Quantity sold * Unit Price To determine the Circle […]
Read More →
{ 0 Comments }
Visualise an MS Excel file with two worksheets: Employee headcount – a multi column dataset with information such as Employee code, Date of Joining, Age, Division, Department and Location. Each row represents data for one employee. The number of rows on this worksheet is approximately 700. Training Data – a multi column dataset with information […]
Read More →
When creating a Pivot Table one can easily apply the Maximum function for figures in the Value area section by right clicking and choosing Maximum from “Summarise Values By” > Maximum. The maximum so computed for figures in the Value area section is a “conditional maximum” because the numbers in the Value area section will […]
Read More →
Visualise a Pivot Table with a few Fields dragged in the Report filter, Row labels and Value Area section. In the Column labels are two fields, Month and then Year – so in the column labels, for every month, there is data for three years 2005, 2006 and 2007. For some months, there is data […]
Read More →
Show months with no data which fall within a certain date range of a Pivot Table
{ 4 Comments }