Assume a two column database in range A3:B6. Headings are in A2:B2. In range A3:A6 are numbers, say 60,30,20 and 10. In range B3:B6 are some names, say Ashish, Sameer, Gautam and Sanjay. In cell A7, enter any name, say Sameer. The task is to extract the corresponding number from the range (A3:B6) into cell B7. […]
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 →
Assume the following data on a worksheet: 1. Cell B3 – Tenure of loan (in years). This will be a whole number, say 15 2. Cell B5 – Escalation (in percentage), say 10% 3. Cell B6 – Escalation period (in months), This will be a whole number, say 24. This means that the escalation will […]
Read More →
{ 4 Comments }
In cell B6, a user can select one of the following entries from a validation drop down list – Reason1, Reason2 and Reason3. If the user selects either Reason1 or Reason2, a dependent validation drop down lists should appear in cell C6. However, if the user select Reason3 in cell B6, the dependent validation list from cell C6 should […]
Read More →
{ 0 Comments }
In range C4:C10, assume the following values/formulas 1. Cell C4 – 1 2. Cell C5 – 2 3. Cell C6 – W 4. Cell C7 – We 5. Cell C8 – Empty cell 6. Cell C9 =IF(D9+E9=2,”a”,””). Please ensure that cells D9:E9 are empty 7. Cell C10 =1/0. This will yield the #DIV/0! error To count numbers […]
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 →
{ 74 Comments }
In this file, the source data sheet is a pasted special pivot table. The task is to convert this pivot table data layout to the result shown in the “Desired result” sheet. Effectively, we need to convert a matrix like data layout to a tabular layout. We need to “denormalise” the pivot table. Solution for […]
Read More →
Assume a folder on the desktop named Test. Within this folder there is a another folder named Monthly Data. Within this folder are MS Excel files received every month. The number of columns in each file is not the same. Therefore in the January .xlsx file, there would be 100 columns, in the February.xlsx file, there could […]
Read More →
{ 10 Comments }
Assume a matrix like data layout where Site’s are mentioned in range C2:C15 and first day of every month from January 2011 till April 2012 are mentioned in range D1:S1. In range D2:S15, the numbers represent the value of the electricity bill received in that month. Some cells can be blank representing bills not received in that […]
Read More →
{ 227 Comments }
Assume a folder named Test on the desktop. In this Test folder, there is another folder named Survey reports. In the Survey report folder, there are multiple Excel files. All the Excel files have the same structure i.e. if there is an Operator Names in cell C9 of one Excel file, then in other Excel files as […]
Read More →
Return an exact value via the LOOKUP() function
{ 8 Comments }