Assume a “Labour utilisation” report which shows day wise tasks performed by different employees (row labels are task names, column labels are days and data in the matrix shows employee names). Each employee can perform multiple tasks on the same day and same task on multiple days. Given a list of all employees, one may want to answer […]
Read More →
{ 12 Comments }
In each of the four worksheets (one for each week) of this workbook, there is a two column database showing Employee ID and Revenue earned by that employee in that week. Not all employees appear on all sheets and there is no fixed cell reference for any employee on any sheet – therefore employee A001 can […]
Read More →
{ 34 Comments }
In range A1:BG2185 of this workbook, there is macro economic and demographic data for years from 1984 to 2009 (26 years) for 84 countries . Furthermore, there are 57 parameters being tracked for each of the 26 years for all 84 countries. There are missing observations for some years across many parameters and many countries. […]
Read More →
{ 4 Comments }
Assume a three column database – Site ID, Reason & Date and time. The data is available in range A2:C6 of sheet1 of this workbook. The objective is to view all those records where Mains failed between 23:00 and 03:00 (irrespective of the date). Applying a Custom Filter > Between on the Data and time column […]
Read More →
{ 4 Comments }
In this workbook, the worksheet named Bookings shows flight booking data as per the company’s records. The Worksheet named Vendor data shows bookings as per the vendor’s records. There are discrepancies at three levels between these two sheets. 1. There could be PNR’s in the company records (column B of Bookings) which are not present […]
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 →
{ 1 Comments }
In range B3:B7, assume the following values: 12,34-,23,6-,8. As can be observed, the minus sign is appearing after the number thereby treating 34- and 6- as text values. The objective is to place the minus sign before the number so that all values in range B3:B7 can be added. Since data is in one column […]
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 →
Given the following inputs/restrictions, one may want to compute the end date and time of a project: 1. Start date and time of the project; and 2. Official working hours; and 3. Lunch breaks hours Furthermore, to add to the complication, one may have different National holidays and weekend days i.e. while for some, the weekend […]
Read More →
Assume a worksheet with formulas referring to the previous worksheet. When this sheet is copied by right clicking the sheet and selecting Move or Copy > Copy, formulas in this newly created sheet, should change to refer to the previous sheet. In other words, if sheet2 has formulas referring to sheet1, then when sheet3 is created (by […]
Read More →
Create employee wise Effort Utilisation Report
{ 0 Comments }