How to Rank Excel Values Based on Their Size Differences
Question details
The user needs to assign ranks or scores to values that accurately reflect the proportional size differences between them, rather than assigning standard consecutive ranks.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a comparison matrix where standard ranking distorts the data because extreme value gaps are mapped to sequential, equally-spaced integers.
- Observed behavior
- The standard RANK function assigns consecutive integers (1, 2, 3...) regardless of how large or small the numerical gap is between the actual values.
Identify the data range containing your values and decide on the scale you want to use for scoring (for example, a 1-to-10 or 0-to-100 scale).
Calculate a Normalized Score Using Min-Max Scaling
Use a Min-Max normalization formula to scale your values into a specific range. This ensures the output score reflects the true proportional differences.
Instead of traditional ranking, you can distribute values proportionally across a custom scale (e.g., 1 to 10). When underlying values jump significantly, the resulting score will also reflect that large jump.
Click on the empty cell where you want the first proportional rank to appear.
Type the formula =1+9*(C17-MIN($C$17:$C$23))/(MAX($C$17:$C$23)-MIN($C$17:$C$23)). Replace C17 with your first data cell, and $C$17:$C$23 with your absolute data range.
Press Enter to calculate the score. Then, click and drag the fill handle at the bottom-right corner of the cell down to apply the formula to the remaining rows.
If your matrix requires whole numbers instead of decimals, wrap your formula in the ROUND function, like so: =ROUND(1+9*(...), 0).
Use Original Values with Conditional Formatting
Instead of converting values to ranks, utilize the raw values and apply visual formatting to represent the spacing naturally.
Calculate Proportional Ranks Easily in WPS Spreadsheet
WPS Spreadsheet provides powerful built-in calculation functions and advanced visualization tools to help you create normalized scores that reflect the exact size differences of your data.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the values you wish to rank.
- 2. Apply the normalization formula: Select a blank cell adjacent to your data and input the Min-Max formula provided above.
- 3. Drag to fill: Use the fill handle to copy the scoring formula down the column.
- 4. Visualize the results: Highlight the new scores, go to the 'Home' tab, click 'Conditional Formatting', and apply Data Bars or Color Scales to see the proportional gaps visually.

Frequently Asked Questions
Why doesn't the standard RANK function account for size differences?
The RANK function is designed strictly for ordinal ranking (e.g., 1st, 2nd, 3rd place). It only looks at the order of the numbers from highest to lowest and completely ignores the mathematical gaps or magnitudes between those numbers.
How can I score values proportionally on a 1 to 100 scale?
Modify the scaling portion of the formula. Use this structure: =1+99*(Cell-MIN)/(MAX-MIN). This sets your lowest value to 1 and your highest value to 100, distributing everything else proportionally in between.
What if I want a descending proportional rank?
To reverse the sequence so the highest value gets the lowest score (e.g., 1) and the lowest gets the highest score (e.g., 10), simply subtract your calculated result from your maximum scale value plus one. For a 1-10 scale, use: =11 - [your normalization formula].
Does WPS Office support these normalization formulas?
Yes, WPS Spreadsheet supports all standard statistical and mathematical formulas, including MIN, MAX, and ROUND. You can seamlessly copy and paste these formulas between Microsoft Excel and WPS Office.




