Tags: SUM

Rank numbers in a range after satisfying conditions

{ 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 →

Compute year on year growth in a Pivot Table

{ 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 →

Compute attrition rate from two different data sources

{ 0 Comments }

Assume two databases: 1. One showing employee headcount (one row per employee) which has all employee details such as Name, ID, Date of Joining, Supervisor name, Department etc. (Range A1:R781 of Source worksheet) 2. The other showing data for employees who resigned. (Range U1:Z36 of Source worksheet) The task is to compute the attrition rate for selected Group and […]

Read More →

Recompute figures in the Value area section of a Pivot Table after receiving a user input

{ 0 Comments }

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 →

Compute “running total in” across years in a Pivot Table

{ 10 Comments }

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 →

Story telling with Excel Power BI

{ 6 Comments }

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, […]

Read More →

Ensure that “Show Value as” feature of the Pivot Table works even when some Pivot Table columns are unfiltered/hidden

{ 9 Comments }

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 →

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 →

Determine cumulative expenses per employee when per diem rates vary by block of dates

{ 10 Comments }

Assume per diem travel rates vary by block of dates (from and to).  So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day.  Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day.  With different travel dates (from and to) specified per traveller, the […]

Read More →

Sum data from a particular cell of last n sheets only

{ 0 Comments }

A number is entered in cell A1 of every sheet in a multi sheet workbook.  The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets – x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed […]

Read More →