where data is the Excel Table in B5:E16. A similar formula in G12 returns the 3 biggest losers. These results are dynamic and will immediately update if data changes. Read below for a detailed explanation of both formulas. Note: Dynamic Array Formulas are only available in Excel 365 and Excel 2021. 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: 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: 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: The LARGE function is configured to return the third largest value in the Change column: With 3 given for k, LARGE returns the 3rd largest value .02699 (2.699%). We can then simplify the FILTER function to the following: 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: 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: 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: 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: 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: The SMALL function returns the third largest change in the table: With 3 as k, SMALL returns -0.0671 (-6.7%). We can now simplify the FILTER function to this: 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: 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_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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
