Tags: COLUMN

Sum the largest 5 of the last 10 numbers in a row ignoring blanks

{ 2 Comments }

Assume a single row of data with numbers and blanks appearing at random intervals.  The objective is to sum the largest 5 of last 10 numbers in that row.  Solving this problem entails multiple steps: Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers […]

Read More →

LOOKUP unique data from multiple columns where search string appears multiple times

{ 2 Comments }

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 →

Compute monthly asset amortisation expense

{ 0 Comments }

In range B3:V3, one will record Purchase price of various Assets purchased in different months.  In the next two rows one specifies whether the Assets purchased in that month have to be amortised or not and the months over which the Assets has to be amortised respectively. The task is to determine the amount to […]

Read More →

Sum up diagonal cells in a range

{ 0 Comments }

Assume there are numbers typed in range B3:G8.  In row 10, one may want to sum up diagonal cells i.e. in cell B10, one would want to get the value from cell B8, in cell C10, one may want to sum values from cells B7 and C8.  in cell D10, one may want to sum […]

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 →

Determine the maximum number of consecutive 1’s appearing in a range

{ 21 Comments }

Assume a database where customers are listed from cell A6 down.  From cell B5 to the right months are entered from April to March (B5:M5).  In B6:M6 (Customer 1), a user enters 1’s and 0’s.  A value of 1 respresents “Cheque bounced” and 0 represents “Cheque honoured”.  Similar data is entered for other customers in B7:M500. […]

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 →

Summarise data from multiple sheets with one condition

{ 6 Comments }

Assume there are ten worksheet tabs – one for each retail store.  Each worksheet tab has sale data of different brands across 12 months – brands are arranged in rows and months are arranged in columns.  The structure of all worksheets tabs is the same.  Furthermore, each store is managed in any of the two retail […]

Read More →