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 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 →
Assume there are two types of inverters – Inverter1 with a capacity of 15kW and Inverter2 with a capacity of 20kW. For an electricity load factor of, say 60kW, the objective is to determine the minimum number of inverters to be used. Therefore the number of inverters which can be used for a load factor of 60kW […]
Read More →
Assume a database where customers are listed from cell A6 down. From cell B5 to the right months are entered from April to March (B5:M5). In B6:M6 (Customer 1), a user enters 1’s and 0’s. A value of 1 respresents “Cheque bounced” and 0 represents “Cheque honoured”. Similar data is entered for other customers in B7:M500. […]
Read More →
{ 4 Comments }
Assume the following numbers in range C5:C22. Heading is in cell C4. Range C5:C7 – 11 Range C8:C11 – 14 Range C12:C13 – 23 Range C14-C19 – 56 Range C20-C22 – 78 One may want to colour cells for every change in number in range C5:C22 with the following two modifications: 1. Shading should be for […]
Read More →
{ 4 Comments }
In range G13:G17, assume the following entries – VG:25, NS:50, NS:20, #DIV/0! and VG:150. To sum up the values where VG appears, you may refer to my solution in this workbook.
Read More →
{ 3 Comments }
Assume a two column database of patient ID’s and service availed. One patient may avail the same service multiple times in a year due to which that record may appear as many times as the service is availed. For e.g., if patient A001 avails the Radiology service twice, then A001 and Radiology will appear in two rows. […]
Read More →
{ 2 Comments }
In range A7:A15, assume the following values A, S , empty cell, empty cell, R, A, W, A, SD. Starting from cell B17, transpose data to the right (ignoring the blank cells). Therefore, in range B17:F17, the result should be A, S, R, W SD. Solution for MS Excel 2010 and higher versions If you […]
Read More →
{ 72 Comments }
Given an alphanumeric string, one may want to perform the following Extract phone number from the string Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself. These numbers may be mobile numbers and/or mobile numbers. Furthermore, PIN codes may also be mentioned in the address string. One may want […]
Read More →
Create employee wise Effort Utilisation Report
{ 0 Comments }