How to Rank Values in an Excel Row Using the RANK.EQ Function
Question details
The user wants to determine the rank or order of numerical values located in a specific horizontal row by applying a formula in the row below it and dragging it across.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating horizontal rankings for a dataset laid out in a row rather than a standard column.
- Observed behavior
- Needs to output the rank order of each value compared to the entire row of values while keeping the comparison set fixed.
Ensure your dataset contains numerical values and has no blank cells in the row you are evaluating, as empty cells might cause ranking errors or be ignored by the RANK.EQ function.
Use RANK.EQ with Absolute Cell References
Apply the RANK.EQ formula using absolute references for the array so the range remains fixed when copying the formula across multiple columns.
The RANK.EQ function returns the rank of a number within a list of numbers. When working across a horizontal row, locking the evaluation range with absolute references (using the dollar sign '$') is crucial. This ensures the comparison dataset remains static as you copy the formula to the right.
Click on the first cell where you want the rank to appear. For example, click D41 if your first data point is located right above it in D40.
Type the formula =RANK.EQ(D40, $D$40:$M$40) into the formula bar. Replace D40 with your first cell and $D$40:$M$40 with the complete range of your row's data. The dollar signs lock the array range.
Press Enter to calculate the first rank. Then, hover over the bottom-right corner of cell D41 to see the Fill Handle (a small cross icon). Click and drag it horizontally to the end of your data range (e.g., to M41).
Use WPS Office to Handle Complex Data Ranking
WPS Spreadsheet fully supports the RANK.EQ function and hundreds of other advanced formulas. It provides a lightweight, user-friendly interface to analyze, rank, and visualize your horizontal datasets efficiently.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the row data you want to rank.
- 2. Insert the RANK.EQ function: Click on the formula bar, go to the Formulas tab, and select 'Insert Function' to find RANK.EQ, or simply type =RANK.EQ() directly into the result cell.
- 3. Select references and drag: Select the target number, lock your row range using the F4 key on your keyboard to instantly create absolute references, and use the Fill Handle to drag the formula across the row.
Frequently Asked Questions
What is the difference between RANK and RANK.EQ?
In modern versions of spreadsheet software, RANK.EQ is the updated version of the legacy RANK function. Both perform the exact same calculation, assigning the top rank to duplicate values. RANK.EQ is recommended for better compatibility with newer spreadsheet standards.
Why is my formula returning incorrect ranks when I drag it across the row?
This usually happens if you forget to use absolute references (the '$' symbols) for the dataset range. For example, use $D$40:$M$40 instead of D40:M40. Without the dollar signs, the comparison range shifts incorrectly to the right as you drag the formula.
Can I rank the values in ascending order instead of descending?
Yes. By default, RANK.EQ ranks values in descending order (highest value is ranked 1). To rank in ascending order (lowest value is ranked 1), add ', 1' to the end of your formula as the order argument, like this: =RANK.EQ(D40, $D$40:$M$40, 1).
How can I break ties when using RANK.EQ?
To break ties and assign unique ranks, you can combine RANK.EQ with the COUNTIF function. A common formula structure is =RANK.EQ(D40, $D$40:$M$40) + COUNTIF($D$40:D40, D40) - 1. This checks for previous occurrences of the same number and increments the rank dynamically.
