## 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.*