Tags: SUBTOTAL

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 →

Prioritise investment liquidation to minimise Capital Gains

{ 2 Comments }

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

Read More →

Dynamically extract unique values from a filtered range

{ 0 Comments }

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

Read More →

Valuing Closing Stock using FIFO method of Accounting

{ 24 Comments }

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

Read More →

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

{ 0 Comments }

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

Read More →

Shade alternate band of rows in a filtered range

{ 2 Comments }

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

Read More →

Sum visible cells of a filtered range ignoring errors

{ 4 Comments }

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

Read More →

Average a range of numbers with blanks appearing at random intervals

{ 4 Comments }

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

Read More →

VLOOKUP() function to work only on visible cells of filtered range

{ 12 Comments }

The VLOOKUP() function returns data from a lookup_array irrespective of the filter setting of the lookup_array.  To make the lookup_array of the VLOOKUP() function work only on the visible cells of a filtered range, refer to this workbook.

Read More →