One worksheet shows the date wise courses (External Course, Internal Course and Elearning Course) taken by staff members. The data also shows the Line Managers that they report to. Another worksheet lists down all staff members and the Line Managers that they report to. From data on the first worksheet, one can easily determine (via a Pivot Table) […]
Read More →
{ 0 Comments }
Performing calculations or writing formulas on large databases pose the following major problems: 1. Time taken to process is unduly long; and 2. File size increases disproportionately A typical problem is fetching data from another worksheet or workbook via the VLOOKUP() function. Quite often, when one confirms the VLOOKUP() formula in a single cell with the Enter […]
Read More →
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 →
Assume numbers from 1 to 8 in range B3:B10 and numbers from 9 to 16 in range C3:C10. The objective is to create all possible combinations in column E from these two ranges without using VBA (macros). This combined range of all possible combinations is called a Cartesian product. You may refer to the following steps […]
Read More →
{ 17 Comments }
Assume a beginning date in cell A7 and ending date in cell B7. In range C7:E7, the task is to compute the years (in cell C7), remaining months (in cell D7) and remaining days (in cell E7). As an example, if one types in 12-March-2013 in cell A7 and 24-March-2014 in cell B7, then the […]
Read More →
Assume that there are three separate tables showing the following information: 1. Date of visit data for visitors to a certain recreation facility. The same visitor may visit the facility multiple times 2. Fee per visit in different cites 3. Region in which each city lies The question is to analyse the three tables above via […]
Read More →
A tournament has 18 participating teams with 25 players in each team. Each team has to play five rounds of the Tournament and not all players play all rounds. Scores earned by each player in each round are shown in individual cells. If a player does not play a round, that cell is left empty. The task […]
Read More →
{ 10 Comments }
Assume numbers are typed in range A1:A2 of multiple worksheets in a workbook. The task is the compute the MODE of these numbers. Mode is defined as the value which appears most frequently in a range of cells. So, if one types 1,3,4,3,5,6 in range A1:A6, then the mode will be 3 – 3 appears maximum number of times in […]
Read More →
Assume a large data set spanning many rows and columns. In the data set are entirely blank rows and columns. As an example, let’s say data is in range A2:G5000, then from A5005:J87634 and then from A87636:F92345. Furthermore, assume that columns H and I are completely blank. Data in the filled up cells can be […]
Read More →
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 →
Creating Exception Reports
{ 23 Comments }