Dynamic Arrays
Biggest gainers and losers formula: extract Top N & Bottom N movers (SORTBY & TAKE)
Rank the largest and smallest percentage changes without reordering the source table. Distinguish top/bottom rankings from positive-only gainers and negative-only losers.
Turn raw period-over-period tables into automated Top Movers & Laggards panels
For each SKU, divide the change in value by its prior value. This example assumes positive numeric prior values and numeric current values. A zero prior value has no defined percentage change; correct or exclude it before ranking.
TAKE(SORTBY(A2:D6,D2:D6,-1),F2) returns the highest changes; sort order 1 returns the lowest. These formulas rank all rows: the bottom list can still contain gains, and the top list can contain losses. For strictly positive or negative lists, filter by the sign before sorting.
Enter a positive whole number from 1 to 5 in F2. Equal changes have no explicit secondary ranking here; add a secondary sort key when a reproducible tie policy matters.
| Ticker / SKU (A) | Prior (B) | Current (C) | % Change (D) | Movers Rank |
|---|---|---|---|---|
| SKU-Nova | $100 | $138 | +38.00% | #1 Biggest Gainer! |
| SKU-Apex | $200 | $244 | +22.00% | #2 Gainer |
| SKU-Core | $150 | $153 | +2.00% | Mid-pack |
| SKU-Bolt | $120 | $96 | -20.00% | #2 Loser |
| SKU-Vega | $80 | $52 | -35.00% | #1 Biggest Loser! |
Return the complete Top N table
sort_order = -1 sorts largest % gains to the top before TAKE slices the top F2 rows.
=TAKE(SORTBY(A2:D6,D2:D6,-1),F2)Extract #1 Biggest Loser (sort_order = 1 ascending)
Sorting ascending places the steepest negative % decline (-35.00%) in row 1.
=INDEX(TAKE(SORTBY(A2:D6, D2:D6, 1), F2), 1, 1)Check the sign before calling a result a loser
If all values in Current increase, the ascending list identifies the smallest gains, not losses. The final check changes all current values to twice their prior values and verifies the lowest change is positive.
Try the example
Use the original values or change them to test your understanding.