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 →
Assume a column of data with blanks appearing in a few random cells. One may want to use this range of cells for creating a Validation drop down in a specified cell. After creating the validation drop down, the blanks from the source data range would obviously show up in the drop down as well. The task is […]
Read More →
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 →
{ 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 →
{ 2 Comments }
Assume data in range D6:E11 – months in D6:D11 and numbers in E6:E11. Headings are in D5:E5. On filtering multiple values in column D, one may want to view the filtered criteria in cell (separate criteria by comma for multiple selections in auto filter drop down). For solving this problem, you will have to download and install the morefunc […]
Read More →
{ 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 →
The SUMPRODUCT() function works on all cells in the specified range – whether filtered or not. However, one may want to apply the same function on the visible cells of a filtered range. You may refer to my solution in this workbook.
Read More →
{ 0 Comments }
Given a certain beginning date and specified number of days, one may want to determine the ending date (beginning date plus number of days). When determining the ending date, the following two cases should be considered Case 1 Holidays are: 1. Sundays only; and 2. User specified public holidays Case 2 Holidays user specified public holidays […]
Read More →
{ 8 Comments }
Assume there are two columns with data repeated in all both columns. One may wan to extract the unique text values from a specific column based on conditions specified by end-user. Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem Solution for MS Excel 2010 and higher versions […]
Read More →
{ 4 Comments }
Given a single column with duplicates appearing, one may want to dynamically extract unique values to another range. Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem Solution for MS Excel 2010 and higher versions If you are using the Power Query add-in, then a […]
Read More →
Average a range of numbers with blanks appearing at random intervals
{ 4 Comments }