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


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:

  1. Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers
  2. Identify the largest 5 of those 10 numbers
  3. Sum those largest 5 numbers

Here are the steps

  1. Suppose the numbers and blanks are in range A2:V2
  2. Type 10 in cell X1
  3. Enter this array formula (Ctrl+Shift+Enter) in cell X2


Prioritise investment liquidation to minimise Capital Gains


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 for with the same Fund Name (with different market values).  Let's call this dataset the Investment schedule.

The objective is to determine the following:

a. Minimum total gain realised from sale of one/multiple funds; and
b. Short term gain present in a above; and
c. Total Long term gain realised from sale of one/multiple funds; and
d. If Long term investments fall for liquidating the particular fund, then how much is the gain/loss by forcible liquidating the short term investments.

You may refer to my solution in this workbook.

Dynamically extract unique values from a filtered range


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 downwards).  Data from cell A18 downwards should keep changing with different filter conditions specified in the Auto filter drop down of column A.

You may refer to my solution in the this workbook.

To extract unique values from an unfiltered range without any conditions, you may refer to the following link

To extract unique values from an unfiltered range with multiple conditions, you may refer to the following link

Valuing Closing Stock using FIFO method of Accounting


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 is from the oldest stock lying in inventory i.e. the inventory left over will be from the recent stock purchased.

The objective is to determine the value of the closing stock of Raw material on FIFO basis.

You may refer to my solution in the attached workbook.

Compare value of one cell with value of next visible cell of a filtered range


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


True's indicate matching values with the value in the next row.  However, the above formula would fail when an auto filter is applied to the range.

To compare the value in the current row with the value in the next visible row below, refer to my solution in this workbook.

Shade alternate band of rows in a filtered range


Assume the following numbers in range C5:C22.  Heading is in cell C4.

Range C5:C7 - 11
Range C8:C11 - 14
Range C12:C13 - 23
Range C14-C19 - 56
Range C20-C22 - 78

One may want to colour cells for every change in number in range C5:C22 with the following two modifications:

1. Shading should be for alternate band of rows i.e. C5:C7 should be coloured, C8:C11 should not be coloured, C12:C13 should be coloured, C14:C19 should not be coloured and C20:C22 should be coloured.

2. Shading should vary with the range being filtered i.e. If range C5:C22 is filtered to exclude 14 and 56, then numbers 11 and 78 should be coloured i.e. range C5:C7 and C20:C22 should be coloured.

To accomplish this, try the following:

a. Select cell C5 and go to Home > Format > Conditional formatting > New Rule > Use a formula to determine which cells to format

b. In the formula box there, enter the following formula


c. Click on Format and select any Fill colour

d. Click on OK > OK > Apply

e. Copy cell C5, select range C6:C22 and right-click > Paste Special > Formats

With no filter in place, colouring will happen as mentioned in point 1 above.  Auto filter the range now on some values and the colour banding should change.

Sum visible cells of a filtered range ignoring errors


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, the SUM() function would also include the invisible rows of a filtered range.  The SUBTOTAL(9,range) function would not work either because the range in the SUBOTAL() function should also be error free.

Assume that the database is in range A11:B20.  Column A has been filtered and column B is the column of numbers which also has the error values.

For Excel 2007 and prior versions, the following array formula (confirmed by Ctrl+Shift+Enter) will sum the visible rows a filtered range ignoring error values.


For Excel 2010 and higher versions, the following formula will work


Average a range of numbers with blanks appearing at random intervals


With blanks appearing in a range of numbers, one may want to:

1. Average the first n numbers; and/or
2. Sum the last n numbers (in a horizontal and vertical range)

Because of blanks, the range for averaging will need to automatically keep expanding till the nth number is reached.

You may refer to my solution in this workbook.