Getting the Top N results from a list of data, such as a Top 10 list is a very common Excel request. While this was possible before dynamic arrays, it has been made much easier using dynamic arrays.
In this example, we will be using random data to simulate scores of people playing a game.
The formulas we will use include:
- SEQUENCE()
- RANDARRAY()
- SORT()
- FILTER()
- LARGE()
Other than LARGE(), these functions are only available in Office 365.
Getting Started
Before getting into the heavier Excel formulas, we start by setting up the number of players. We also set up the number of top scores to get using our top n formula.
There is no need to name the ranges and no formulas are required at this point.
Setting up the Player Scores
In this example, we will be creating random data for players. Their names will not be provided, they will just be named “Player 1”, “Player 2”, etc.
Creating the Player Names – SEQUENCE()
The player names will be created using the SEQUENCE() function, which creates an array of numbers, which can be used to create a sequential list of player numbers.
=SEQUENCE(rows, [columns], [starting number], [step multiplier])If you are just looking for a list of 100 player numbers, you can use:
=SEQUENCE(100)To make this more readable, we will add the word “Player” before the number.
="Player " & SEQUENCE(100)Finally, we’ll refer to the cell containing the number of players instead of using a fixed number. Note that you don’t need to use absolute ($) referencing!
Creating the Scores – RANDARRAY()
Scores in this example will be created using a list of random numbers. The formula used to generate this data is the RANDARRAY() function.
=RANDARRAY(rows, columns, minimum, maximum, integer)For this example, we will be using the following parameters:
- The number of rows will be the number of players.
- All data will be in 1 column.
- The minimum player score will be 100 points.
- The top player score will be 10,000 points.
- The score must be an integer.
Assuming that the score is in 100 point increments, you can round this result to the nearest hundred by using the ROUND() formula.
=ROUND(number, -2)
Rounds value to the nearest hundred.
Getting the Top N Scores
There is only one formula entered into cell E5 that is required in order to get the top n scores from the random data!
However; this is a complex formula! Let’s break this formula down by each formula within the larger formula.
=SORT(FILTER(A5#:B5#,B5#>=LARGE(B5#,F2)),2,-1)Getting the n-th largest value – LARGE()
The first formula that will be investigated is the LARGE() formula. This formula provides the value that is at a specific rank within an array of values.
=LARGE(an array of values, the rank of the value within the array)If we refer to only the cell containing the RANDARRAY() formula, it will return an error!
=LARGE(B5, 10)
In order to correct this, we add a hashtag after the cell name (#); this allows the formula to refer to the entire range.
=LARGE(B5#, 10)
This formula returns the 10th largest value – in order to modify the formula to refer to top n vs. top 10, simply change the number to the value in the “top scores to get” cell.
=LARGE(B5#, F2)Getting the Top N values – FILTER()
Now that we have a value that all other values will need to be under, we will use a function to include only relevant values.
This function is the FILTER() function.
=FILTER(array, true / false array, [value to return if empty])For the first array, this should include both rows. This can be accomplished by using both the hashtag (to return an array) and a colon (to combine 2 arrays).
=A5#:B5#
The true / false array will return a TRUE value in cases where a condition is met. In the case of our top n check, we want cases our value column (B5#) is greater than the nth largest value (as created by the LARGE() function).
B5#>=LARGE(B5#,F2)
Finally, combine the data to filter with the true / false array to get the top n values.
=FILTER(A5#:B5#, B5#>=LARGE(B5#,F2))
The final step is to sort these top values from top to bottom!
Sorting the values – SORT()
The final step is to sort the values in descending order. This can be accomplished using the SORT() function.
=SORT(array, [sort column number], [sort order], [row or column])In this case:
- The array is our list of sorted values.
- The column number is 2; this represents the column with the values (if you wanted to sort by player number, you could use 1).
- The sort order is descending (-1).
- There is no need to add the final argument.
This brings us back to the final formula, which will be in cell E5.
=SORT(FILTER(A5#:B5#,B5#>=LARGE(B5#,F2)),2,-1)
To test with the top 5 values, change the value in F2 to 5. Note that since the values are random, all the values on the sheet will recalculate!
Conclusion
This technique can be used to generate value lists on dashboards – there is no need for the values to be random!
If the values are coming from a single, unchanging source, and there is no need to change the number of values to return, it can be better to use Power Query.