How to Show the Top or Bottom Five Regions in WPS Office Excel
Question details
The user needs a dynamic Excel formula that displays the top five or bottom five performing regions based on a user selection, without manually editing the formula each time.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Analyzing regional performance and switching between the best and worst performers using a single cell selection.
- Observed behavior
- The goal is to have one formula that automatically returns either the top five or bottom five regions depending on the value in a switch cell.
Ensure your source data has region names in one column and numeric performance values in the adjacent column. Confirm the range you will reference, such as A1:B18, and leave an empty switch cell such as G1.
Use TAKE and SORT to Display Top or Bottom Five Regions
Use this dynamic array formula when you want one formula to switch between the five highest and five lowest performing regions based on a cell value.
This formula sorts the selected range A1:B18 by the second column. TAKE then returns the last five rows for top values when G1 equals 1, or the first five rows for bottom values when G1 equals 2.
Click an empty cell where you want the dynamic list of top or bottom five regions to appear.
Type =TAKE(SORT(A1:B18,2),IF(G1=1,-5,IF(G1=2,5))) and press Enter. Replace A1:B18 with your actual region and values range.
Enter 1 in cell G1 to show the top five regions, or enter 2 to show the bottom five regions.
If your numeric values are not in the second column of the selected range, change the 2 in SORT to the correct column number within the range.
Display Top or Bottom Five Regions with a Dynamic Formula in WPS Office Excel
WPS Office Excel can directly create a dynamic formula that returns the top five or bottom five regions based on a switch cell. Use the same spreadsheet skills and formulas you already know, with full compatibility for Excel workbooks.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office and open the workbook that contains the region and performance data.
- 2. Enter the dynamic formula: Click an empty cell, type =TAKE(SORT(A1:B18,2),IF(G1=1,-5,IF(G1=2,5))), and press Enter.
- 3. Switch between top and bottom: Type 1 in cell G1 for the top five regions or 2 for the bottom five regions.
- 4. Save the workbook: Save the file as .xlsx to keep the dynamic formula working in WPS Office and Microsoft Excel.
Frequently Asked Questions
Does this formula work in all versions of WPS Office Excel?
It requires dynamic array functions TAKE and SORT. If your WPS Office Excel version supports dynamic arrays, the formula works directly. If you see #NAME?, update WPS Office or use an alternative with INDEX and SORT.
How can I change the number of rows from five to ten?
Replace -5 and 5 in the IF function with -10 and 10, or reference a cell that contains the number of rows you want to return.
What if my values are in column C instead of column B?
Select the range including the values, such as A1:C18, and change the SORT index from 2 to 3 because the numeric values are in the third column of the selected range.
Can I use a different cell instead of G1 as the switch?
Yes, update the formula to reference the cell you prefer. For example, replace G1 with H1 throughout the IF function.
