Function Problems

How to Assign Points by Rank with Tied Scores in Excel

Natalie Taylor Oct 9, 2026 869 views

Question details

The user needs to distribute specific point values based on score rankings (e.g., 12 points to the lowest score down to 1 point for the highest), ensuring that tied scores receive the exact average of their rank positions so the total points remain exactly 78.

Assigning Points by Rank in Excel With Tied Scores

Product: Excel
Device & OS: not provided
Scenario: Calculating competition points, grading, or league tables where ties must share the average points of their respective rank positions without altering the overall points pool.
Observed behavior: Need a reliable formula that ranks scores in ascending order, handles identical ties by averaging their points, and correctly calculates the final assigned points per participant.

Before you start

Ensure your data range is continuous, formatted as numbers, and does not contain empty cells or text values, as this can cause ranking functions to return #VALUE! or #N/A errors.

Solution 1Recommended

Use the RANK.AVG Formula with Subtraction

Using a combination of simple subtraction and the RANK.AVG function ensures that tied scores receive the average points for their rank positions, maintaining a constant total.

To distribute points inversely (lowest score gets the highest points), you need to rank the scores in ascending order and subtract the resulting rank from the total number of participants plus one. For 12 participants, you subtract the rank from 13.

Set up your data

Ensure your scores are listed in a single column. For this example, assume the scores are located in cells A3 through A14.

Enter the formula

Click on cell B3 and enter the following formula: =13-RANK.AVG(A3,$A$3:$A$14,1)

Apply to the remaining cells

Press Enter to calculate the first result. Then, click on cell B3 again and double-click or drag the fill handle (the small square at the bottom-right corner of the cell) down to cell B14.

Use the RANK.AVG Formula with Subtraction

Understanding the Formula: The '1' at the end of the RANK.AVG function forces Excel to rank the lowest score first. Subtracting this rank from 13 (12 scores + 1) flips the weighting, guaranteeing the lowest score earns 12 points while perfectly averaging out any tied positions.

Solution 2

Use Dynamic Arrays in Modern Excel Versions

If you are using Microsoft 365, Office 2021, or newer versions, you can use dynamic arrays to spill the formula across the entire range automatically.

WPS Spreadsheet Solutions

Easily Rank Scores and Calculate Ties in WPS Spreadsheet

WPS Spreadsheet fully supports advanced statistical functions like RANK.AVG and dynamic arrays, allowing you to easily manage complex scoring, grading, and ranking systems with high efficiency.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your list of scores.
2. Select the target cell: Click on the empty cell next to your first score where you want the assigned points to appear.
3. Insert the RANK.AVG formula: Go to the Formulas tab, select Insert Function, and search for RANK.AVG, or directly type =13-RANK.AVG(A3,$A$3:$A$14,1) into the formula bar.
4. Fill the series: Press Enter to generate the points, then drag the fill handle downwards to apply this calculation to the rest of the dataset.

Fully compatible with Microsoft Excel formulas, including RANK.AVG and legacy array formulas (Ctrl+Shift+Enter).Free, lightweight, and highly responsive alternative for all your spreadsheet needs.Built-in function wizard helps you easily select, understand, and configure complex statistical formulas.

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between RANK.EQ and RANK.AVG?

RANK.EQ assigns the top rank of a set of tied values to all matching values (e.g., if two people tie for 2nd, they both get rank 2, and the next person gets rank 4). RANK.AVG assigns the mathematical average rank to the tied values (e.g., they both get rank 2.5), which is crucial for distributing a fixed pool of points evenly.

How do I adjust this formula if I have 20 scores instead of 12?

To adapt this logic for a different number of scores, change the starting subtraction number to be your total number of scores plus 1. For 20 scores, the formula would be =21-RANK.AVG(A3,$A$3:$A$22,1).

Why am I getting a #VALUE! error when using the RANK.AVG function?

A #VALUE! error typically occurs if the referenced score or the data range contains text strings or hidden spaces instead of actual numbers. Ensure all cells in your scoring range are formatted properly as numbers.

Excel & Data Problems