With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations. Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story. To be able to interact with/create visualizations, […]
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 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 →
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 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 }
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 }
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 →
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 →
Story telling with Excel Power BI
{ 6 Comments }