How to Return Top Three Results Including Ties in Excel
Question details
The user needs to create a dynamic formula to extract the top three distinct values from a dataset, while ensuring all rows tied at the third-highest value are included in the results.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an automated leaderboard or top-ranking list using dynamic array formulas to filter dataset values.
- Observed behavior
- Dynamic array results successfully spill into worksheet cells but are incompatible with the body of a formal Excel Table format.
Ensure you have a recent version of Excel or a compatible spreadsheet software that fully supports dynamic array functions such as FILTER, SORT, and UNIQUE.
Use FILTER, SORT, and UNIQUE to Extract Top Results with Ties
Combine these dynamic array functions inside a LET function to sort your data, identify the third-highest unique threshold, and filter out all rows meeting that criteria.
This formula approach calculates the ranking dynamically. It evaluates the total values, finds the precise value of the third-highest distinct entry, and returns every row that equals or exceeds this amount.
Because this utilizes dynamic arrays, the output will automatically expand (spill) into adjacent blank cells. Remember that spilled-array formulas cannot be used directly inside an official Excel Table.
Click on an empty cell in a standard worksheet range (outside of any formatted Excel Table) where you want the top results to start displaying.
Type the following formula: =LET(M, SORT(DemoTbl,2,-1), total, TAKE(M,,-1), top, INDEX(UNIQUE(total),3), FILTER(M,total>=top)). Replace 'DemoTbl' with your actual data range, and adjust the column index '2' based on where your numerical values are located.
Press Enter. The formula will calculate the threshold and spill the filtered results, showing your top three distinct values and all corresponding ties.
Easily Filter Top Data and Handle Ties with WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like FILTER, SORT, and UNIQUE. You can effortlessly build automated reports that rank data and handle ties without manual sorting.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing the data you want to rank.
- 2. Select an output range: Click on a blank cell in a standard worksheet area with enough space for the results to spill downwards.
- 3. Input the dynamic formula: Enter the combined LET and FILTER formula to define your data range and sort criteria.
- 4. Generate the ranking: Press Enter to instantly retrieve the top three distinct results, including all tied values.
Frequently Asked Questions
Why do I get a #SPILL! error when executing this formula?
A #SPILL! error typically occurs if there is existing text or data blocking the cells where the formula needs to display its results. It also happens if you try to place a dynamic array formula directly inside the body of an Excel Table. Ensure the output area is completely empty and outside of any structured Table.
Can I modify the formula to return the top 5 results instead of the top 3?
Yes. In the formula segment 'top, INDEX(UNIQUE(total),3)', simply change the number 3 to 5. This tells the INDEX function to locate the fifth-highest distinct value, returning the top five results along with any ties.
How can I rank based on the lowest values instead of the highest?
To extract the bottom results, change the sort order in the SORT function segment. Change the -1 (descending order) to 1 (ascending order). The formula will then sort the values from lowest to highest and return the bottom three unique entries plus ties.
