# Tags: OFFSET

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 […]

Assume a five column dataset with the Fund Name in column A, Market Value in column B, Gain/Loss in column C, Gain/Loss % in column D [column C/column B] and Type of investment in column E (Short term or Long term).  Since multiple investments can be made in one fund, there can be multiple line items […]

Assume date wise revenue data has been shown on two sheets – Sports wear and Fitness equipment (Data worksheets).  On each of these two worksheets, there are two columns – Date and Revenue.  On the Summary worksheet, the user would like to summarise data (Count instances and sum revenue figures) based on multiple conditions.  The […]

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 […]

Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices.  On another sheet, the closing stock of each Raw material type is entered.  In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued […]

Assume data in range B4:B18 (heading is in cell B3).  To compare the value in the current row with the value in the row below, one may enter the following formula in cell C4 and copy down =B4=B5 True’s indicate matching values with the value in the next row.  However, the above formula would fail when […]

Assume data in range A3:D8 (headings are in A3:D3).  One may apply an auto filter on any column(s).  The task is to count the blank cells in visible rows of range C4:C8 and D4:D8. You may refer to the data set and my solution in this workbook.