Function Problems

How to Create a Custom Ranking Formula with RANK.EQ in Excel

Maira Mehtab Sep 22, 2026 870 views

Question details

The user wants to create a custom Excel ranking format that converts standard numerical ranks (e.g., 1 through 15) into specific decimal groupings, such as 1.1, 1.2, 2.1, up to 3.5.

Product: Excel
Device & OS: not provided
Scenario: Grouping and formatting ranks into custom decimal increments, where every five ranks are categorized under a new whole number.
Observed behavior: Standard Excel ranking functions output standard integer ranks (1, 2, 3), but the goal is to transform these into a customized decimal format using a formula.

Before you start

Ensure your dataset contains numerical values to be ranked and check for any potential duplicate values, as tied numbers will receive the same rank and affect the final custom decimal formatting.

Solution 1Recommended

Use a Combined Formula with RANK.EQ, INT, and MOD

Combine the RANK.EQ, INT, and MOD functions to directly output the custom decimal ranking format in a single cell.

This method calculates the rank and formats it simultaneously. By grouping every five ranks under a major number (e.g., ranks 1-5 become 1.1 to 1.5), you can easily categorize and segment large datasets.

Select the destination cell

Click on the cell where you want the first custom rank to appear (for example, B2).

Enter the combined formula

Type the formula `=INT((RANK.EQ(A2,$A$2:$A$16)-1)/5)+1&"."&MOD(RANK.EQ(A2,$A$2:$A$16)-1,5)+1` into the formula bar and press Enter.

Apply to the remaining rows

Click and drag the fill handle at the bottom-right corner of the cell down to apply the formula to the rest of your dataset.

Handling Duplicates: Tied ranks will produce the exact same custom decimal output. Review your data if strict unique sequential rankings are required.

Solution 2

Calculate Rank and Format Separately Using a Helper Column

Calculate the base rank in one column and convert it to the custom format in another for easier formula troubleshooting.

Efficient Data Ranking

Use WPS Spreadsheet for Advanced Formula Calculations

WPS Spreadsheet fully supports RANK.EQ, INT, and MOD functions, allowing you to create custom ranking formats just like in Microsoft Excel. It offers a smooth and responsive experience for complex data analysis without any cost.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the numerical data to be ranked.
2. Input the formula: Select the target cell and paste the combined RANK.EQ formula to generate the custom ranks.
3. Fill down the series: Double-click the fill handle in the lower right corner of the cell to apply the formatting to all corresponding rows automatically.

Fully compatible with Microsoft Excel formulas and formatsSeamless processing of large datasets without lagFree, lightweight, and easy to use across Windows, Mac, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

What happens if there are duplicate values in my data when using RANK.EQ?

The RANK.EQ function assigns the same top rank to duplicate values. For example, if two values tie for 3rd place, both will receive rank 3, and the next sequential value will receive rank 5. This will result in identical custom decimal formats for the tied values.

How can I change the group size from 5 to 10 in the custom rank?

To group every 10 ranks instead of 5, replace the number 5 with 10 in both the INT and MOD sections of your formula: `=INT((RANK.EQ(A2,$A$2:$A$16)-1)/10)+1&"."&MOD(RANK.EQ(A2,$A$2:$A$16)-1,10)+1`.

Is RANK.EQ the same as the older RANK function?

Yes, RANK.EQ is the updated version of the older RANK function in modern spreadsheet software. It calculates the rank in exactly the same way but is the recommended standard for current and future compatibility.

Excel & Data Problems