Assume a simple two column data range as follows: Product Amount A 1 S 2 D 3 E 4 Product A F 5 G 6 R 7 Product B q 8 w 9 s 10 d 11 c 12 v 13 b 14 Product C In the table above, one may want to compute the Product […]
Read More →
Author: Ashish Mathur
Scenario 1 In a Stock Broking business (Firm), Customers deposit margin money through cheques. Although cheques are received from customers at branches, clearing (realization) of the same normally takes 3-4 days. On one hand, the Firm wishes to allow trading to the customers against the deposited cheques from the day it receives the cheque, on other hand there is […]
Read More →
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 →
Extract City, State and Pin code from an address string
{ 66 Comments }
For a lot of Excel users, a common problem is to extract City, State and Pin Code from an address string. What compounds the issue is that there is no standardisation in an address string. For e.g., one may end the address with a Pin code, while others may end it with a State and Country. […]
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 month wise pending audits
{ 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 →
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 →
Prioritise investment liquidation to minimise Capital Gains
{ 2 Comments }
Assume a five column dataset with the Fund Name in column A, Market Value in column B, Gain/Loss in column C, Gain/Loss % in column D [column C/column B] and Type of investment in column E (Short term or Long term). Since multiple investments can be made in one fund, there can be multiple line items […]
Read More →
Customise Pivot Table reports
{ 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 →
Auto detect sum range when copying and pasting
{ 11 Comments }