Tags: LOOKUP

Compute rent payable over the contact period after factoring in escalations

{ 0 Comments }

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 →

Apportion a number over empty cells

{ 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 →

Create charts on different sheets by clicking a button

{ 6 Comments }

Assume a five column database with the first column containing codes, second one containing a separator, third one containing some alpha values, fourth one is a combination of the first three columns and the last column has response times. One may want to create as many charts as there are codes in column A.  The charts […]

Read More →

Extract n th. most frequently occurring item from a database

{ 2 Comments }

Assume a database in range D5:D11 with the following values – Black, Black, Purple, Purple, Grey, Grey, Grey.  To extract the most frequently occurring item, one may use the following formula =INDEX($D$5:$D$11,MODE(MATCH($D$5:$D$11,$D$5:$D$11,0))) To extract the second most frequently occurring item, try the following: 1. Enter 2 in cell C16: 2. In cell D16, enter the […]

Read More →

Summarise data from multiple sheets with one condition – PartII

{ 2 Comments }

Assume a worksheet which has scores (1-10) on four future dates on nine questions.  Each row represents responses for one question.  Likewise there are 20 respondents i.e. 20 worksheets which have the same structure. One may want to summarise data from all 20 worksheets into one worksheet.  The question and solution are more clearly explained in the attached […]

Read More →

Dynamically transpose data after ignoring blank cells

{ 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 →

List down most frequently appearing names in descending order of frequency

{ 0 Comments }

Given a list of names in a worksheet range, one may want to pull out, the most frequently appearing names in descending order of frequency, to another range.  Furthermore, one may want to provide an additional date criteria as well. You may refer to my solution in this workbook.

Read More →

Compute Day Sales Outstanding (DSO)

{ 46 Comments }

A financial concept, Day Sales Outstanding (DSO), is a measure of the average number of days that a company takes to collect revenue  after a sale has been made.   For further clarification, read here. Herein, i have presented the solution for two different data layouts.  Further, Layout 2 has two further divisions – one for computing […]

Read More →

Extract data from unknown lookup range

{ 14 Comments }

The VLOOKUP() function accepts a definite lookup array (second input in the VLOOKUP()) specified by the end-user.  In a situation where the lookup array is unknown, a function may have to cycle through various lookup ranges to cull out the required value. You may refer to my solution in this workbook.

Read More →

Extract numeric data and dates from string

{ 12 Comments }

In column A, there are some alphanumeric entries.  Somewhere in the alphanumeric entries (beginning, middle or end) there are the following: 1. Dates 2. Numbers (without decimals) 3. Numbers (with decimals) Furthermore, there may or may not be spaces between numbers/dates and text entries.  In Excel 2010 and prior versions, the only way to solve this problem would […]

Read More →