Assume a four column input data range (refer sheet named “Input” of this workbook) arranged as follows: 1. First tow columns are Group and Type which have text values 2. Third column is a month column with entries such as Dec_11 and Sep_12. These denote 12 months ended December 2011 and 9 months ended 2012 3. […]
Read More →
{ 5 Comments }
Assume data is in range A3:E10. In A4:A10, various service types are mentioned. In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012. In range B4:E10 are numbers representing number of customers. One may want to answer the following questions from this data: 1. For every date, total number of customer walkins and […]
Read More →
{ 66 Comments }
Having created a Pivot Table from a Base_Data sheet, one may want to compute the Account wise and Growth % of 2010 over 2009. There are two ways one can go about answering the question above: Solution A – For Excel 2010 and higher versions – This solution is for those using the PowerPivot MS Excel […]
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 →
{ 4 Comments }
In a multi column database, assume a filter has been applied on some columns. Post filtering, some error values (such as #DIV/0!, #NA etc.) appear in the one of the numeric columns. The objective is to sum numbers in a numeric column. The usual SUM() function would not work because the range of sum function should be error free. Furthmore, […]
Read More →
Given the net annual salary figure and progressive tax rates for different income slabs, one may want to calculate the gross annual salary. You may refer to my solution in this workbook.
Read More →
{ 2 Comments }
Assume there are three worksheets, one each for 2008, 2009 and 2010. On each sheet there are two columns for Account code and Account description. Thereafter there are 12 columns (one for each month). Values inside the matrix represent amount spent on a specific item in a specific month. On the P&L worksheet, a user will provide the […]
Read More →
{ 0 Comments }
The objective is to determine the quantity of stock (of a particular type) to be transferred from “Stock surplus” locations to “Stock deficient” ones. If all the requirements cannot be met from one location, tap other locations. The final output should show the location from where stock is being transferred. Furthermore the order of determining […]
Read More →
{ 0 Comments }
Many a times writing nested IF() statements become cumbersome, lengthy and highly prone to errors. In worksheet named “Question”, one would like to avoid using spare columns for computation of landed value for the three product sources (“CKD”, “LMP”, “IMP”). While one can use a nested IF() statement, it would be unmanageable because of the length of the […]
Read More →
Compute Pro rata growth rate within a Pivot Table
{ 0 Comments }