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 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 →
{ 0 Comments }
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 →
{ 24 Comments }
Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices. On another sheet, the closing stock of each Raw material type is entered. In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued […]
Read More →
Assume the following sentences in range B2:B6 B2 – This is an Apple Pie B3 – An apple a day keeps a doctor away B4 – These Pears, apples and mangoes are sweet B5 – In this season, prices of mangoes have increased B6 – This is a glass of Guava juice In range B9:B10, type […]
Read More →
Assume the following dates in range B4:B8 B4 – 29/09/2011 B5 – 04/10/2012 B6 – 05/10/2012 B7 – 07/10/2014 B8 – 15/07/2015 One may want to count dates which are within 365 days of each other. The answer should be four. The four dates are: 1. 04/10/2012 2. 05/10/2012 3. 07/10/2014 4. 15/07/2015 The formula […]
Read More →
{ 0 Comments }
In range B3:V3, one will record Purchase price of various Assets purchased in different months. In the next two rows one specifies whether the Assets purchased in that month have to be amortised or not and the months over which the Assets has to be amortised respectively. The task is to determine the amount to […]
Read More →
Assume a database of five rows and four columns. Each cell of the database has some text value – let’s call this the source grid. In another part of the same worksheets, the text values of source grid have been arranged as rows and column headings – let’s call this the destination grid. The task […]
Read More →
{ 0 Comments }
Assume there are numbers typed in range B3:G8. In row 10, one may want to sum up diagonal cells i.e. in cell B10, one would want to get the value from cell B8, in cell C10, one may want to sum values from cells B7 and C8. in cell D10, one may want to sum […]
Read More →
Perform an iterative sum of Top n values across multiple columns
{ 4 Comments }