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 →
Assume date wise revenue data has been shown on two sheets – Sports wear and Fitness equipment (Data worksheets). On each of these two worksheets, there are two columns – Date and Revenue. On the Summary worksheet, the user would like to summarise data (Count instances and sum revenue figures) based on multiple conditions. The […]
Read More →
{ 0 Comments }
Assume there is data for complaints received and complaints resolved for two years – 2009 and 2010 for some regions regions. Each Region has its own worksheet. There are three headings on each sheet – Particulars, 2009 and 2010. While the Particulars column has the text “Complaints received” and “Complaints resolved”, the year columns have number of complaints received […]
Read More →
{ 2 Comments }
In a range of cells, assume there is a list of invoices raised during a certain period. Since adequate care was not taken to number the invoices sequentially, there are missing invoice numbers in the Excel range. So, it is possible that after raising invoice number 501, 502 and 503 the next invoice raised was 507. Given […]
Read More →
In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched. Furthermore, the VLOOKUP() function returns data from one column only. To work with a list where there are multiple occurrences of a particular string and data has to be extracted from multiple columns, […]
Read More →
{ 8 Comments }
Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all. In this workbook, I have shown the technique to count unique values with conditions on a large database 1. Using PowerPivot – Will only work in […]
Read More →
{ 0 Comments }
Data on the sheet named “Sales Data Master” shows the Territory wise, Show Title wise and Sales man wise forecast and actual data. One may want to summarise data based on an unknown combination of conditions. Conditions may be entered as: 1. All three conditions specified 2. No condition specified 3. Only first condition specified […]
Read More →
{ 8 Comments }
Line of Business (LoB) wise CAGR (%) is shown in range A3:B8. A benchmark growth rate is shown in cell B11. The task to show a column chart of the data presented in range A3:B8. LoB’s with a CAGR (%) greater than the benchmark rate should appear in Green colour and those with a CAGR (%) […]
Read More →
{ 0 Comments }
A number is entered in cell A1 of every sheet in a multi sheet workbook. The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets – x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed […]
Read More →
{ 0 Comments }
Data in a two column database (A3:B13) can be Auto filtered on column A with one or many conditions. Data in column B will be numbers only (positive, negative or 0’s) – no text values. After filtering data in column A, one may want to extract the unique numbers from column B to cell A18 (and then […]
Read More →
Determine cumulative expenses per employee when per diem rates vary by block of dates
{ 10 Comments }