Biggest Gainers And Losers

Explanation

In this example, the goal is to display the biggest 3 gainers and losers in a set of data where Start and End columns contain values at two points in time, and Change contains the percentage change in the values. The data in B5:E16 is defined as an Excel Table with the name data . Two formulas are required, one to return the top 3 gainers in the table, and one to return the top 3 losers. The primary component of the solution is the FILTER function, which is used to extract data that meets specific logical conditions from the table.

If you are new to FILTER, see this video: Basic FILTER function example

Top 3 gainers

In the example shown, the formula to return the top 3 gainers in cell G5 is:

=SORT(FILTER(data,data[Change]>=LARGE(data[Change],3)),4,-1)

Working from the inside out, the first task is to extract the three rows from the data that have the largest change values. To do this, we use the FILTER function together with the LARGE function like this:

FILTER(data,data[Change]>=LARGE(data[Change],3))

Inside FILTER, the array argument is the entire table, since we want to return all four columns. The include argument is a logical test based on the LARGE function:

data[Change]>=LARGE(data[Change],3)

The LARGE function is configured to return the third largest value in the Change column:

LARGE(data[Change],3) // get 3rd largest value

With 3 given for k , LARGE returns the 3rd largest value .02699 (2.699%). We can then simplify the FILTER function to the following:

FILTER(data,data[Change]>=0.0269)

Essentially, we are asking FILTER for rows in the table where change is greater than or equal to 0.0269. FILTER returns the 3 matching rows in an array like this:

{"JOF",111.63,117.546,0.053;
"HHB",8.104,8.323,0.0269;
"XXO",43.124,47.048,0.091}

Note: values have been rounded for readability.

At this point, we have the data we want, but it is not sorted. Since the goal is to show the biggest gainers first, we need to sort the array in descending order by Change. To do this, we use the SORT function directly on the array returned by FILTER:

=SORT(filtered_array,4,-1)

This is an example of nesting - the result from FILTER is delivered as an array inside the SORT function. Since the change column is the last column, sort_index is given as 4, and sort_index is provided as -1 to sort in descending order. Finally, the SORT function returns the final sorted array to cell G5, which spills into the range G5:J7.

Video: Basic SORT function example

Top 3 losers

Next, we need to list the biggest 3 losers. In the worksheet shown, the formula in G12 is:

=SORT(FILTER(data,data[Change]<=SMALL(data[Change],3)),4,1)

The first task is to extract the three rows from the data that have the smallest change values. To do this, we use the FILTER function together with the SMALL function like this:

FILTER(data,data[Change]<=SMALL(data[Change],3))

The array argument in FILTER is given as the table name data , since we want to return all four columns. The include argument is supplied as a logical expression that targets the three smallest values in Change:

data[Change]<=SMALL(data[Change]

The SMALL function returns the third largest change in the table:

SMALL(data[Change],3) // get 3rd smallest value

With 3 as k , SMALL returns -0.0671 (-6.7%). We can now simplify the FILTER function to this:

FILTER(data,data[Change]<=-0.0671)

We are asking FILTER for rows in data where Change is less than or equal to -0.0671. FILTER returns the 3 matching rows in an array like this:

{"EYN",7.673,7.158,-0.067;
"YOL",17.492,16.058,-0.082;
"DPP",4.067,3.790,-0.068}

Note: values have been rounded for readability.

We have the data we want, but it is unsorted. The goal is to show the biggest losers first, so we want to sort the array in ascending order by change. To do this, the FILTER function is nested inside the SORT function , and the result from FILTER is delivered as array:

=SORT(filtered_array,4,1)

Sort_index is given as 4, since change is the fourth column, and sort_index is provided as 1 because we want to sort ascending order, starting with the largest negative change. Finally, the SORT function returns the final sorted array to cell G12, which spills into the range G12:J14.

Note: this formula will always return the three rows in the data with the smallest change values, whether these values are negative or not. The label “losers” may not make sense in a small set of data with no negative change values.

Explanation

The goal is to combine data from different worksheets with a formula. Note that we are not restructuring the data in any way, we are simply combining data in different worksheets that already have the same structure. At a high level, the formula we are using combines data from multiple sheets with the VSTACK function and removes empty rows with FILTER. The data in the workbook comes from 3 separate worksheets (Sheet1, Sheet2, and Sheet3) and is combined in another worksheet named Summary. The Diagram below provides an overview:

Notice that the range in the formula below is fixed as B5:B16 and some of the rows in this range are empty on each worksheet. This means the solution should also provide a way to remove empty rows in the final result. In the worksheet example above, the formula used to solve this problem in cell B5 looks like this:

=LET(data,VSTACK(Sheet1:Sheet3!B5:E16),FILTER(data,CHOOSECOLS(data,1)<>""))

Let’s look at how this formula works step-by-step. The first step is to combine the data, which is done with the VSTACK function.

Note: in this example, we are working with a tiny amount of data in a small number of worksheets so that the problem is easy to understand. However, the same approach will work with larger sets of data in many more worksheets.

Combining data with VSTACK and a 3D reference

The first step in this formula is to combine the data in three separate ranges on three separate sheets. When you have data in “normal” ranges (i.e. data not in a named range or an Excel Table) at a predictable location on multiple worksheets, a nice approach is to use the VSTACK function with a 3D reference like this:

=VSTACK(Sheet1:Sheet3!B5:E16)

The reference “Sheet1:Sheet3!B5:E16” is called a “3D reference”. It points to the range B5:E16 on Sheet1 through Sheet3 which, in this case, is Sheet1, Sheet2, and Sheet3. VSTACK retrieves data from the range B5:E16 on each sheet and stacks each range on top of another vertically. This is the core of the formula — the part that does the actual data consolidation.

Note: If you are new to the idea of a 3D reference, this video provides an overview .

This works well, as you can see in the screen below. However, one problem when using a fixed range like this is that the output may contain empty rows, which also appear in the output:

To remove the extra rows, we can use the FILTER function. However, before we do that, it makes sense to store the output from VSTACK in a variable with the LET function. The reason we do this is because we are going to use the same data twice in FILTER. By using a variable, we only need to run the VSTACK operation one time.

LET function

The LET function allows you to name variables inside a formula. This makes more complex formulas easier to read and write. It also improves performance since certain operations only need to run one time. In this case, we use LET to name the output from VSTACK “data” like this:

=LET(data,VSTACK(Sheet1:Sheet3!B5:E16),

VSTACK runs as explained above and the result (the combined ranges) is stored in the variable “data”. The next step is to remove the empty rows. For that, we’ll use the FILTER function.

Removing empty rows with FILTER

As seen in the screen above, the result from VSTACK (now stored in the variable data ) contains empty rows. To remove these empty rows from the final result, we use the FILTER function and the CHOOSECOLS function together like this:

FILTER(data,CHOOSECOLS(data,1)<>"") // remove empty rows

Here, the array in FILTER is provided as data , which was created by LET in the previous step. The logic to remove empty rows is based on the CHOOSECOLS function:

CHOOSECOLS(data,1)<>"" // test column 1

CHOOSECOLS is designed to retrieve one or more specific columns from a larger set of data. In this case, we are asking CHOOSECOLS for the first column in data . Then we use a logical expression to test for “not empty” cells in the column. The result is an array of TRUE and FALSE values, one per row. This array is returned to FILTER as the include argument. When FILTER applies this array of TRUE and FALSE values to data, only the rows associated with TRUE values are returned. The rows associated with FALSE values are discarded. The final result on the summary sheet looks like this:

Note: we are only testing the first column (Date) for empty rows. If you need a more robust test for empty rows, see this example .

Without the LET function

It isn’t necessary to use the LET function for this formula, it’s just more efficient. Here is the same formula without LET:

=FILTER(VSTACK(Sheet1:Sheet3!B5:E16),CHOOSECOLS(VSTACK(Sheet1:Sheet3!B5:E16),1)<>"")

The overall structure is the same, but notice that we need to run the VSTACK operation twice inside FILTER. With larger amounts of data, this will slow the formula down.

Without a 3D reference

The 3D reference is useful in this problem because the ranges on each sheet are at the same location. As long as this remains true, you can easily expand the 3D reference to include additional sheets. However, there is no requirement that you use a 3D reference. You could refer to each range separately inside VSTACK like this:

=VSTACK(Sheet1!B5:E16,Sheet2!B5:E16,Sheet3!B5:E16)

This works fine, but the approach won’t scale well as you add a larger number of sheets.

Using the combined data

Once you have combined the data with the formula above, you can use it normally in other functions. For example, to sum the Total for all rows with a color of “Red”, you can use a formula like this:

=SUMIFS(E5:E16,C5:C16,"red")

You can also refer to the spill range B5# to refer to the combined data. For example, to return all rows that have a color of “Red” you can use FILTER like this:

=FILTER(B5#,C5:C16="red")

With Excel Tables

If you are combining data that exists in Excel Tables , this problem is much easier. For example, with the data in this example in three tables (Table1, Table2, and Table3) we can combine the data with a simple VSTACK formula like this:

=VSTACK(Table1,Table2,Table3)

There is usually no need to remove empty rows because Excel Tables automatically expand and contract to fit the data they contain. This means we don’t need to use any fancy tricks to figure out how many rows to retrieve, or how many empty rows to remove — we can simply combine data in the tables with VSTACK.

Data in external workbooks

It is possible to combine data in separate external workbooks with VSTACK like this:

VSTACK(Sheet1.xlsx!B5:E16,Sheet2.xlsx!B5:E16,Sheet3.xlsx!B5:E16)

Or, with a 3D reference to a single workbook, like this:

VSTACK([workbook.xlsx]Sheet1:Sheet3!B5:E16)

Note: I have only tested the external links on a single local drive, so results may vary in different environments.